September 5, 2008 at 10:38 am
Hey guys, having a brain fart here ...
I'm building a job activity monitor report, and to get the current run status, I'm dumping the results of xp_sqlagent_enum_jobs into a temp table. Well ... reporting services isn't liking this, and I'm not sure how to get around it. I can't do an insert into exec with a temp table variable, so I'm not sure what other options I have right now.
Thanks in advance
September 5, 2008 at 10:53 am
Hello,
I'm not sure what the issue is, as the following works on my SQL 2005 Instance. (Okay the Column DataTypes need correcting, but this was just a quick test).
Use tempdb
Go
/* Create Local tables */
Create Table tempdb.dbo.Temp05109
(
[Fie1d1] varchar(100),
[Fie1d2] varchar(100),
[Fie1d3] varchar(100),
[Fie1d4] varchar(100),
[Fie1d5] varchar(100),
[Fie1d6] varchar(100),
[Fie1d7] varchar(100),
[Fie1d8] varchar(100),
[Fie1d9] varchar(100),
[Fie1d10] varchar(100),
[Fie1d11] varchar(100),
[Fie1d12] varchar(100),
[Fie1d13] varchar(100)
)
Go
Insert Into tempdb.dbo.Temp05109
Execute xp_sqlagent_enum_jobs 1, ''
Select * From tempdb.dbo.Temp05109
Drop Table tempdb.dbo.Temp05109
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 5, 2008 at 10:57 am
This works for me as well:-
Use tempdb
Go
/* Create Local tables */
Declare @TempTbl table
(
[Fie1d1] varchar(100),
[Fie1d2] varchar(100),
[Fie1d3] varchar(100),
[Fie1d4] varchar(100),
[Fie1d5] varchar(100),
[Fie1d6] varchar(100),
[Fie1d7] varchar(100),
[Fie1d8] varchar(100),
[Fie1d9] varchar(100),
[Fie1d10] varchar(100),
[Fie1d11] varchar(100),
[Fie1d12] varchar(100),
[Fie1d13] varchar(100)
)
Insert Into @TempTbl
Execute xp_sqlagent_enum_jobs 1, ''
Select * From @TempTbl
www.sql.lu
SQL Server Luxembourg User Group
September 5, 2008 at 10:59 am
Adam,
Are you doing this:-
create table #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
insert into #enum_job
exec master.dbo.xp_sqlagent_enum_jobs 1,test
select * from #enum_job
drop table #enum_job
MJ
September 5, 2008 at 11:04 am
Figured it out … I guess? Using the wizard, it doesn’t like temp tables, but as is in a dataset, it worked fine.
September 5, 2008 at 12:03 pm
SSRS hates temp tables. When it needs to update it's schema information, it runs your query or stored procedure, but it runs it with the parse only option on. This means the procedure does not actually run, so when the parsing engine gets to the first part that needs the temp table, the temp table does not exist because the code to create it was parsed, not actually run.
I usually create a stub at the top of procedures that returns NULL values or a single row of all of the appropriate data types. Build the report from the stub, and then once the report is deployed take the stub out and replace it with the actual procedure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply