DTS Return Value from Stored Procedure - Help!
-
Hello,
I have a stored procedure that creates a job and executes it. This job fires a DTS to be executed through DTSRUN.exe and it is all working fine except my return value is always "0" when I call
EXEC @ReturnCode = msdb.dbo.sp_start_job
I added the following step to the job to see what was happening when I caused the job to fail by passing a non-existing DTS package name:
@output_file_name
= 'C:\DTSRUN.LOG'
And the contents of the DTSRUN.LOG file are:
DTSRun: Loading...
Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
Error string: The specified DTS Package ('Name = 'DTSTestXYZ'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
So, the process if failing as it should on my test to break the process and yet the return value in my stored procedure remains "0", which is translated to "success".
Am I overlooking something here? I've been searching and reading BOL, Professional SQL Server 2000 Programming, online searches, etc. but I haven't found an answer.
Is there a way to pass a return value to the store procedure that indicates that the DTS failed as logged by the contents of the log file above?
Any help is appreciated.
Kindest Regards, -
You problem is sp_start_job returns 1 only if the job could not be started, not if it failed.
Take a look at sp_help_job as it might help you in this case if no other istances can be kicked off during the same time frame.
-
Antares686,
Thanks for your suggestion I will look into it and see if takes care of my problem. I'll then post it here for the benefit of others.
Kindest Regards, -
Please post a reply if you find out - I have came on here looking for exactly the same answer to this problem!
-
I have found a way to do this. Ensure that your Log Package Execution to SQL Server found under Package > Properties and Logging.
Then use the following to run detailed information on the run of your DTS package
USE msdb
GO
SELECT * FROM dbo.sysdtspackagelog
WHERE name = 'packagename'
GO
-
That is the basis for sp_help_job which is the MS supported method.
-
Antares686,
I'm still looking into this but I'm having a real brain-freeze and can't seem to find info on this.
Is there a way to select the returned columns from the sp_help_job into a #temp table? So basically the resultset from a stored proc.
I've attempted several ways but I'm not getting it to work, I've usually done it from straight select statements and not stored procedures.
I'm actually wanting to grab the value of the last_run_outcome column in the sp_help_jobstep
I have setup my job step 1 to run step 2 on failure. In step 2 I raise an error but the rest of the process doesn't seem to detect it so I want to attempt doing this from the last_run_outcome column.
I'm including the stored proc code that is firing my process below but it's now working as expected yet
AND the contents of the log files that are getting created during the execution.Thanks!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
set NOCOUNT ON
GO
ALTER procedure [dbo].[proc_RunDTS]
@DTSPackageName varchar(128) = NULL,
@GVName1 varchar(128) = NULL,
@GVVal1 varchar(128) = NULL,
@GVName2 varchar(128) = NULL,
@GVVal2 varchar(128) = NULL,
@GVName3 varchar(128) = NULL,
@GVVal3 varchar(128) = NULL,
@GVName4 varchar(128) = NULL,
@GVVal4 varchar(128) = NULL,
@GVName5 varchar(128) = NULL,
@GVVal5 varchar(128) = NULL
AS
DECLARE @ReturnCode int
SET @ReturnCode = 0
DECLARE @jobid uniqueidentifier
-- Specify DTS to be executed
DECLARE @dts varchar(128)
-- Get the value of the DTS name for the
-- DTS package that will be executed
IF(@DTSPackageName IS NULL)
BEGIN
-- Exit the procedure since no DTS
-- name was passed in the parameters
GOTO QuitWithNoSuccess
END
ELSE
BEGIN
SET @dts = @DTSPackageName
END
-- Initialize command
DECLARE @cmd varchar(3200)
SET @cmd = 'Dtsrun /S "(local)" /N "' + @dts + '" /W "0" /E '
-- Specify global variables values to be passed to DTS trough DTSRUN
IF (@GVName1 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName1 + ':8="' + @GVVal1 + '" '
END
IF (@GVName2 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName2 + ':8="' + @GVVal2 + '" '
END
IF (@GVName3 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName3 + ':8="' + @GVVal3 + '" '
END
IF (@GVName4 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName4 + ':8="' + @GVVal4 + '" '
END
IF (@GVName5 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName5 + ':8="' + @GVVal5 + '" '
END
-- DEBUG PRINT
PRINT @cmd
-- Create a unique job name
DECLARE @jobname varchar(128)
SET @jobname = CAST(NEWID() AS CHAR(36))
-- Create job
EXEC @ReturnCode = msdb.dbo.sp_add_job
@job_name = @jobname,
@enabled = 1,
@owner_login_name = 'sa',
@delete_level = 0, -- 0=Never, 1=On success, 2=On failure, 3=always
-- Use 1 if you need to review the job history.
-- 3 deletes the job itself and all of its history.
@job_id = @jobid OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @jobid,
@server_name = '(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @jobid,
@step_id = 1,
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd,
@on_fail_action = 4,
@on_fail_step_id = 2,
@output_file_name = 'C:\DTSRUN.LOG' -- ### COMENT OUT AFTER TESTING
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @jobid,
@step_id = 2,
@step_name = 'Failed Execute DTS',
@subsystem = 'TSQL',
@command = 'RAISERROR(''Failed to execute DTS'',16,1)',
@output_file_name = 'C:\DTSRUNFAIL.LOG' -- ### COMENT OUT AFTER TESTING
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
-- Start job
EXEC @ReturnCode = msdb.dbo.sp_start_job
@job_id = @jobid
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
EXEC @ReturnCode = msdb.dbo.sp_help_jobstep
@job_id = @jobid,
@step_id = 1
GOTO EndSave
QuitWithNoSuccess:
RETURN (1)
EndSave:
RETURN @ReturnCode
--------------
DTSRUN.LOG:
DTSRun: Loading...
Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
Error string: The specified DTS Package ('Name = 'DTSTestXXX'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
------------------------
DTSRUNFAIL.LOG:
Job '2F09307B-34EC-4D23-914F-2945609B6F7F' : Step 2, 'Failed Execute DTS' : Began Executing 2007-08-23 15:18:25
Msg 50000, Sev 16: Failed to execute DTS [SQLSTATE 42000]
Kindest Regards, -
This should b the right method
EXEC INTO #Temp sp_help_jobstep
-
Antares686,
As always, thanks for your help. I will find time to test this today and I'll report my findings.
Kindest Regards, -
I'm posting what I found and works after testing many different things in case it benefits someone out there.
Antatres686, thanks for all your input - it was very helpful. On the last one, by the way, the way to get the results from a stored procedure into a temp table is to create the #temptable with the fiels you need to populate and do an
INSERT INTO #temptable
EXECUTE dbo.WhateverStoredProcName @WhateverArgument = 'WhateverValue' --(of course, if argument values are expected)
This didn't work for me because the stored proc I was trying to get the value from is a "nested" stored procedure - which means there are other external stored procs. called from within it.
Anyway, here is what worked for me. This stored procedure will take up to 5 global variables / values to build the command you pass to the DTS. All global variables are passed as strings so you may need to convert them to the appropriate types in your receiving DTS Package.
Cheers all!
Jose Lopez
- - - - -
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[proc_RunDTS]
@DTSPackageName varchar(128) = NULL,
@GVName1 varchar(128) = NULL,
@GVVal1 varchar(128) = NULL,
@GVName2 varchar(128) = NULL,
@GVVal2 varchar(128) = NULL,
@GVName3 varchar(128) = NULL,
@GVVal3 varchar(128) = NULL,
@GVName4 varchar(128) = NULL,
@GVVal4 varchar(128) = NULL,
@GVName5 varchar(128) = NULL,
@GVVal5 varchar(128) = NULL
AS
DECLARE @ReturnCode int
SET @ReturnCode = 0
DECLARE @LastRunOutcome int
SET @LastRunOutcome = 1 -- 0=failure, 1=success
DECLARE @jobid uniqueidentifier
-- Specify DTS to be executed
DECLARE @dts varchar(128)
-- Get the value of the DTS name for the
-- DTS package that will be executed
IF(@DTSPackageName IS NULL)
BEGIN
-- Exit the procedure since no DTS
-- name was passed in the parameters
GOTO QuitWithNoSuccess
END
ELSE
BEGIN
SET @dts = @DTSPackageName
END
-- Initialize command
DECLARE @cmd varchar(3200)
SET @cmd = 'Dtsrun /S "(local)" /N "' + @dts + '" /W "0" /E '
-- Specify global variables values to be passed to DTS trough DTSRUN
IF (@GVName1 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName1 + ':8="' + @GVVal1 + '" '
END
IF (@GVName2 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName2 + ':8="' + @GVVal2 + '" '
END
IF (@GVName3 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName3 + ':8="' + @GVVal3 + '" '
END
IF (@GVName4 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName4 + ':8="' + @GVVal4 + '" '
END
IF (@GVName5 is not null)
BEGIN
SET @cmd = @cmd + '/A ' + @GVName5 + ':8="' + @GVVal5 + '" '
END
-- DEBUG PRINT
PRINT @cmd
-- Create a unique job name
DECLARE @jobname varchar(128)
SET @jobname = CAST(NEWID() AS CHAR(36)) -- ALLOW TO RUN MULTIPLE DTS INSTANCES AT THE SAME TIME
-- Create job
EXEC @ReturnCode = msdb.dbo.sp_add_job
@job_name = @jobname,
@enabled= 1,
= 'ASP',
@owner_login_name= 'sa',
@delete_level= 1, -- 0=Never, 1=On success, 2=On failure, 3=always
-- Use 1 if you need to review the job history.
-- 3 deletes the job itself and all of its history.
@job_id = @jobid OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @jobid,
@server_name= '(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @jobid,
@step_id= 1,
@step_name= 'Execute DTS',
@subsystem= 'CMDEXEC',
@command= @cmd,
@on_fail_action= 4,
@on_fail_step_id= 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id= @jobid,
@step_id= 2,
@step_name= 'Failed Execute DTS',
@subsystem= 'TSQL',
@command= 'RAISERROR(''Failed to execute DTS'',16,1)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
-- Start job
EXEC @ReturnCode = msdb.dbo.sp_start_job
@job_id = @jobid
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
-- Delay the SELECT statement a few seconds
-- to give the DTS process time to run and
-- the outcome value to be updated in the
-- system table
WAITFOR DELAY '00:00:05' -- I'm using five seconds here. Use what you need.
-- Get the "last run outcome" for the DTS Package
SELECT @LastRunOutcome = last_run_outcome
FROM msdb.dbo.sysjobservers
WHERE job_id = @jobid
IF (@LastRunOutcome = 0) -- 0 = Failed, 1 = Succeeded, 3 = Cancelled, 5 = Unknown
BEGIN
RAISERROR('Failed to execute DTS',16,1)
END
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithNoSuccess
GOTO EndSave
QuitWithNoSuccess:
RETURN (1)
EndSave:
RETURN @ReturnCode
Kindest Regards,
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply