April 28, 2009 at 9:02 am
I just checked the job history for a job I set up yesterday to run a stored procedure and see no output in the job history or the step log I chose. I have
The history just shows the job ran successfully:
Date4/28/2009 10:44:33 AM
LogJob History (ArchiveXMediaSuite)
Step ID1
ServerMGRICSQLDEV1
Job NameArchiveXMediaSuite
Step NameRunProcedure
Duration00:00:02
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: NT AUTHORITY\SYSTEM. The step succeeded.
The log file, set up for that job step is being updated, but no output from the procedure is there. What I see in the log file:
Job 'ArchiveXMediaSuite' : Step 1, 'RunProcedure' : Began Executing 2009-04-27 19:00:00
Job 'ArchiveXMediaSuite' : Step 1, 'RunProcedure' : Began Executing 2009-04-28 07:00:00
Job 'ArchiveXMediaSuite' : Step 1, 'RunProcedure' : Began Executing 2009-04-28 10:44:33
WhenI run the procedure manually, using: Exec spArchiveXMediaSuite, the exact same command that is in the job step, I get:
(0 row(s) affected)
(0 row(s) affected)
(2 row(s) affected)
(4 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(5 row(s) affected)
This is what I expect to be put into the step log and the job history, in case it is needed for troubleshooting.
Anyone know why the info is not being put into my logs or job history?
SQL Version 9.0.4035
First part of procedure source:
/****** Object: StoredProcedure [dbo].[spArchiveXMediaSuite] Script Date: 04/27/2009 14:45:09 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spArchiveXMediaSuite]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spArchiveXMediaSuite]
GO
/****** Object: StoredProcedure [dbo].[spArchiveXMediaSuite] Script Date: 04/27/2009 14:45:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure spArchiveXMediaSuite
As
/*
Job script:
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ArchiveXMediaSuite',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job is to copy records from the XMediaSuite DB for thepurpose of retaqinign the records, as they must be removed from the originating DB to maintain good performance.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBAGroup', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
/****** Object: Step [RunProcedure] Script Date: 04/28/2009 10:59:46 ******/
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = N'41fb90d3-331f-4217-913e-1b0a52646894' and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RunProcedure',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'Exec spArchiveXMediaSuite',
@database_name=N'MGFX_Archive',
@output_file_name=N'D:\SQLData\Logs\XMediaSuiteArchive.log',
@flags=6
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'TwiceDaily7And7',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=12,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090427,
@active_end_date=99991231,
@active_start_time=70000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
May 7, 2009 at 3:39 pm
If I right click a job under the jobs folder and view history, I get nothing. If I right click the Jobs folder itself and select view history I can see the history for all my jobs. I also found that when I view the job history from the Job Activity monitor it works also.
Maybe that will work for you too.
May 8, 2009 at 5:50 am
I see the job history just fine, it's the step results that ar emissing. In my job definition I have selected an output file, and have selected to include step output in job history, but I get none of that data. If I run the stored procedure in a query window I get all kinds of output, it's that when the same procedure runs in the job there is no output recorded.
I'm on the last day of this contract, so I won't have an opportunity to troubleshoot this problem any more after today.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply