Transact-SQL Reference

sp_getbindtoken

Returns a unique identifier for the transaction. This unique identifier is referred to as a bind token. sp_getbindtoken returns a string representation to be used to share transactions between clients.

Syntax

sp_getbindtoken [@out_token =] 'return_value' OUTPUT [, @for_xp_flag]

Arguments

[@out_token =] 'return_value'

Is the token to use to share a transaction. return_value is varchar(255), with no default.

@for_xp_flag

Is a constant. If equal to 1, a bind token is created that can be passed to an extended stored procedure to call back into the server.

Return Code Values

None

Result Sets

None

Remarks

In Microsoft SQL Server 2000, sp_getbindtoken will return a valid token only when the stored procedure is executed inside an active transaction. Otherwise, SQL Server will return an error message. For example:

Note In SQL Server 7.0, sp_getbindtoken returns a valid token even if the stored procedure is executed outside an active transaction. The example works in SQL Server 7.0.

/*open a database*/
USE MYDB
GO
/*declare bind token; no active transaction*/
DECLARE @bind_token varchar(255)
/*return bind token*/
EXECUTE sp_getbindtoken @bind_token OUTPUT
/*get an error message*/
Server: Msg 3921, Level 16, State 1, Procedure sp_getbindtoken, Line 4
Cannot get a transaction token if there is no transaction active.
Reissue the statement after a transaction has been started.

When sp_getbindtoken is used to enlist a distributed transaction connection inside an open transaction, SQL Server 2000 returns the same token. For example:

USE MYDB
   DECLARE @bind_token varchar(255)
   BEGIN TRAN
      EXECUTE sp_getbindtoken @bind_token OUTPUT
      SELECT @bind_token AS Token
      BEGIN DISTRIBUTED TRAN
         EXECUTE sp_getbindtoken @bind_token OUTPUT
         SELECT @bind_token AS Token
/*returns the same token*/
Token
-----
PKb'gN5<9aGEedk_16>8U=5---/5G=--
(1 row(s_) affected)

Token
-----
PKb'gN5<9aGEedk_16>8U=5---/5G=--
(1 row(s_) affected)

The bind token can be used with sp_bindsession to bind new sessions to the same transaction. The bind token is only valid locally inside each SQL Server and cannot be shared across multiple instances of SQL Server.

To obtain and pass a bind token, you must run sp_getbindtoken prior to executing sp_bindsession for sharing the same lock space. If you obtain a bind token, sp_bindsession runs correctly.

Note It is recommended that you use the srv_getbindtoken Open Data Services API to obtain a bind token to be used from an extended stored procedure.

Permissions

Execute permissions default to the public role.

Examples

A. Obtain a bind token

This example obtains a bind token and displays the bind token name.

DECLARE @bind_token varchar(255)
BEGIN TRAN
EXECUTE sp_getbindtoken @bind_token OUTPUT
SELECT @bind_token AS Token

This is the result set:

Token
----------------------------------------------------------
\0]---5^PJK51bP<1F<-7U-]ANZ

B. Use the @for_xp_flag parameter

This example specifies a constant to use for calling back to the server.

DECLARE @bind_token varchar(255)
BEGIN TRAN
EXECUTE sp_getbindtoken @bind_token OUTPUT, 1
SELECT @bind_token AS Token

If a constant is not used for @for_xp_flag, this error message is returned:

Msg 214, Level 16, State 1, Server <server_name>, Procedure <procedure_name>, Line 5
Cannot convert parameter @for_xp_flag to type constant expected by procedure.