SQL Server DBAs! Please help!

[zev_steinhardt]

I know that this isn't a database board, but I'm posting on the off-chance that someone can help me out here.

I'm having a problem passing a parameter value into a stored procedure
that I am running on a remote (linked) server, and am receiving a DTC
error because of it.

I have a stored procedure that brings in a variable (@CustID int). I
later pass that parameter to another stored procedure. The code looks
like this...


EXEC LinkedServer.dbname.dbo.spname @CustID


When I run that, I get this error...


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


However, if I hard-code the parameter, it works:


EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.


I can even do this:


DECLARE @var int
SET @var = 1234
EXEC LinkedServer.dbname.dbo.spname @var -- this works too.


But if I accept the variable as an input parameter to my stored
procedure, I get the error listed above.


Any help you can provide would be greatly appreciated.

Zev Steinhardt

[Athena]

My first thought is that @CustID has some sort of bad value (a null maybe?) that is causing the error you see. Try dumping @custid and seeing what it looks like.

My second thought is that maybe it's something caused by the remote connection. Is there any way you can test it locally?

I also see this (http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql) from MS about the error. Anything ring a bell there?

[Armilla]

Sounds like a permissioning problem with the DTC. Have a look at the MS KB article below and see if that helps:

http://support.microsoft.com/default.aspx?scid=kb;en-us;329332&Product=sql

[cwthree]

I'm with Armilla.

What version of SQL Server, including any service packs, are you using on each server? I ran into a similar problem with distributed transactions in SQL Server 2000. As I recall, one server had SQL Server service pack 3a and the other didn't. Installing SP3a on the lower version server corrected the problem.

[crazyjoe]

Definitely check the service packs. Distributed transactions and linked servers start to go REALLY wierd sometimes if both servers aren't patched in just the right way. It's one of the more annoying features (heh, features) of SQL Server. All of a sudden out of nowhere you'll get some wierd OLE DB messages between two servers that were happily talking to each other only minutes ago.

[zev_steinhardt]

Thanks for the help everyone. We eventually decided that transactional processing wasn't all that important and that if only some of the statements go through and not others, it's not all that bad.

We never did figure out what the problem was in the end. The DTC was properly set up on both machines.

Zev Steinhardt