Thoughts.exe

Ever wanted to return a table with unknown set of columns from your SQL stored procedure? Yes, you can. Say thanks to SQL XML.
(more…)

Certain times we get an error while attaching an mdf file saying,

“The file “D:\DataBaseFile.mdf” is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Could not open new database ‘D:\DataBaseFile.mdf’. CREATE DATABASE is aborted.”
(more…)

The following SQL query demonstrates how to query between two SQL servers in the same query.

Here is the sample. The query demonstrate a join between two tables in different SQL server instances,

SELECT * FROM anotherServer.DataBaseName.dbo.TableName table1
inner join tableName2 tbale2 on  table2.Id = table1.Id

anotherServer - this is name of the instance of the second SQL sever
DataBaseName - this is the Database name in which the table resides on the second SQL Server instance
TableName - Name of the table in the second SQL Server instance

To make this possible, it is necessary to link the second SQL Server instance to the first one.
To link the SQL Servers there is a inbuilt stored proc named ’sp_addlinkedserver’.

Here is a sample to link to an SQL server instance named ‘anotherServer’,

EXEC    sp_addlinkedserver
  @server='machineName',
  @srvproduct='',
  @provider='SQLOLEDB',
  @datasrc='anotherServer',
  @catalog ='DatabaseName'

You will need to link the servers before you can run queries accessing a different server.

It might also be needed to add login for the server linking. For this, we will need to add a login using sp_addlinkedsrvlogin

Here is a sample way,

EXEC sp_addlinkedsrvlogin
  'anotherServer',
  'false',
   NULL,
  'Username',
  'Password'

Have fun.