Happy new year, SQL peeps! I hope everyone had a wonderful holiday. Last week was very quiet at work, but I did have a developer email me with a problem she was having. (I do believe she may have been the only one actually working, can you imagine??) But that’s ok, I love using this type of thing as blog fodder. After all, chances are she’s not the only one with this question.

So here’s the scenario. On MyServer2 (our remote server), I have a database, MyDatabase2, that contains a single table.

USE MyDatabase2;
GO
CREATE TABLE MyTab2 (
FirstNamevarchar(20),
LastNamevarchar(20))
GO

On our local server, MyServer1, I have a database with a table called MyTab1 to record debugging statements and a procedure that inserts into MyTab1 and also inserts into MyTab2 via a linked server.

USE MyDatabase1
GO
CREATE TABLE MyTab1 (
ActionTaken varchar(100));
GO
CREATE PROCEDURE usp_InsertName1 (@FirstName varchar(20), @LastName varchar(20))
AS
BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION
INSERT INTO MyTab1 (ActionTaken) SELECT 'Inserting into remote table';
INSERT INTO MyLinkedServer.MyDatabase2.dbo.MyTab2 VALUES (@FirstName, @LastName);
INSERT INTO MyTab1 (ActionTaken) SELECT 'Done Inserting into remote table';
COMMIT TRANSACTION
END
GO
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'SQL', @provider=N'SQLNCLI10', @datasrc=N'MyServer2', @catalog=N'MyDatabase2'
/* For security reasons the linked server remote logins password is changed with ######## */EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'False',@locallogin=N'MyLogin1',@rmtuser=N'MyLogin2',@rmtpassword='MyLogin2'

Seems simple enough, but when I attempt to execute this local procedure, I get the following error:

OLE DB provider “SQLNCLI10″ for linked server “MyLinkedServer” returned message “Cannot start more transactions on this session.”.
Msg 7395, Level 16, State 2, Procedure usp_InsertName1, Line 11
Unable to start a nested transaction for OLE DB provider “SQLNCLI10″ for linked server “MyLinkedServer”. A nested transaction was required because the XACT_ABORT option was set to OFF.

So I see what the error message says, but why does this happen? Well, with the BEGIN TRANSACTION statement, we’ve started a local transaction. That’s obvious. However, when we issue the INSERT statement against the linked server, we instantiate another, nested, transaction on the remote server. And the SQL Server OLE DB provider doesn’t support nested transactions. The common solution to this error is to set XACT_ABORT to ON, just like the error recommends. This tells SQL Server that, if anything goes wrong while running that remote insert statement, go ahead and abort the current transaction. By doing this, we eliminate the need to start that nested transaction.

ALTER PROCEDURE usp_InsertName1 (@FirstName varchar(20), @LastName varchar(20))
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO MyTab1 (ActionTaken) SELECT 'Inserting into remote table';
INSERT INTO MyLinkedServer.MyDatabase2.dbo.MyTab2 VALUES (@FirstName, @LastName);
INSERT INTO MyTab1 (ActionTaken) SELECT 'Done Inserting into remote table';
COMMIT TRANSACTION
END
GO

But is that the only way to skin this cat*? Nope. What if we were to use a remote stored procedure to perform the insert, and call that from our local procedure?

-- On MyServer2
USE MyDatabase2;
GO
CREATE PROCEDURE usp_InsertName (@FirstName varchar(20), @LastName varchar(20))
AS
BEGIN
INSERT INTO MyTab2 (FirstName, LastName) VALUES (@FirstName, @LastName)
END
GO
-- On MyServer1
CREATE PROCEDURE usp_InsertName1 (@FirstName varchar(20), @LastName varchar(20))
AS
BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION
--This works. But there's a problem...
INSERT INTO MyTab1 (ActionTaken) SELECT 'Calling remote procedure';
EXEC MyLinkedServer.MyDatabase2.dbo.usp_InsertName2 @FirstName, @LastName;
INSERT INTO MyTab1 (ActionTaken) SELECT 'Remote procedure complete';
COMMIT TRANSACTION
END
GO

This works because remote stored procedures are executed outside the scope of the current transaction, so no nesting here. This allows us to get around the error, but it also means that any work done by the remote procedure won’t be undone in the event of a ROLLBACK in the local procedure. And that’s probably not what we intended.

So how do we use the remote procedure and get everything inside the scope of the transaction? We start a distributed transaction.

ALTER PROCEDURE usp_InsertName1 (@FirstName varchar(20), @LastName varchar(20))
AS
BEGIN
SET NOCOUNT ON
BEGIN DISTRIBUTED TRANSACTION
--Now even the remote proc is within the tran
INSERT INTO MyTab1 (ActionTaken) SELECT 'Calling remote procedure';
EXEC MyLinkedServer.MyDatabase2.dbo.usp_InsertName2 @FirstName, @LastName;
INSERT INTO MyTab1 (ActionTaken) SELECT 'Remote procedure complete';
COMMIT TRANSACTION
END
GO

Another way to do this would be to change the configuration of the linked server itself to automatically promote transactions to distributed transactions.

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

You want to be careful changing settings like this, however, as they could have unintended effects on other applications that use this linked server.

* No cats were harmed in the making of this post.