OpenRowset - Passing Variables/Parameters into Stored Procedure

OpenRowset - Passing Variables/Parameters into Stored Procedure

Post by P » Fri, 23 Nov 2001 03:21:52



I have read other posts on this topic but have not been able to locate
a solution.  I'm trying to pass in variables into the OpenRowset
function that is used in a MS SQL Stored procedure.  Here's the code:


        SELECT s.*
        FROM clarify..table_site as s
        JOIN OPENROWSET('SQLOLEDB','servername';'login';'password',

AS a
        on s.objid = a.iobjid2site


What is the proper sytnax for including variables/parameters into the
OPENROWSET function?  I tried using 'exec

I also tried adding additional single quotes around the parameter
names. I also tried declaring variables in the sp_permissionsget_test
procedure and then reference these in the OPENROWSET  function.

Any info would be greatly appreciated!

TIA
PD

 
 
 

OpenRowset - Passing Variables/Parameters into Stored Procedure

Post by oj » Fri, 23 Nov 2001 03:42:31


pd,

the parameters are not resolved in openrowset(). you would need to construct
a string and execute it.

exec('SELECT s.*
FROM clarify..table_site as s
JOIN OPENROWSET(''SQLOLEDB'',''servername'';''login'';''password'',


AS a
on s.objid = a.iobjid2site')

-oj


> I have read other posts on this topic but have not been able to locate
> a solution.  I'm trying to pass in variables into the OpenRowset
> function that is used in a MS SQL Stored procedure.  Here's the code:


> SELECT s.*
> FROM clarify..table_site as s
> JOIN OPENROWSET('SQLOLEDB','servername';'login';'password',

> AS a
> on s.objid = a.iobjid2site


> What is the proper sytnax for including variables/parameters into the
> OPENROWSET function?  I tried using 'exec


> I also tried adding additional single quotes around the parameter
> names. I also tried declaring variables in the sp_permissionsget_test
> procedure and then reference these in the OPENROWSET  function.

> Any info would be greatly appreciated!

> TIA
> PD