Get a variable from a sp_ - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Get a variable from a sp_

Author

Topic

LanceS
Starting Member

5 Posts

Posted - 2004-01-27 : 17:14:56

I am trying the the variable @thisjobStatus from a ystem stored proc to find our if the job is idle. I can get the stored proc to return the proper result set but don't know how to get the value of the current_execution_status into the variable. Your help is very apprieciated. Here is the current script please help me finish it.

DECLARE @thisjobStatus INT
, @thisJobID UNIQUEIDENTIFIER

SELECT @thisJobID = job_id FROM msdb..SysJobs WHERE Name = 'MyDTS'

EXECUTE msdb..sp_get_composite_job_info @thisJobID

SELECT @thisjobStatus = NULL

WHILE @thisjobStatus <> 4
BEGIN
EXECUTE msdb..sp_get_composite_job_info @thisJobID
SET @thisJobStatus = current_execution_status (from the sp_)
END

PRINT @thisJobStatus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 17:18:27

You need to put the results of the stored procedure into a temp table:

CREATE TABLE #Temp
(

)

INSERT INTO #Temp
EXEC msdb..sp_get...

SELECT @thisJobStatus = current_execution_status
FROM #Temp
WHERE...

DROP TABLE #Temp

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 17:32:02

That isn't going to work. You are going to get an error about the INSERT EXEC not being able to be nested. Take a look at this:

http://dbforums.com/arch/29/2003/2/692967

Tara

LanceS
Starting Member

5 Posts

Posted - 2004-01-27 : 17:38:27

When I do that I get the following error:

An INSERT EXEC statement cannot be nested.

Here is the full script:

DECLARE @thisjobStatus INT
, @thisRowCount INT
, @thisJobID UNIQUEIDENTIFIER
, @thisSQL VARCHAR(5000)

SELECT @thisJobID = job_id FROM msdb..SysJobs WHERE Name = 'MyDTS'

CREATE TABLE #temp(
job_id UNIQUEIDENTIFIER
, originating_server VARCHAR(100)
, name sysname
, enabled int
, description VARCHAR(100)
, start_step_id INT
, category VARCHAR(100)
, owner VARCHAR(100)
, notify_level_eventlog INT
, notify_level_email INT
, notify_level_netsend INT
, notify_level_page INT
, notify_email_operator INT
, notify_netsend_operator INT
, notify_page_operator INT
, delete_level INT
, date_created DATETIME
, date_modified DATETIME
, version_number INT
, last_run_date VARCHAR(100)
, last_run_time VARCHAR(100)
, last_run_outcome INT
, next_run_date INT
, next_run_time INT
, next_run_schedule_id INT
, current_execution_status INT
, current_execution_step VARCHAR(100)
, current_retry_attempt INT
, has_step INT
, has_schedule INT
, has_target INT
, type INT
)
INSERT INTO #Temp
EXECUTE msdb..sp_get_composite_job_info @thisJobID

SELECT @thisjobStatus = current_execution_status FROM #Temp

PRINT @thisJobStatus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 17:41:28

Yep, can't use the temp table. Check out the link that I posted.

Tara

LanceS
Starting Member

5 Posts

Posted - 2004-01-29 : 14:25:29

Thanks Tara I found another way around this.

In case it helps anyone else:

SELECT @thisJobID = job_id FROM msdb..SysJobs WHERE Name = @thisDTS

SELECT @thisInitDate = run_Date
, @thisInitTime = run_time
FROM msdb..sysjobhistory
WHERE Job_ID = @thisJobID
AND Instance_ID = (SELECT MAX(Instance_ID) FROM msdb..sysjobhistory WHERE Job_ID = @thisJobID)

EXEC msdb..sp_start_job @job_name = @thisDTS

SELECT @thisNewDate = @thisInitDate
, @thisNewTime = @thisInitTime

PRINT @thisInitDate
PRINT @thisInitTime

WHILE @thisInitDate = @thisNewDate AND @thisInitTime = @thisNewTime
BEGIN
SELECT @thisNewDate = run_Date
, @thisNewTime = run_time
FROM msdb..sysjobhistory
WHERE Job_ID = @thisJobID
AND Instance_ID = (SELECT MAX(Instance_ID) FROM msdb..sysjobhistory WHERE Job_ID = @thisJobID)
END

SMerrill
Posting Yak Master

206 Posts

Posted - 2007-04-06 : 15:56:31

Tara, your link is expired. what is that reference?

~ Shaun Merrill
Seattle, WA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-06 : 16:09:10

quote:
Originally posted by SMerrill

Tara, your link is expired. what is that reference?

~ Shaun Merrill
Seattle, WA

I have no idea. It was 3 years ago!

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources