April 10, 2002 at 8:57 am
I want to capture the results of sp_help_job into a temporary table for further processing, but it contains INSERT INTO..EXEC statements and SQL Server doesn't allow these to be nested. Is there a way to get the data with a cursor or something? Everything that I've tried fails syntax or returns the 8164 nesting error.
Guess I'll mention that the INSERT INTO..EXEC statements are in sp_get_composite_job_info which is called by sp_help_job, if that matters.
April 10, 2002 at 10:27 am
Pretty interesting. No ideas yet on a good fix, a workaround would be to grab the logic from the proc and just dump into a new one you create that does all the work plus whatever you need, then returns the recordset (or just outputs to a table for you).
Andy
April 10, 2002 at 2:15 pm
Yes, that would work. I found another idea using OPENROWSET:
insert into #jobtbl (<<many columns>>) from
(select a.* from OPENROWSET('SQLOLEDB','USER';'PW','exec msdb..sp_help_job') as a
This appears to work but I'm not pleased with the user/pw in the script. Yeah, I may clone that proc. Thanks.
April 10, 2002 at 3:23 pm
Gong! The OPENROWSET solution isn't working. Thought I saw it work but more tests show a SQLOLEDB error about object has no columns. Puzzling.
btw, I clipped the last ) - should read "... as a)"
I'll stop answering my own post now.
April 10, 2002 at 4:53 pm
Thanks for the follow up. When you get the script going we'll have room for it in the script library!
Andy
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply