Linked Server Error using MSDTC

Question

I am Showing An example of Stored Procedure For Data Transaction using "Linked Server" Between Two System Through Internet

Alter Proc [dbo].[usp_Select_TransferingDatasFromServerCheckingforExample]

@RserverName varchar(100), ----- Server Name  
@RUserid Varchar(100),           ----- server user id
@RPass Varchar(100),              ----- Server Password 
@DbName varchar(100)           ----- Server database    

As
Set nocount on
Set Xact_abort on
Declare @user varchar(100)
Declare @userID varchar(100)
Declare @Db Varchar(100)
Declare @Lserver varchar(100)
Select @Lserver = @@servername
Select @userID = suser_name()
select @User=user
Exec('if exists(Select 1 From [Master].[' + @user + '].[sysservers] where srvname = ''' + @RserverName + ''') begin Exec sp_droplinkedsrvlogin ''' +  @RserverName + ''',''' + @userID + ''' exec sp_dropserver ''' + @RserverName + ''' end ')


set @RserverName='['+@RserverName+']'
declare @ColumnList varchar(max)
set @ColumnList = null
select  @ColumnList = case when @ColumnList is not null then @ColumnList + ',' + quotename(name) else quotename(name) end  from syscolumns where id = object_id('bditm') order by colid
set identity_insert Bditm on
exec ('Insert Into Bditm ('+ @ColumnList +') Select * From '+ @RserverName + '.'+ @DbName + '.'+ @user + '.Bditm')
set identity_insert Bditm off

set @RserverName=replace(replace(@RserverName,'[',''),']','')

Exec sp_droplinkedsrvlogin  @RserverName,@userID
Exec sp_dropserver @RserverName

When i Running the Stored Procedure I Getting the different errors in different Time of Execution

Some time Shows this Error

"The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ServerName" was unable to begin a distributed transaction."

Another time Shows This Type of Error

OLE DB provider "SQLNCLI" for linked server "70.38.11.49" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI" for linked server "70.38.11.49" returned message "Communication link failure".
Msg 65535, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF]. 
OLE DB provider "SQLNCLI" for linked server "70.38.11.49" returned message "Communication link failure".
Msg 65535, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF]. 
OLE DB provider "SQLNCLI" for linked server "70.38.11.49" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.

I am Using the Windows Xp And SQL Server 2005 in Client side And Windows server 2003 and Sql server 2005 In Net Server

I Set the MSDTC in Both System

How Can i Solve This Problem (And also give the Correct Information For Setting The MSDTC in Windows XP And Windows server 2003)

Answer 1

A long shot, but i've encountered a similiar problem a while back, on MSSQL Server 7. It turned out that both system were required to contact each other by NetBios names, and since they were not part of a Domain (but were communicating over the WAN like in your case).

A simple solution, at least to check if this is the case, would be to edit lmhosts file to map the computer name of each one its IP address (both in the client and server).

See a somewhat confused post i made back then: http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/2c246bd2afc7c4d9

Answer 2

I agree with the above posting that it might be a protocol issue but it might need to use Named Pipes as the interface.

Or it could be that the user account that the DTC service is runnning under only has local rights and no rights on the network. Use a domain account with sufficient rights on both machines to run DTC under.

Or it could be that you need to start a distributed transaction with BEGIN DISTRIBUTED TRANSACTION at the front-end of your process.

Answer 3

you might want to look at:

http://support.microsoft.com/kb/306212

and

http://support.microsoft.com/kb/937517

Answer 4

I can confirm it's a network connectivity issue. I have seen this before.

MSDTC agents must be able to connect to all database instances they are coordinating.

There was some MS-certified proceure for testing this... google for msdtc ping.