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.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-09-23 : 12:46:15
|
| I'd like to check for the existence of a job with the following SP code:DECLARE @JOBNAME VARCHAR(200)DECLARE @resultcode INTSET @JOBNAME = ' This is a job for DB: '+'MyDB'EXEC @resultcode = msdb.dbo.sp_help_job @job_name= @JOBNAMEprint @resultcodeThis works great, if @resultcode is 0 then job exists.Since this SP is not run from QA, how are the result columns from the sp_help_job accessed? More generally, how does any SP refer to the resulting dataset returned in an EXEC?SamC |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-23 : 12:50:33
|
| You can create a temp table with the format expected from the sp then insert into itinsert #a exec ....But why not just look at the sp_help_job SP in msdb and use the bits of it that you need.==========================================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. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-23 : 13:24:38
|
| Nigel,First thanks for elaborating on how result sets are obtained from a EXEC SP command. It's dirty to use a temp table, but works.I had tried other techniques to obtain the existence of the job, but permission became a problem. The returncode of sp_help_job is all I need at this time.Thanks,Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-23 : 13:36:03
|
quote:
It's dirty to use a temp table...
Why??? |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-09-23 : 15:36:47
|
| I referred to the temp table as a 'dirty' solution relative to the handling of result sets returned by a SELECT. Unlike SELECT, EXEC result sets can't be worked into SQL as a subquery.I'm glad to have learned of this workaround to EXEC. This technique of dealing with a result set requires a temp table, deletion of the temp table (cleanup - hence the dirty remark).SamC |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-23 : 15:56:46
|
quote:
I had tried other techniques to obtain the existence of the job, but permission became a problem
You should be able to query msdb.dbo.sysjobs in a proc without explicitly granting SELECT to the proc consumer ...Jay White{0} |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-23 : 18:52:49
|
| Would probably have to put the proc in msdb and grant permission on that to do it though.==========================================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. |
|
|
|
|
|
|
SP Check for existence of Job