Passing Parameters through a linked server?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Adam Smith (adam2001usa_at_hotmail.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 14:31:07 -0700

Hello I have a stored proc:

CREATE PROCEDURE dbo.sp_test
@IN int,
@ID int OUTPUT
AS
begin
SET NOCOUNT ON

Set @ID=12345 + @IN
end

GO

I'm executing this through a linked server. The exception returned is:

Could not execute procedure 'sp_test' on remote server
'QAPS2Remote'.\r\n[OLE/DB provider returned message: Parameter type
cannot be determined for at least one variant parameter.]

The literal query being run is:

declare @P1 int
set @P1=NULL
exec QAPS2Remote.HRXMLCollector_prod.dbo.sp_test @IN = 1, @ID = @P1
output
select @P1

How do I declare the parameters using C#? The code I'm using is:

dbCommand.Connection = conn;
dbCommand.CommandType = CommandType.StoredProcedure;

dbCommand.CommandText = "QAPS2Remote.HRXMLCollector_prod.dbo.sp_test";

SqlParameter sqlParameterIn = dbCommand.Parameters.Add("@IN",
SqlDbType.Int);

sqlParameterIn.Direction = ParameterDirection.Input;
sqlParameterIn.Value = 1;
SqlParameter sqlParameterOut =
dbCommand.Parameters.Add("@ID",SqlDbType.Int);
sqlParameterOut.Direction = ParameterDirection.Output;

dbCommand.ExecuteNonQuery();

Thanks in advance.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages