Stored Proc - Distributed Transaction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc - Distributed Transaction

Author

Topic

boogiewoogie
Starting Member

7 Posts

Posted - 2008-07-02 : 12:54:38

Hey all,

I'm hoping someone can share their expertise in helping me resolve my problem
with a stored proc invoked in a C# windows app. The stored proc updates
several fields in a local database as well in a remote database. The stored
proc is similar to the following:

-- UPDATE local table
UPDATE TABLE_A
SET COL_0 = @VALUE0
WHERE COL_9 = 1

-- UPDATE remote table
UPDATE [REMOTE-SERVER].[DB].[dbo].TABLE_R
SET [REMOTE-SERVER].[DB].[dbo].TABLE_R.COL_1 = @VALUE1
FROM [REMOTE-SERVER].[DB].[dbo].TABLE_R r
INNER JOIN TABLE_A a on a.ID=r.ID

Now, when executing the stored proc within MS SQL Server Management Studio,
the stored proc successfully runs. However, when invoked in my C# app, it fails
with the following 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].

The stored proc is running under SQL Server 2005, Windows 2003 server. And the
remote/linked SQL Server is running under SQL Server 2000, Windows 2000 server.

I've researched for the last few days, ensured MS DTC is configured correctly
according to the Microsoft KB and running and so forth for both servers (per
our DBA).

I figured if it's running without issue in Management Studio, then MS DTC
wouldn't be the source of the problem. I'm thinking that it may be on the
application-side that I might be missing. My box is running Windows XP Pro
with DTC configured and running correctly per the DTCTester.exe app from
Microsoft.

Your help is greatly appreciated.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-02 : 13:10:16

see if this helps:http://support.microsoft.com/kb/873160

boogiewoogie
Starting Member

7 Posts

Posted - 2008-07-02 : 13:38:22

Thanks, sodeep. I've read through this and verified the settings before (a few times in fact). I followed through the instructions again after your post and I'm pretty confident I have that nailed down. But, I'm still running into the problem when I invoke the stored proc in my application.

boogiewoogie
Starting Member

7 Posts

Posted - 2008-07-16 : 11:39:48

I've resolved this issue and figured I'd share it with the community. In my case, it was a name resolution issue. After replacing the server name with the ip address, it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources