temp table

[sardinka2]

I am trying to create a temp table. What am I doing wrong?
insert into #job
exec msdb..sp_help_job @job_name='.dba.archive.baks',@job_aspect ='steps'

 

[VBrit]

format is:

Insert into #(tablename)
(fieldlist) values (valuelist)

or

insert into #(table name) select x,y from z

You can't run stored procedures in T-sql in line statements because they return a recordset that the T-sql does not know about.

I suggest that if you need that table, look at the stored proc sp_help_job and copy the t-sql in that procedure to create your table.

 

[dbomrrsm]

Here is one I did earlier:

Declare @tablecount int
Set @tablecount = (Select count (*) from #TEMPCUST)
CREATE TABLE #TEMPCOUNT (RECORD int)
INSERT INTO #TEMPCOUNT
EXEC sp_Gen_Random_Numbers 1,@tablecount,10000

You need to make sure your temp table columns are of a data type that the Exec will return and that the Exec return is in the order of the columns in your temp table.

Good luck

[blue]DBomrrsm[/blue]

 

[sardinka2]

can you help me write one. basically I need to know when job was last time was executed, with fail or susecc, and next run time.

 

[dbomrrsm]

Create table #job (FirstField varchar (30),SecondField varchar (30),

continue here with all the fields that msdb..sp_help returns with the appropriate data types with commas in between then end with a

)

THEN

insert into #job
exec msdb..sp_help_job @job_name='.dba.archive.baks',@job_aspect ='steps'

You can then do a select on #job as follows:

[code]Select *
From #job
Where (your criteria)

[blue]DBomrrsm[/blue]