question

Ravi_8999 avatar image
Ravi_8999 asked

Error Handling in Sql while going aross linker servers

Iam executing the query as below.
SELECT TOP 10 [Id] ,[InternalId] ,[WebReference] ,[CompanyCode] ,[PolicyType] ,[AffinityCode] ,[RequestType] ,[RequestXML] ,[ResponseXML] ,[ElapsedTime] ,[DateCreated] ,[EmailSent] ,[IsError] INTO #Test FROM [SourceServer].[SourceDB].[DBO].[TEST_TRANSACTIONS_SOURCE] BEGIN TRY BEGIN TRANSACTION INSERT INTO [DBO].[TEST_TRANSACTIONS_TARGET] ([Id] ,[InternalId] ,[WebReference] ,[CompanyCode] ,[PolicyType] ,[AffinityCode] ,[RequestType] ,[RequestXML] ,[ResponseXML] ,[ElapsedTime] ,[DateCreated] ,[EmailSent] ,[IsError]) SELECT TOP 10 [Id] ,[InternalId] ,[WebReference] ,[CompanyCode] ,[PolicyType] ,[AffinityCode] ,[RequestType] ,[RequestXML] ,[ResponseXML] ,[ElapsedTime] ,[DateCreated] ,[EmailSent] ,[IsError] FROM #Test DELETE S FROM [SourceServer].[SourceDB].[DBO].[TEST_TRANSACTIONS_SOURCE] S INNER JOIN #TEST T ON S.ID = T.ID COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH

Error iam getting is as below
OLE DB provider "SQLNCLI11" for linked server "DIS-DOCK-01" returned message "No transaction is active.".

All i want is to extract data from Source server and Load target table in target server and delete the data from Source once this has been loaded onto Target.

I dont want to delete the data from source, in case of any error within the Insert Statement.
I used Try catch block but going across the servers this is throwing the error.

Using Sql Server 2014 Standard Editions on both Source and Target Serves

sql query
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

That is a problem with the Distributed Transaction Coordinator (MSDTC). It needs to be configured and running on both servers.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.