Binaryworld - Make several SQL Server connections partecipate to the same transactio ... [ SQL Server -> Server Operations ]

You can reuse resources from other connections by resorting to two little known SQL Server's stored procedures, which allow several connections to share the same transactional space: sp_bindsession and sp_getbindtoken. In particular the sp_getbindtoken stored procedure lets you retrieve the current transaction's token (a string that identifies its own transactional context) and reuse it in another connection, so that the current and the other connection can share the same transaction. All you have to do in the connection where you want to reuse a transactional context is passing the token to the sp_getbindtoken stored procedure. At this point you'll bump into a problem you may not have anticipated: it's not easy to use this technique in SQL Server, because T-SQL doesn't provide any form of Inter Connection Communication to pass data between connections. Such problem doesn't exist when using programming languages, such as VB or VC++. In this case you can use any standard form of Inter Process Communication to pass the token between different applications, or between modules or components wanting to share the transactional context. Let's see how to overcome SQL Server's limits and share the same transactional context between multiple connections with the help of a support table and a few stored procedures. Say you want only connections owned by the same users to take part to the transactions, all that you need is a support table that for simplicity will be created into master database (master.dbo.IsolationTemp), and structured in this way:

SysUser: name of the user that publish his own transaction;
BindToken: the token, called "of Bind", that is the SQL Server's internal ID for the transactional context;
SysSPID: the SPID, called "system process identifier", that identifies the process that has published the transactional context.
In order to easily use this temporary swapping table, four support stored procedures have been defined: sp_CSPublish: Used in the connection that wants to make avaible its own transactional space.
sp_CSUnpublish: It cancels the publishing of its own transactional space and removes the respective information from the support table.
sp_CSSubscribe: Used by the connection that is going share a transactional space owned by the same user or login name, it calls sp_bindSession with the proper token from the temporary table.
sp_CSUnsubscribe: Cancels the subscription and renounces the transaction context borrowed during the subscription.
The sp_CSInitialize stored procedure is noteworthy because you can register it as an auto-starting stored procedure, so that it re-creates the support table with the proper values at every SQL Server reboot. Here is the code you have to insert in a script to be run through the SQL Server Query Analyzer:

Click here to copy the following block

/* Begin of script InstallCS.Sql */
/* Author: Giuseppe Dimauro March 2000 */

-- master database selection
USE master
go

IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'sp_CSUnpublish' AND type = 'P')
DROP PROCEDURE sp_CSUnpublish
go

IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'sp_CSPublish' AND type = 'P')
DROP PROCEDURE sp_CSPublish
go

IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'sp_CSInitialize' AND type = 'P')
DROP PROCEDURE sp_CSInitialize
go

IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'sp_CSSubscribe' AND type = 'P')
DROP PROCEDURE sp_CSSubscribe
go

IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'sp_CSUnsubscribe' AND type = 'P')
DROP PROCEDURE sp_CSUnsubscribe
go

-- Initialization stored procedure
CREATE PROCEDURE sp_CSInitialize AS
BEGIN
-- Delete the previous table
drop table IsolationTemp
-- Create a new copy of the support table
create table IsolationTemp(SysUser varchar(64) primary key, BindToken varchar(4096), SysSPID integer)
-- Allow the public users to fully use the table
GRANT ALL ON IsolationTemp TO public
END
go

-- Set the sp_CSInitialize stored procedure as auto-starting
sp_procoption 'sp_CSInitialize', 'startup', true
go

-- Terminate the publishing of the isolation level
CREATE PROCEDURE sp_CSUnpublish AS
BEGIN
-- Delete the current user's rows from the support table
DELETE FROM master.dbo.IsolationTemp where sysUser = system_user
END
go

-- Publish the own isolation level
CREATE PROCEDURE sp_CSPublish AS
BEGIN
DECLARE @bind_token varchar(255)
-- Get the own isolation level's token
EXECUTE sp_getbindtoken @bind_token OUTPUT
-- Delete the previous entries
exec sp_CSUnpublish
-- Create the new entries for the current isolation level
INSERT INTO master.dbo.IsolationTemp (SysUser, BindToken, SysSPID) VALUES (system_user, @bind_token, @@SPID)
END
go

-- Subscribe a published isolation level
CREATE PROCEDURE sp_CSSubscribe AS
BEGIN
declare @BindToken varchar(4096)
-- Get the token
select @BindToken = (SELECT TOP 1 BindToken From master.dbo.IsolationTemp (NOLOCK) Where SysUser = SYSTEM_USER)
-- Hook the isolation level
exec sp_BindSession @BindToken
END
go

-- Refuse the subscribed isolation level
CREATE PROCEDURE sp_CSUnsubscribe AS
BEGIN
exec sp_BindSession NULL
END
go

grant all on sp_CSUnpublish to public
grant all on sp_CSPublish to public
grant all on sp_CSInitialize to public
grant all on sp_CSSubscribe to public
grant all on sp_CSUnsubscribe to public

-- First execution of the initialization stored procedure
exec sp_CSInitialize
go

/* End of script InstallCS.Sql */