ASP Alliance Member: MTG

Distributed Transactions:

Every SQL command (batch) you issue to an SQL Server is handled as a transaction. If the transaction fails via a hardware failure, system crash, etc., the entire transaction is aborted (rolled back) and any changes that are intended are NOT saved (commited).

Every transaction is recorded in the Transaction Log of the corresponding database. Transactions are implicitly created for every SQL command batch or explicitly through the use of BEGIN TRAN and COMMIT TRAN statements.

This system is in place to ensure the integrity of your SQL Server data. If a transaction does not get saved (commited) because of a failure of some sort, the changes are not saved (rolled back).

This kind of redundancy is not possible with SQL commands issued against a remote server. In order for a local server to effectively create a transactional model at a remote server you have to implicitly create distributed/remote transactions in the SQL script issued to the remote SQL server.

The following is an example of how to do this against an SQL Server:

BEGIN DISTRIBUTED TRAN -- begin distributed transaction INSERT INTO RemoteServer.Database.dbo.Table (Col1, Col2, Col3) VALUES ("Val1","Val2","Val3") -- a new record is created but NOT for sure until it's COMMITed IF EXISTS ( -- if the insert produced a NULL value in column Col1 then ROLLBACK SELECT * FROM RemoteServer.Database.dbo.Table WHERE Col1 IS NULL ) ROLLBACK TRAN -- the changes are ROLLED BACK / not saved to the database ELSE COMMIT TRAN -- end/save distributed transaction In this example, a new record is created in the database table specified and then it is checked for NULL values in the Col1 column. If NULL values EXIST, the changes are rolled back / not saved, hence, the INSERT that was performed is undone (no record is created).

The entire distributed transaction process is handled by the Microsoft Distributed Transaction Coordinator (MS DTC). So, in order for this process to work, the MSDTC service should be running at both the local and remote servers. You can check this by going to Control Panel and double-clicking on Services.

Distributed / Remote Queries

In the above example, you probably noticed the use of a remote server name in the query (RemoteServer). This type of query could only be possible if you properly configured your local SQL Server to do so (the server that is processing you SQL scripts).

When issuing SQL commands in this fashion you are NOT issuing them directly to the remote SQL Server but rather letting the local SQL Server run the SQL commands and direct them toward the specified remote server(s).

SQL Servers that you access in this fashion are called linked servers.

NOTE: SQL Server considers remote servers to be those used for replication ONLY. All other servers not used for replication are considered to be linked servers.

Perform the following statements on the SQL Server from where you will be accessing a linked MS-SQL server: USE master EXEC sp_addlinkedserver @server = "ServerAliasName", --> any name you want to refer to this linked server as @srvproduct = "SQLServer OLEDB Provider", --> You CAN NOT use 'SQL Server' at the beginning of the text for @srvproduct! @provider = "SQLOLEDB", @datasrc = "192.168.0.1", --> the TCP/IP address to the linked server (or simply the name) @catalog = "master" --> the default database to use EXEC sp_addlinkedsrvlogin @rmtsrvname = "192.168.0.1", --> the name of the server specified above in @server @locallogin = "sa", --> maps this login name to that specified in @rmtuser (below) @rmtuser = "sa", --> the user name to use on the linked server @rmtpassword = "password" --> the password of the @rmtuser user As you can see, two Stored Procedures are used here: sp_addlinkedserver and sp_addlinedsrvlogin.

Now, you can issue SQL statements like that above simply by specifying the alias you created in @server in your queries: SELECT * FROM ServerAliasName.Database.dbo.Table

Executing Stored Procedures Stored on Linked Servers:

Before you can execute stored procedures on linked SQL servers, there are two commands you need to issue on the SQL Server that will be accessing those linked servers: USE master EXEC sp_serveroption "ServerAliasName", "rcp", TRUE EXEC sp_serveroption "ServerAliasName", "rcp out", TRUE By the way, RCP stands for Remote Procedure Calls.

After doing so you can execute the Stored Procedures as follows: EXEC ServerAliasName.Database.dbo.sp_name

Using Pass-through Queries:

When you issue SQL commands against a linked/remote SQL server, you are, in reality, passing the commands to your local SQL server. The local SQL Server then sends those commands, with the help of MS DTC, to the linked/remote server.

There is a way you can tell your local SQL server NOT to process/parse the SQL commands intended for a linked/remote SQL server. Here's two examples:

If you setup a linked server through sp_addlinked server and sp_addlinkedsrvlogin as specified above on the local SQL server:
If you DID NOT explicitly add a linked server to your local SQL Server using sp_addlinkedserver and sp_addlinkedsrvlogin as specified above:

In the second example, there is no need to pre-configure the local SQL server to access the specified linked SQL server. It is done so (Ad Hoc) explicitly, and temporarily, using the OPENROWSET function.

Comments & Questions Form Comment(s):