XPDTC

XPDTC.DLL - DTC transaction information

version: 1.0.0.3

last updated: 7 July 2004

platform: SQL Server 2000, Windows XP SP1 and higher or Windows Server 2003 and higher

Content:

Description
Usage
Parameters
Return codes
Installation
Uninstall
Version and platform support
Known limitations
FAQ
Troubleshooting
License
Planned functionality enhancements
Realized functionality enhancements
Change log

Description:

XPDTC provides a way to retrieve information about the fact if the current context in which the extended stored procedure (xp_dtc_tx_info) is executed, is part of a distributed transaction or not. When part of a distributed transaction, the XP can retrieve the transaction object and return the transaction ID which uniquely identifies the MS-DTC transaction, the transaction timeout and the transaction description. This information is very useful when you need to troubleshoot distributed transaction and correlate transaction back to COM+ or MS-DTC based on the Transaction ID.

The extended stored procedure uses the undocumented ODS call srv_getdtcxact, which when called in the context of a distributed transaction returns a interface pointer to a MS-DTC ITransaction object (with AddRef() called). This can be useful if you want to extend the transaction from an XP call in to a different resource manager. The source of xp_dtc_tx_info is made available so you can use it as a starting point.

Usage:

XPDTC only supports named parameters, since most of the parameters are optional. XPDTC does not support ordinal parameters, mainly to reduce complexity, ambiguity and the change on mistakes.

-- ************************************************************************
-- Begin of sample section
-- ************************************************************************

declare @rc                     int,
        @IsInTransaction        int,
        @TransactionId          uniqueidentifier,
        @TransactionTimeout     int,
        @TransactionDescription varchar(40)
exec @rc = master.dbo.xp_dtc_tx_info
        @IsInTransaction        = @IsInTransaction output,
        @TransactionId          = @TransactionId output,
        @TransactionTimeout     = @TransactionTimeout output,
        @TransactionDescription = @TransactionDescription output
select rc                      = @rc,
        IsInTransaction         = @IsInTransaction,
        TransactionId           = @TransactionId,
        TransactionTimeout      = @TransactionTimeout,
        TransactionDescription = @TransactionDescription

This is the complete list off parameters used by xp_dtc_tx_info. The parameter names used are case-insensitive. When a parameter is marked as mandatory this means you have to provide the parameter, all other parameters are optional. Either because default values are provides, either because they are really optional in nature.

Name	Data types allowed	Direction	Mandatory	Value ranges	Description
@IsInTransaction	INT	Output	Optional	[0\|1]	Returns a Boolean value [0\|1] to indicate it the at time the procedure was execute the transaction was part of a distributed transaction. This is determined by calling the undocumented ODS call srv_getdtcxact. If this call returns a NULL pointer the transaction is not part of a distributed transaction, when this call returns a pointer to a valid ITransaction object the transaction is part of a distributed transaction.
@TransactionId	UNIQUEIDENTIFIER	Output	Optional	All valid UNIQUEIDENTIFIER values	Transaction ID that uniquely identifies the distributed transaction, NULL when the transaction is not part of a distributed transaction (@IsInTransaction = 0)
@TransactionTimeout	INT	Output	Optional	>= 0	Returns the transaction timeout as it is set on the distributed transaction
@TransactionDescription	VARCHAR(40)	Output	Optional		Returns the optional transaction description an it is set on the distributed transaction

Return codes:

The procedure returns only two return codes, 0 (zero) indicating successful execution, 1 indicating failure always accompanied with an error message.

This is how to retrieve the return code:

declare @rc int
exec @rc = master.dbo.xp_dtc_tx_info...
select @rc

Installation:

To install XPDTC follow these instructions:

For SQL Server 7.0 download XPDTC70.ZIP and unzip the files
For SQL Server 2000, download XPDTC80.ZIP and unzip the files

Sources: XPDTC-SRC.ZIP (Visual Studio 2003, C++ project)
Copy xpdtcXX.dll into the SQL Server BINN directory. For SQL Server 7.0 copy XPDTC70.DLL, for SQL Server 2000 copy XPDTC80.DLL

For SQL Server 7.0 the default installation location is "C:\MSSQL7\BINN"
For SQL Server 2000 the default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"
Register the extended stored procedure using OSQL or SQL Query Analyzer by executing:
-- SQL Server 7.0 install
exec sp_addextendedproc 'xp_dtc_tx_info', 'xpdtc70.dll'
-- SQL Server 2000 install
exec sp_addextendedproc 'xp_dtc_tx_info', 'xpdtc80.dll'
Grant rights to the correct set of users using OSQL or SQL Query Analyzer by executing:
grant execute on xp_dtc_tx_info to public
By default only the member of the sysadmin role have execution rights on the XP after it is being registered

Uninstall:

To remove XPDTC from a system follow these instructions:

Force the DLL out of memory, using OSQL or SQL Query Analyzer by executing:
dbcc xpdtc70(free) -- for SQL Server 7.0
dbcc xpdtc80(free) -- for SQL Server 2000
Unregistered the XP from the system, using OSQL or SQL Query Analyzer by executing:
exec sp_dropextendedproc 'xp_dtc_tx_info'
Delete the XPDTC70.DLL or XPDTC80.DLL from the SQL Server BINN directory

Version and platform support:

XPDTC is tested and supported on:

SQL Server 2000 (including all available service packs), running on Windows XP SP1 and Windows Server 2003
MSDE 2000 (including all available service packs), running on Windows XP SP1 and Windows Server 2003

XPDTC is supported on:

SQL Server 7.0 (including all available service packs), running on Windows NT 4.0, Windows 2000 and Windows XP
MSDE 1.0 (including all available service packs), running on Windows NT 4.0, Windows 2000 and Windows XP

XPDTC is NOT supported or tested in any way or form on:

SQL Server 7.0 or 2000 running on Windows 95, Windows 98, Windows 98 Second Edition or Windows Me
MSDE 1.0 running on Windows 95, Windows 98, Windows 98 Second Edition or Windows Me
MSDE 2000 running on Windows 95, Windows 98, Windows 98 Second Edition or Windows Me
SQL Server 7.0 running on the Alpha processor

Known limitations:

There are currently no known limitations that we are aware off.

FAQ:

There are currently no FAQ answers available.

Troubleshooting:

There is currently no troubleshooting information available.

License

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
Neither the name of SQLDev.Net nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
Binaries, source code and any other parts of this distribution may not be incorporated into any software licensed under the terms of the GNU General Public License (GPL) or the GNU Lesser Public License (LGPL). Binaries, source code and any other parts of this distribution
may not be incorporated into any software licensed under any license requiring source code disclosure of derivative works.
Modified redistributions of source code, binaries and/or documentation must carry the above copyright as required by clauses (1) and (2) and may retain the name "SQLDev.Net" in source code, documentation and metadata.
The name "SQLDev.Net" is a trademark of SQLDev.Net B.V. the Netherlands.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Planned functionality enhancements:

The next release will contain the following feature (subject to change):

No changes are planned
Other suggestions, ideas, please send email to ideas

Realized functionality enhancements:

None

Change log:

Version	Date	Description
1.0.0.3	2004/07/04	updated release for TechEd Europe 2004
1.0.0.2	2004/05/22	updated release for TechEd US 2004
1.0.0.1	2003/08/03	first release to web
1.0.0.0	2003/07/26	lib created

***