Click here to Skip to main content
Click here to Skip to main content

SQL Server 2000 Using Tranasaction on Distributed servers - Linked Servers

, 14 Dec 2004
Rate this:
Please Sign up or sign in to vote.
Using tranasaction on distributed servers and querying them - Linked Servers in SQL Server.

Introduction

Let's say, for example, you are having your Stock database and Sales database, both in different locations (geographic locations). Then you need to use your Action query on both the servers. For this situation, let’s see how you can implement transactions.

MS DTC

In order to make a safe connection for distributed transaction, Microsoft facilitates us with MS DTC, that is Microsoft Distributed Transaction Coordinator. This works with a two phase Commit Protocol called 2PC.

The two phases of the same are:

  1. Prepare phase
  2. Commit phase

Prepare phase

The MS DTC of the local server sends a request to all the servers used in the transaction to start a session which in turn returns a Success/ Failure acknowledgement. And the remote server does all the Atomicity and Data consistency at their end.

If any one of the servers acknowledges the local server with a failure message, then the transaction is taken as a Doubt transaction and can be rolled back at any point of time.

Commit phase

When the entire server returns a Success message, then the local server sends all the Remote server’s message to commit which in turn receives a Success/ Failure. In case of failure, the entire transaction is rolled back across the servers.

Commands

BEGIN DISTRIBUTED TRAN
COMMIT TRAN
ROLLBACK TRAN

Key Points to be aware of:

  1. Cannot be nested.
  2. Local transactions can easily be escalated.
  3. MS DTC must be running in the local server.

OK, now let's see how this can be implemented physically.

For this task to be accomplished, you need to do the following steps:

  1. Add the remote server as a linked server.
  2. Add server login to the Added linked server.
  3. Set RPC calls in case you are using Sproc from the remote server.
  4. Use part names for retrieving data.
  1. sp_AddLinikedServer [@server=],
                                 [@srvProduct=],
                                 [@Provider=], 
                                 [@DataSrc=],
                                 [@Location=], 
                                 [@provStr=], 
                                 [@Catalog=]

    By this, you can add a server. All the parameters are self explanatory, hence I skip briefing them! Here, the important things are server, srvProduct in case you add servers other than SQL Server like Oracle, Exchange...., and ProvStr.

  2. sp_AddLinkedSrvLogin

    Parameters are:

    • [@rmtsrvName=] - Server name
    • [@useSelf=] - Set it to true so that the current login info will be carried over. Very useful if NT authentication is used.
    • [@LocalLogin=] - Name of the user for which a mapping is done!
    • [@rmtUser=] - Alias of the local login (login info for remote server).
    • [@rmtPasssword=] - Password for the rmtUser.

    To drop a Linked server:

     sp_dropServer '<Servername>'

    To drop a Linked server Login info:

    sp_dropLinkedSrvLogin '<Servername>'
  3. For using the sproc from the remote server, you need to enable the RPC calls on both the servers using RPC, RPC OUT with the sproc sp_ServerOption
    • sp_ServerOption RPC - from Remote to Local server.
    • sp_ServerOption RPC OUT - from Local server to Remote.

    Now you are done with all the settings for accessing and querying the remote servers (linked servers).

  4. How to query??

    Big question isn't it? But SQL gives a simple answer... just use the full part names as, say for example:

    Linked server Name: MyRemoteServer
    DB_OWNER: Senthil
    Database: dbTest
    Table: tblTest

    Here goes your query:

    SELECT a.* FROM MyRemoteServer.dbTest.Senthil.tblTest AS A

    I.e.: select .......... from LinkedServerName.DataBase / Catalog name.Owner.Table /Entity. You can join this result and do any thing as you do with your local database.

    To retrieve the Metadata from the Linked server, you can use the following sproc:

    sp_LinkedServers - List all active linked server
    sp_Catalog
    sp_Tables_ex
    sp_Columns_ex
    sp_table_previlages_ex
    sp_column_privilages_ex
    sp_Primarykeys
    sp_foreignkeys
    sp_indexes
    

    Note: all the stored procedures have some parameters which I'm really bored to explain here. You can find them from the book online!

Other than the above stated method for retrieving, you can still use other methods also for the same. They are:

  1. OPENQUERY
  2. OPENROWSET

OPENQUERY

For this, you need to add the Linked server first!

  • you can use the Action queries with this (I/U/D) until your OLEDB Provider supports!
  • you can join the result set with any thing as you do always with local server.

Syntax:

SELECT ... FROM OPENQUERY('<Linked server name>','<Query>')

Example:

            SELECT a.* 
            FROM OPENQUERY ('MyRemoteServer','Select * from Employee) AS A 
            Inner Join Department d ON d.DeptID = a.DeptID

Note: Department lies in local server!

OPENROWSET

This is used with out adding the Linked server because you will be adding them in the query itself!!

Syntax:

SELECT .. FROM OPENROWSET
       ('<Provider>','<Datasource/Catalog>','<Loginid>','<pwd>','<Query>')

Performance Issues:

There is some thing called as Collation. What this does is some thing superb and also something a developer should know!!

Default set to off and you can turn it to on in:

sp_ServerOption '<Servername>','Collation Compatible',True

What it does if this is set to off is the data retrieved from remote server comes as a whole from the referenced table irrespective of the WHERE clause. In the local server where the clause is applied on the result set, say for example, if you have a query like:

SELECT * FROM employee WHERE salary >  10000

Here all the records of employee is brought to the local server and with that result set, the WHERE clause is applied. So turn it on so that you will be having at least two CPUs in action and also you can still have security on your data!!

Things that cannot be used on a Linked server

  1. CREATE/ALTER/DROP on the objects!
  2. Order by if BLOB objects are used in the SELECT clause.
  3. Any BLOB related statements cannot be used in the Linked server.

Conclusion

By this, I'm sure, you can have a Linked server and keep it working. Be aware that registering a server in EM and this is same! Any further clarification on this is welcome!!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

R. Senthil Kumaran
Architect Tesco HSC
India India
Am a C#.Net, ASP.Net, SQL Server Professional
Intrested in Music, Cricket, and exploring new ideas...

Comments and Discussions

 
GeneralThanks PinmemberHakan Fatih YILDIRIM23-Oct-07 20:42 
Generalthanks Pinmemberdwight029-Aug-06 12:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 14 Dec 2004
Article Copyright 2004 by R. Senthil Kumaran
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid