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.

Add a check constraint for your date in Sql Server

 

Generally most of the people will have the requirement to add the dates to the database.  Most of the cases they will have two related dates, as FromDate and ToDate or Begin and End.  However most of the people wont add any constraints to their database even they knows that the First Date is always less than the Second Date. 

CREATE TABLE CheckDates
(
    StartDate DATETIME,
    EndDate DATETIME,

    CONSTRAINT
        cmp_Dates CHECK (StartDate<EndDate)
)

Here I am adding a table level check constraint.  Now the database will verify when ever the user tries to add the Start Date > End Date with out writing any triggers or additionally writing logic in stored procedures or front end application every time we insert.

However, you will face problem if you try to insert the column level constraint by referring to another column in the same table.

CREATE TABLE CheckDates
(
    StartDate DATETIME,
    EndDate DATETIME CONSTRAINT cmp_Dates CHECK (StartDate<EndDate)
)

Writing the script as above will give you the error message “Column CHECK constraint for column 'EndDate' references another column, table 'CheckDates'.”.  Notice the comma in the first script which separates the column after EndDate DATETIME.