CODEWITHSUNDEEP
sql-server
Shimjith
Shimjith

Reputation: 51

Linked Server Error

I am using Linked server For transferring data using MSDTC

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 ')

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('Crnot') order by colid   

Set identity_Insert Crnot On
exec ('Insert Into ['+ @RserverName + '].'+ @DbName + '.'+ @user +'.Crnot ('+ @ColumnList +') Select '+ @ColumnList +' from Crnot ')
Set identity_Insert Crnot Off

Exec sp_droplinkedsrvlogin  @RserverName,@userID
Exec sp_dropserver @RserverName

when executing this qry i get the error "No transaction Active"

Upvotes: 1

Views: 377

Answers (1)

gbn
gbn

Reputation: 432742

Check your MS DTC configuration (cut and paste from a doc, not checked recently):

  1. Start, Run, dcomcnfg.exe

  2. In the Component Services window, expand Component Services... Computers...My Computer.

  3. Right-click My Computer, Properties.

  4. Click Security Configuration on the MSDTC tab.

  5. Click to select the Network DTC Access check box.

  6. Set both the Allow Inbound and Allow Outbound check boxes

  7. Under the Transaction Manager Communication group, click to select the No Authentication Required option.

  8. Verify that the DTC Logon Account name is set to NT AUTHORITY\NetworkService.

  9. Click Ok etc

In your code, Set identity_Insert Crnot only applies to local objects.

It should be part of the dynamic SQL INSERT

Upvotes: 1

Related Questions