Hi,
I trying to use the system stored procedures to do some job processing.
I want to run the sp_help_job and get the output into a table/cursor

Any thoughts on how I might accomplish this.

Thanks

Use this approach:

create table #Temp (
[name] varchar(255),
[db_size] varchar(255),
[owner] varchar(255),
[dbid]  varchar(255),
[created] varchar(255),
[status] varchar(255),
[compatibility_level] varchar(255))

insert #Temp
exec sp_helpdb

Andre

Andre
Thanks for the suggestion.
Unfortunately it didn't work.

This is the pfrag
create temp table...
insert  #sys_jobs_output execute sp_help_job

and the response
Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info,
Line 67
An INSERT EXEC statement cannot be nested.

(0 row(s) affected)

Analysis
Seems that the Sp_help_job calls the sp_get_composite that creates
additional temp table, thus the nested error.


I also tried the openquery/openrowset approach and both failed.

Any other thoughts?
Thanks

Andre,

Thanks again for the suggestion.

I finally realized that I could cheat and create copies of the MS procedures
sp_help_job, sp_get_composite_info and comment out the final step that
deletes the temporary tables.
This way, I can use the provided functionality, and leave the data around
for the next process.

Thanks again.

Glad you figured it out.  I've done similar "cheats" in the past myself.  I
created my own copy of sp_who2 so I could check activity by a certain user.
Interestingly enough, I just saw the same thing described in a SQL Server
Mag article.  Maybe we're not such hacks after all. :)

Andre

Try this link :

http://tinyurl.com/5btxz -- cyclop ------------------------------------------------------------------------ Posted via http://www.webservertalk.com ------------------------------------------------------------------------ View this thread: http://www.webservertalk.com/message960490.html

Running SQL database off of SAN -- is it feasible ?
KDC Problem
Reclaim space back
Quick question about rowguid column
This is probably a simple question, but...