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
