How to export delimited (tab, space, comman) file from query analyzer

How to export delimited (tab, space, comman) file from query analyzer Visual Management Studio (2005) In the  Query menu choose Query options 1.  From Results Choose Text 2. Choose out format from drop down list 3.  Click OK. 4. In the query menu from  Result to choose Result to file 5.  Run your query it […]

How to get only date from data field SQl server t- sql

Following line gives you the short date  from long date time field. ie. From a date time field of Sql server field if you need to extract only date use the the following method. Replace GetDate() with your date field name. Cast(Floor(Cast(Getdate() as float)) as datetime

Accessing Different Database on Different SQL Servers

Accessing Different Database on Different SQL Servers I was trying to Access two databases on two different servers. I was usring four part name to SQlServer “[sqlserver02].MyDatabase.dbo.aspnet_membership” but it was not letting me do that and was showing following error message. Msg 7202, Level 11, State 2, Line 1 Could not find server ‘sqlserver02’ in […]

Two methods to update data from one table to another table using sql and TSQl

Two methods to updated data from one table to another table using sql and TSQL, T1=Table1 T2=Table2 F1=Field1 F2=Field2 F3=Filed3 F4=Field4 Method one, Update date using subquery from one table to another table update T2 set T2.F1=isnull(T1.F1,”), T2.F2= T1.F2, T2.F3= T1.F3 from T1 inner join T2 on T2.F2= T1.F4 where T2.F2<> T1.F2 and T2.F3<> T1.F3 […]

how to show multiple fields as a single field separated by comma

I wanted to concatinate multiple address fields as a single field separated by comma. Here is the solution which is doing job for me. 8000 is the Max length of varchar data type in sql server. Using COALESCE() function saves  lot of IF or CASE decision logics. This function Returns the first nonnull expression among […]

Using (nolock) with SQL Select Query in SQL Server 2005

I was using following query select top 10 * from [sqlserver02\sqlserver02].mydatabase.dbo.aspnet_membership (nolock) whereas “sqlserver02″ was a remote serever  and I found following errro Msg 4122, Level 16, State 1, Line 1 Remote table-valued function calls are not allowed. Then I remove (nolock) from the query and it worked. Does any one know why remote serever […]

Accent Insensitive search in sql server + COLLATE SQL_Latin1_General_CP1_CI_AI

We were facing a problem in searcing the exact data from Microsoft sql server 2005 database, Our database is containng foreign language characters ,çéèêëñòóôõöàáâãäåìíîïùúûüŵŷýÿ as you can see in following data, I was wondering how we can solve this problem, I tried to search it on the google and posted this in some forums. The Coleg Sir Gâr   […]

Linked Servers +Microsft SQL Server, Excel, Access (Ms-Access)

Linked Servers + Distributed Queries Linking to External Data Sources Following stored procedure will allow you to create a link to another SQL Server. Link server name would be “SECOND” EXEC sp_addlinkedserver @server=‘myservername’,–name you will be using to refer link server @srvproduct = ‘Excel’, –Product name i.e excel @provider = ‘Microsoft.Jet.OLEDB.4.0’, –Jet engine @datasrc = […]

Some information about databases

Database is a set of rows and columns. We use database to store information in it and we can retrieve that information on demand. To manipulate database we use SQL (Structured Query Language.) There are many database or DBMS (Database Management System) are available few re-known are Oracle, SQL Server, MySql, Sybase etc. Which database […]