How to Using Variable in OPENROWSET T-SQL

Hi,
How to use variable as parameter in stored procedure using OPENROWSET
'
I try the query below, but get an error. Please help me.
DECLARE @.job varchar(5)
SET @.job='RefreshTLMReport'
SELECT *
FROM
OPENROWSET('sqloledb'
, 'server=reportsvr;trusted_connection=yes
'
, 'set fmtonly off exec msdb..sp_help_job @.job_name='
+ @.job + ')'
ThanksResant
declare @.par int,@.sql varchar(8000)
set @.par=1
set @.sql ='exec mysp ' + cast(@.par as varchar(10))+''''
EXEC ('select *
from
OPENROWSET(''SQLOLEDB'',''SERVER=name;DA
TABASE=pubs;UID=sa;PWD=pass;'',''set
fmtonly off; ' + @.sql+')')
"Resant" <resant_v@.yahoo.com> wrote in message
news:1131512827.293294.303190@.g44g2000cwa.googlegroups.com...
> Hi,
> How to use variable as parameter in stored procedure using OPENROWSET
> '
> I try the query below, but get an error. Please help me.
> DECLARE @.job varchar(5)
> SET @.job='RefreshTLMReport'
> SELECT *
> FROM
> OPENROWSET('sqloledb'
> , 'server=reportsvr;trusted_connection=yes
'
> , 'set fmtonly off exec msdb..sp_help_job @.job_name='
> + @.job + ')'
>
> Thanks
>|||Hi
sp_help_job will not return a single resultset therefore you can't use it in
OPENQUERY. Your code is also truncating the jobname to 5 characters. Try
DECLARE @.job sysname
SET @.job='RefreshTLMReport'
EXEC reportsvr.msdb..sp_help_job @.job_name=@.job
John
"Resant" wrote:

> Hi,
> How to use variable as parameter in stored procedure using OPENROWSET
> '
> I try the query below, but get an error. Please help me.
> DECLARE @.job varchar(5)
> SET @.job='RefreshTLMReport'
> SELECT *
> FROM
> OPENROWSET('sqloledb'
> , 'server=reportsvr;trusted_connection=yes
'
> , 'set fmtonly off exec msdb..sp_help_job @.job_name='
> + @.job + ')'
>
> Thanks
>|||I think it's much better, but thanks all.
DECLARE @.job varchar(5)
SET @.job='RefreshTLMReport'
SELECT *
FROM
OPENROWSET('sqloledb'
, 'server=reportsvr;trusted_connection=yes
'
, 'set fmtonly off exec msdb..sp_help_job')
WHERE name=@.job|||Hi Resant
This will produce different results to running it directly and specifying
the @.job_name!
You have still declared @.job as varchar(5) instead of a sysname.
John
"Resant" wrote:

> I think it's much better, but thanks all.
> DECLARE @.job varchar(5)
> SET @.job='RefreshTLMReport'
> SELECT *
> FROM
> OPENROWSET('sqloledb'
> , 'server=reportsvr;trusted_connection=yes
'
> , 'set fmtonly off exec msdb..sp_help_job')
> WHERE name=@.job
>sql