Parameters in .NET to OPENROWSET

  • Thread starter Thread starter Adam Smith
  • Start date Start date
A

Adam Smith

Hello. Given the stored proc:

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

Set @ID=12345 + @IN
end
GO

What I can't figure out is how to add an incoming parameter in .NET. My
attempts thus far, which produces an exception on the @1 parameter:

SqlConnection conn = null;
SqlCommand dbCommand = null;
SqlDataReader dbReader = null;

conn = new SqlConnection(settings["LocalDBConnect"]);
conn.Open();
dbCommand = new SqlCommand();
dbCommand.Connection = conn;
dbCommand.CommandText = "SELECT outparam FROM
OPENROWSET('MSDASQL','DRIVER={SQL
Server};SERVER=PS_SQL2000;UID=collector;PWD=co11ection$$', 'declare
@outparam int exec HRXML_DEVIII.dbo.sp_test @1,@outparam OUTPUT select
@outparam as outparam')";

dbCommand.Parameters.Add("@1", SqlDbType.Int).Value = 1;
dbReader = dbCommand.ExecuteReader();//general exception happens here

int n;

dbReader.Read();
n = dbReader.GetInt32(0);
 
Adam Smith said:
Hello. Given the stored proc:

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

Set @ID=12345 + @IN
end
GO

What I can't figure out is how to add an incoming parameter in .NET. My
attempts thus far, which produces an exception on the @1 parameter:

SqlConnection conn = null;
SqlCommand dbCommand = null;
SqlDataReader dbReader = null;

conn = new SqlConnection(settings["LocalDBConnect"]);
conn.Open();
dbCommand = new SqlCommand();
dbCommand.Connection = conn;
dbCommand.CommandText = "SELECT outparam FROM
OPENROWSET('MSDASQL','DRIVER={SQL
Server};SERVER=PS_SQL2000;UID=collector;PWD=co11ection$$', 'declare
@outparam int exec HRXML_DEVIII.dbo.sp_test @1,@outparam OUTPUT select
@outparam as outparam')";

dbCommand.Parameters.Add("@1", SqlDbType.Int).Value = 1;
dbReader = dbCommand.ExecuteReader();//general exception happens here

int n;

dbReader.Read();
n = dbReader.GetInt32(0);
Click to expand...

First of all, why on earth are you connecting to one sql server just to have
it connect to a different one? Why not just connect to the target server
directly? Why are you using OPENROWSET instead of a linked server? And why
not just invoke the stored procedure?

But the answer to your question is that in the command you've sent down @1
is literal text, not a parameter marker. So to get this to work, you would
have to paste the velue of @1 into the query.

As far as your local Sql Server knows your commandText is

"SELECT outparam FROM
OPENROWSET('MSDASQL','DRIVER={SQL
Server};SERVER=PS_SQL2000;UID=collector;PWD=co11ection$$', 'XXXXXXXXXX')";

David
 
Back
Top