storing stored procedure results in SQL table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

storing stored procedure results in SQL table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-04 : 08:19:57

Dan writes "I need to store the results of the following SQL into a temporary/permanent table for future reference. Any assistance would be greatly appreociated.

EXECUTE MSDB.dbo.sp_help_job @execution_status=1"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-04 : 12:51:13

you need to create a temp table with the correct structure - see sp_get_composite_job_info for that

then
insert #tmp
exec MSDB.dbo.sp_help_job @execution_status=1

I have a feelng that this won't work because there are exec's inside the sp so maybe.

insert #tmp
select * from openquery(self,'exec MSDB.dbo.sp_help_job @execution_status=1')
where self is a linked server to your local server. Could use openrowset instead.

Could also probably

select * into #tmp
from openquery(self,'exec MSDB.dbo.sp_help_job @execution_status=1')

Another option is to look at the code in sp_help_job and sp_get_composite_job_info and duplicate that in your own SP.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 07/04/2003 13:23:19

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-07-04 : 12:58:57

You can use INSERT..EXEC but you need a table with the right structure. A shortcut to manually creating the structure is

select * into result_table
from openrowset('SQLOLEDB'
            , 'SERVER=(local);Trusted_Connection=Yes'
            , 'set fmtonly off exec msdb..sp_help_job @execution_status = 1')

You can now script the result_table and use it as a temp or permanent table definition for your insert exec. Be aware that the above can be fairly flaky and sometimes you'll just have to do it the hard way.

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

philh
Starting Member

18 Posts

Posted - 2003-09-03 : 09:35:34

Um, no, neither of these approaches work. There is an INSERT ... EXEC statement in sp_help_job (sp_get_composite_job_info) already, so you get a nesting error:

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

You can't access the sp through a linked server; all you get are the tables:

Server: Msg 7357, Level 16, State 2, Line 43
Could not process object 'exec msdb.dbo.sp_help_job @enabled=1'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

Anybody else? I really need this function, but I suppose I can do other ugly stuff as a workaround.

tks,

Phil Hegedusich
Senior Web Developer
IIMAK
http://www.iimak.com
My views are not necessarily my own.

philh
Starting Member

18 Posts

Posted - 2003-09-03 : 09:47:26

Erm, yes it does.

Add the FOUR-part reference to the SP call, and you're gold.

Phil Hegedusich
Senior Web Developer
IIMAK
http://www.iimak.com
My views are not necessarily my own.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources