January 19, 2005 at 12:09 pm
I've been trying to find a decent way to do the equivalent of
SELECT *
INTO #MyTable
FROM EXEC msdb.dbo.sp_help_job
I've tried OPENQUERY for the EXEC, no go. Tried external tools like Access, can't save to SQL. Tried DTS, won't build the table automatically. I know I can insert the data using
INSERT #MyTable
EXEC msdb.dbo.sp_help_job
But is there any other way to create a table automatically?
January 19, 2005 at 12:13 pm
not that I am aware of, it would be great if someone had a solution however
January 19, 2005 at 3:25 pm
specifically sp_help_job or in general? It can be done very easily with stored procedures that return a single result set, but sp_help_job returns multiple result sets which prevent you from saving the output in a table. If you are looking for only certain output from sp_help_job, you may be able to script the procedure in Query Analyzer, parse out the part you need into a new procedure, then save the output of your "new" sp_help_job in a table.
Here's an example of how to save the output to a table...
create table #who2
(
#SPID int NULL,
#Status varchar(30) NULL,
#Login sysname NULL,
#HostName sysname NULL,
#BlkBy varchar(128) NULL,
#DBName sysname NULL,
#Command varchar(128) NULL,
#CPUTime int NULL,
#DiskIO int NULL,
#LastBatch char(14) NULL,
#ProgramName sysname NULL,
#SPIDb int NULL)
insert #who2
exec sp_who2
-- Steve
January 20, 2005 at 6:37 am
Steve,
The answer is in general. As I stated, I'm aware that you can create the table first and then insert exec into it.
Sorry for the confusion caused by using sp_help_job. I use it as an example since the first result set (the one you get when you supply a job name or job id parameter) has around 30 columns, and the output is not easily parsed from the sp_helptext output. It calls some xp_ procs in a non-obvious way.
The original goal was simply to fire off a job using sp_start_job, then loop on a WAITFOR DELAY till it finished. sp_help_job is the only reasonable way I can see to tell if a job is still executing or not.
It seemed to me there must be some tricky way using some tool to create a table based on an SP result set, so that's what I'm looking for...
January 20, 2005 at 12:07 pm
I've been known to chastise people for not thoroughly reading a question before posting a reply. Guess I shouldn't do that anymore. Sorry.
This still isn't what you asked for, but at least I'm warning you in advance this time! Here's a procedure that will tell you whether a job is running, based on the part of sp_help_job that does that... hope its useful to you! Steve
(usage code follows the procedure)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE af_Job_Status
( @job_name sysname,
@Status tinyint OUTPUT)
AS
SET NOCOUNT ON
/*************************************************************/
--
-- Module Name: af_Job_Status
--
-- Description:
-- 1 input parm(s).
-- @job_name sysname : full name of the job
-- 1 output parm(s).
-- @status tinyint : 1 if the job is running, 0 if not.
--
-- Procedure description.
-- Procedure accepts a job name and reports back whether the
-- job is currently executing.
--
-- Written By: Steve Phelps
--
-- Date: OCT 19, 2004
--
-- Modified :
-- Date:
--
-- USAGE:
--
-- exec af_Job_Status @job_name, @status OUTPUT
--
/*************************************************************/
-- -- the following declare must be removed. its here for testing.
-- DECLARE
-- @job_name sysname,
-- @Status tinyint
-- SELECT
-- @job_name = 'Backup TLOG - All DBs (NEW)'
-- -- end of test logic
DECLARE
@job_id UNIQUEIDENTIFIER,
@is_sysadmin INT,
@job_owner sysname
CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
SELECT @job_id = job_id from msdb..sysjobs
WHERE name = @job_name
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()
IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
ELSE
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
SELECT @status = running from #xp_results
/*
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
*/
SELECT @job_name AS 'Job Name', @job_id as 'Job ID', @status as 'Running'
DROP TABLE #xp_results
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
usage -
DECLARE
@job_name sysname,
@status tinyint
EXEC afDBA..af_Job_Status 'Log Shipping - Ship Logs', @status OUTPUT
IF @status = 0
EXEC sp_start_job @job_name = 'Log Shipping - Ship Logs'
January 30, 2005 at 5:20 pm
Try using an output parameter with datatype "cursor".
Then you can process the cursor and put the rows in a table once the procedure returns.
Gabriela
January 30, 2005 at 6:05 pm
You may check this topic
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_3w6r.asp
January 31, 2005 at 9:31 am
Gabriela,
Interesting idea, but I don't see how it would be used when calling an existing stored procedure like sp_helpjob. Could you give an example of what you mean?
Vince
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy