Linked server and transaction

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: DaveK (anonymous_at_discussions.microsoft.com)
Date: 06/03/04 

Date: Thu, 3 Jun 2004 02:31:03 -0700

Hi,
I have a problem I'd like some advice on. Here is the situation. I have an sp which queries data using a linked server. Running the sp works fine. Now I've read about the loopback and transaction issue but I can't explain this:

For whatever reason one of our developers has to run a number of sp's and put the result into a temp table and then output those results. It shouldn't matter what the sp is because it's just a simple select from two sql server (all 8.00.919).

The following works very quickly:

DECLARE @Sql VARCHAR(100)
SELECT @Sql = 'MyLinkedServerSp'
EXEC (@sql)

It returns an integer in about 1 second.
However, the following errors after about 4 minutes:

CREATE TABLE #test (Col1 INT)
DECLARE @Sql VARCHAR(100)
SELECT @Sql = 'MyLinkedServerSp'
INSERT INTO #test
EXEC (@sql)

The error is:

Server: Msg 7391, Level 16, State 1, Procedure MyLinkedServerSp, Line 40
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Why does that fail but the first one work and where is the distributed transaction being instigated? This is all via SQL Query Analyser.

Thanks

Davek
http://www.sqlporn.co.uk

Relevant Pages

  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: DB API 2.0 and transactions
    ... CURRENT_TIMESTAMP within a transaction should be the same. ... manadate that CURRENT_TIMESTAMP in only evaluated once in each SQL ... transaction-initiating SQL statement takes place. ... src = self.__cnx.source ...
    (comp.lang.python)
  • OLE DB provider MSIDXS reported an error. Access denied.
    ... I've added a linked server to a development box. ... I ran the same command on my local Sql box. ... OLE DB error trace [OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare ... The development sql instance is running under a local system account. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: commit work not working?
    ... Because he is using straight sql, ... The transaction is made up of the statements that occur ... locks the stock table, updates rows in the stock table ... The database server must perform this sequence of operations either ...
    (comp.databases.informix)
  • Re: Locking and Delay in a Bottleneck
    ... that should avoid the gap issue on rollback, ... SQL Server MVP ... the saving transaction, this journal voucher is having a header table ... number in a table whereupon you commit. ...
    (microsoft.public.sqlserver.programming)