I am using Linked server For Tansaction
example
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+']'
BEGIN TRY BEGIN TRANSACTION
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
Commit Select 1
End try
Begin catch
if (@@ERROR <> 0)
Begin
if @@trancount >0
Begin
Rollback transaction
Select 0
END
End
End Catch
set @RserverName=replace(replace(@RserverName,'[',''),']','')
Exec sp_droplinkedsrvlogin @RserverName,@userID Exec sp_dropserver @RserverName
this is the Error Occuerd
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.