December 17, 2012 at 11:47 am
Hi all,
I am getting an error and hope you can point me to a right direction to resolve it.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL BEGIN DROP TABLE #temp END;
BEGIN TRANSACTION;
BEGIN TRY
SELECT * INTO #temp
FROM [linked_server].db.schema.A where col_1 = 'a'
INSERT INTO [linked_server].db.schema.B SELECT * FROM #temp;
DELETE FROM [linked_server].db.schema.A WHERE col_1 IN (SELECT col_1 FROM #temp)
END TRY
BEGIN CATCH
SELECT @@TRANCOUNT AS open_tran_count
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
END CATCH
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION;
END;
Returns:
OLE DB provider "SQLNCLI10" for linked server "linked_server" returned message "No transaction is active.".
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Thanks,
December 17, 2012 at 12:14 pm
You need to enable and utilize distributed transactions. http://msdn.microsoft.com/en-us/library/ms188386%28v=sql.105%29.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 17, 2012 at 2:04 pm
Thanks for reply,
I've changed "begin tran" to "BEGIN DISTRIBUTED TRAN", but still getting the same error.
Is there anything else I need to change?
December 17, 2012 at 2:26 pm
eugene.pipko (12/17/2012)
Thanks for reply,I've changed "begin tran" to "BEGIN DISTRIBUTED TRAN", but still getting the same error.
Is there anything else I need to change?
If that is all you did I would recommend you go back and actually read the article. There is some configuration you need to complete too. It is all detailed in that article.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply