February 18, 2005 at 11:13 am
i ha this after update trigger that calls a local sp. the local sp then calls a remote sp. i keep getting this error message:
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].
when i try to exeute the local sp by itself, it works fine and there are no problems calling the remote sp -- the problem only seems to appear when i try to make the call from the trigger.
any one have any ideas - any help would be appreciated.
thanks
annie
February 18, 2005 at 3:18 pm
Is the DTC service running on the remote system?
Can you make a call to the remote SP outside of the trigger?
February 18, 2005 at 3:56 pm
Yes, DTC service is running on the remote server.
The local stored proc makes the call to the remote sp and when i execute that by itself, it works fine. The problem occurs when the trigger calls the local stored proc.
I thought that perhaps there was a problem with this particular trigger but i tried creating other triggers on other tables that call a local sp which in turn calls the remote sp but i get the same error message.
I can make a call to the remote server just fine outside the trigger either through a stored proc or a select stmt.
When i use our proudction database as the remote server, the trigger works fine - the call to the remote sp is successful. The problem occurs when using our development server as the remote server.
I am trying to figure out what server settings could affect this. I already modified nested trigger and remote proc trans properties on our development server so that its the same as whats on our production server.
anyone have any suggestions.
February 22, 2005 at 4:04 pm
Well, since the trigger works against the production server but fails against the development server, I'd be curious if there's something different about each environment and/or the version of SQL on each server.
1. Check the version of SQL on your production and development boxes [i.e., Enterprise, Standard, Developer, etc.] Are you (perhaps) using the developer version for your development box? Can check this by running "SELECT @@VERSION".
2. (By chance) are your production and development servers in different Windows domains? Or is security set up differently on each box?
- john
February 23, 2005 at 12:17 am
Are either of the boxes Server 2003? If so, you might want to check this out.
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply