Getting stored procedure output into a table

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
Doug
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
sql