title: Linked Server Error using MSDTC
body: <p>I am Showing An example of  Stored Procedure For Data Transaction using "Linked Server" Between Two System Through Internet</p>
  
  <pre><code>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
  </code></pre>
  
  <p>When i  Running the Stored Procedure I Getting the different errors  in different Time of Execution</p>
  
  <p>Some time Shows this Error</p>
  
  <pre><code>"The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ServerName" was unable to begin a distributed transaction."
  </code></pre>
  
  <p>Another time Shows This Type of Error</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>I am Using the Windows Xp And SQL Server 2005 in Client side  And Windows server 2003 and Sql server 2005 In Net Server</p>
  
  <p>I Set the MSDTC in Both System</p>
  
  <p>How Can i Solve This Problem (And also give the Correct Information For Setting The MSDTC in Windows XP And Windows server 2003) </p>