February 18, 2010 at 1:28 pm
here is my issue - I am trying to execute a stored procedure over a sybase server link and populate a temp table from the remote data source
when I execute the following command, I can view the result set in management studio
DECLARE @cmdVARCHAR(6000)
DECLARE @BU_IDCHAR(4)
DECLARE @last_poll_timeVARCHAR(35)
SET@BU_ID= '1326'
SET@last_poll_time= CONVERT(VARCHAR,GETDATE(),120)
SET@cmd= 'mydb..sp_my_sybase_proc @BU_ID = '
+ @BU_ID +
', @last_run_time = "'
+ @last_poll_time +
'"'
EXEC (@cmd) AT REMOTESYBASE
however as soon as I try and capture the result set into either a temp table or a static table
INSERT #my_temp_table
EXEC (@cmd) AT REMOTESYBASE
SQL throws and error
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "REMOTESYBASE" was unable to begin a distributed transaction.
Any Ideas?
February 19, 2010 at 5:23 am
INSERT...EXEC is just a fancy INSERT (with some annoying restrictions, but they're not relevant here). Every data modification command, like INSERT, UPDATE, and DELETE runs in a transaction. When it's just a single command, like your example, it's called an implicit transaction.
So, your INSERT is broadly equivalent to:
BEGIN TRANSACTION;
INSERT...EXEC;
COMMIT TRANSACTION;
When everything happens locally, SQL Server just takes care of it for you. When you use a linked server however, the implicit local transaction has to be escalated to a distributed transaction (at least it does prior to 2008, but that's another story).
When SQL Server attempts to escalate to a distributed transaction, it encounters an error since the provider you are using to talk to the Sybase server does not support distributed transactions.
The first query works since it is just a pass-through procedure execution - no data modification is involved, so no transaction is required.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply