Problem passing parameter into remote stored proc

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 ideas?
Thanks in advance for your help...
Zev Steinhardtzev_steinhardt
what happen if you assign the parameter to a variable?
DECLARE @.var int
SET @.var = @.CustID
EXEC LinkedServer.dbname.dbo.spname @.var
...
AMB
"zev_steinhardt" wrote:

> 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 ideas?
> Thanks in advance for your help...
> Zev Steinhardt
>|||Thanks for the reply, Alejandro.
I tried that. It didn't work.
I even tried to trick it into thinking that it's another variable
altogether. I put the variable into a temp table, declared a new
variable, populated it with the value from the temp table and passed it
in. That didn't work either.
Zev Steinhardt|||zev_steinhardt,
Are you executing the remote sp inside a transaction?
AMB
"zev_steinhardt" wrote:

> Thanks for the reply, Alejandro.
> I tried that. It didn't work.
> I even tried to trick it into thinking that it's another variable
> altogether. I put the variable into a temp table, declared a new
> variable, populated it with the value from the temp table and passed it
> in. That didn't work either.
> Zev Steinhardt
>|||Yes. The remote sp is within a transaction.
Zev|||zev_steinhardt,
you are using a distributed one, correct?
begin distributed transaction
exec ...
AMB
"zev_steinhardt" wrote:

> Yes. The remote sp is within a transaction.
> Zev
>|||Alejandro...
Yes, it is a distributed transaction... and I have XACT_ABORT on
Zev|||zev_steinhardt,
When you execute the remote sp using:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
then you are not executing it using a distributed transaction, that is why
you do not get the error.
See if this helps.
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
AMB
"zev_steinhardt" wrote:

> Alejandro...
> Yes, it is a distributed transaction... and I have XACT_ABORT on
> Zev
>