Communicate Among the Databases

In certain cases we occasionally need to communicate between the tables that are in different databases.  In this post I am going to explain how to communicate between the tables that are in different databases and in different servers, with a simple example. Let us consider the below database schema,

                    

We have two databases say OurPerson and TheirPerson each database having the table Person(may be different in real time scenarios).

To be simple, let us try to get the names of all the persons from OurPerson database whose ID’s exist in TheirPersons’s Person table.  Here is the simple query to do that.

Query 1:

SELECT P1.Name AS Name FROM  OurPerson.dbo.Person P1 WHERE P1.ID IN(SELECT ID FROM TheirPerson.dbo.Person);

So, in order to get access the tables of the other databases we can just use the complete table information as this,

DatabaseName.User.TableName

and similarly for the columns we use like this

DatabaseName.User.TableName.Column

Accessing the Database From Different Server

The above procedure is fine as long as you have all the databases existing on the same server.  Then what about if the databases are in different servers.  Let us suppose that OurPerson is in Server1 and TheirPerson is in Server2.

Now does the same query as we wrote above will work fine?  Suppose we logged in to Server1 and try to execute with this query,

Query 2:

SELECT P1.Name AS Name FROM  Server1.OurPerson.dbo.Person P1 WHERE P1.ID IN(SELECT ID FROM Server2.TheirPerson.dbo.Person);

The query above is exactly valid query.  But there is no way for the Server1 to pass it’s credentials to Server2, which causes the query to fail.  This is the situation where Linked Server concept come into the picture.  Let us see how to create a Linked Server from Server1 to link Server2.

Creating a Linked Server

You can create the Linked Server from Sql Server with the help of server explorer through the Server Objects as shown in the below figure. 

                            

Right click on the Server Objects and select the New->Linked Server from the context menu which will open a configuration window. In the general tab you can configure your server details, such as the database name, database provider etc. details.  You should provide the security details in the Security tab, which allows you to enter either trusted or untrusted authentication to connect to your linked server.  After everything is configured just click on OK.  That’s it.  Your linked server is now configured with your server.  Now, you can access the Server2’s databases and its tables from the Server1 as in the Query 2 as,

ServerName.DatabaseName.TableName.ColumnName………

Now, you can do all the insertions, updates and queries etc for Server2 from Server1 itself.  One limitation is that you cannot try to update or insert the columns which are of type XML. Hope Microsoft may support this feature in it’s future releases of Sql Server.  For more information on linked servers please visit http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx. This documentation will provide information about how to configure and drop the linked servers using the system defined stored procedures.