June 19, 2008 at 5:28 am
When I try to capture the result set from sp_help_job using the following statement, it works fine:
INSERT dbname.dbo.tablename
EXEC [LinkedServer\Instance].msdb.dbo.sp_help_job @execution_status = 1
GO
If I try the following statement, it fails:
INSERT dbname.dbo.tablename
EXEC msdb.dbo.sp_help_job @execution_status = 1
with this error message:
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.
I'm guessing that this is because sp_help_job calls sp_get_composite_job_info which has an INSERT...EXEC statement itself. Why does this work for the linked server, in that case? How can the result set be captured on a local server? I've tried setting up a linked server pointing back at itself but that doesn't work.
Thanks.
June 20, 2008 at 4:04 pm
I am assuming you are looking for running jobs, I use
execute master.dbo.xp_sqlagent_enum_jobs 1,
'garbage' -- doesn't seem to matter what you put here
and pop this into a temp table and then do a select where state is 1, you could do the same with sp_help_job
June 20, 2008 at 7:08 pm
Try the below command via linked server
exec msdb..sp_get_composite_job_info @execution_status =1
Regards..Vidhya Sagar
SQL-Articles
June 21, 2008 at 12:22 pm
I think openquery or openrowset should allow you to achieve same level of remote-ness as when using a different linked server.
See http://www.sqlservercentral.com/Forums/Topic259078-8-1.aspx#bm259842
June 23, 2008 at 8:16 am
Thanks for the replies. The OPENROWSET method worked perfectly.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply