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.
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:
/* Begin of script InstallCS.Sql *//* Author: Giuseppe Dimauro March 2000 */– master database selectionUSE mastergoIF EXISTS (SELECT name FROM sysobjects WHERE name = ‘sp_CSUnpublish’ AND type = ‘P’)DROP PROCEDURE sp_CSUnpublishgoIF EXISTS (SELECT name FROM sysobjects WHERE name = ‘sp_CSPublish’ AND type = ‘P’)DROP PROCEDURE sp_CSPublishgoIF EXISTS (SELECT name FROM sysobjects WHERE name = ‘sp_CSInitialize’ AND type = ‘P’)DROP PROCEDURE sp_CSInitializegoIF EXISTS (SELECT name FROM sysobjects WHERE name = ‘sp_CSSubscribe’ AND type = ‘P’)DROP PROCEDURE sp_CSSubscribegoIF EXISTS (SELECT name FROM sysobjects WHERE name = ‘sp_CSUnsubscribe’ AND type = ‘P’)DROP PROCEDURE sp_CSUnsubscribego– Initialization stored procedure CREATE PROCEDURE sp_CSInitialize ASBEGIN– Delete the previous tabledrop 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 publicENDgo– Set the sp_CSInitialize stored procedure as auto-startingsp_procoption ‘sp_CSInitialize’, ‘startup’, truego– Terminate the publishing of the isolation levelCREATE PROCEDURE sp_CSUnpublish ASBEGIN– Delete the current user’s rows from the support table DELETE FROM master.dbo.IsolationTemp where sysUser = system_userENDgo– Publish the own isolation levelCREATE PROCEDURE sp_CSPublish ASBEGINDECLARE @bind_token varchar(255)– Get the own isolation level’s tokenEXECUTE sp_getbindtoken @bind_token OUTPUT– Delete the previous entriesexec sp_CSUnpublish– Create the new entries for the current isolation levelINSERT INTO master.dbo.IsolationTemp (SysUser, BindToken, SysSPID) VALUES (system_user, @bind_token, @@SPID)ENDgo– Subscribe a published isolation level CREATE PROCEDURE sp_CSSubscribe ASBEGINdeclare @BindToken varchar(4096)– Get the token select @BindToken = (SELECT TOP 1 BindToken From master.dbo.IsolationTemp (NOLOCK) Where SysUser = SYSTEM_USER)– Hook the isolation levelexec sp_BindSession @BindTokenENDgo– Refuse the subscribed isolation level CREATE PROCEDURE sp_CSUnsubscribe ASBEGINexec sp_BindSession NULLENDgogrant all on sp_CSUnpublish to publicgrant all on sp_CSPublish to publicgrant all on sp_CSInitialize to publicgrant all on sp_CSSubscribe to publicgrant all on sp_CSUnsubscribe to public– First execution of the initialization stored procedure exec sp_CSInitializego/* End of script InstallCS.Sql */
########################################################
This tip has been originally published on Microsoft Italia’s web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli
########################################################
