Msdtc Transaction
- by Shimjith
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.
