Creating a temp table from the results of a query executed within a pr

[aolb]

I am using SQL Server 2000 and I want to report on the steps in a job by using a query. There is the procedure sp_help_jobhistory in the msdb database which returns what I want but I need to put the results into a temp table to be able to use them.

Is there a way of creating a temp table from the results of SQL generated in a query?

My second option is to replicate the query the procedure generates but as it refers to system tables I understand that these cannot be guaranteed to remain the same as the can change between service pack versions. This is why I am looking at putting the results of a query generated from a procedure into a temp table if possible… though I think I’m onto a non starter?

 

[hmckillop]

Create a global temp table in the format you are expecting the results

try this

CREATE TABLE [##ResultsTable] (
	job_id	uniqueidentifier	NULL,
	job_name	sysname ,
	run_status	int ,
	run_date	int ,
	run_time	int ,
	run_duration	int ,
	operator_emailed varchar(100),
	operator_netsent varchar(100),
	operator_paged varchar(100),
	retries_attempted int,
	servername varchar(100),

) ON [PRIMARY]
GO

insert into ##ResultsTable
exec msdb..sp_help_jobhistory

obviously you want to create the global temp table or specific table in whatever db you want it in. but you should be able to work it out.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[aolb]

thanks, just the trick