Create table from SP result set

Question

Post reply

Create table from SP result set

vince.iacoboni@db.com

Hall of Fame

Points: 3965
More actions
January 19, 2005 at 12:09 pm

#65335

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

not that I am aware of, it would be great if someone had a solution however

Shamless self promotion - read my blog http://sirsql.net

hoo-t SSChampion Points: 12713 More actions · Answer 2

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

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 3

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...

hoo-t SSChampion Points: 12713 More actions · Answer 4

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'

Gabriela Nanau SSCommitted Points: 1967 More actions · Answer 5

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

Gabriela Nanau SSCommitted Points: 1967 More actions · Answer 6

You may check this topic

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_3w6r.asp

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 7

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