SP runs but fails when started from a job

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

SQL Server 2005 Forums

Transact-SQL (2005)

SP runs but fails when started from a job

Author

Topic

obiron
Starting Member

23 Posts

Posted - 2009-07-31 : 08:19:03

Hi guys,

I have a stored proc which runs just fine when called exec dbo.sp_.....

when I try to run the same sp from a job then I get an error message

'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value'

the sp builds a dynamic SQL statement (@SQL) and then executes it using EXECUTE sp_executesql @SQL

I think the offending code may be the logic I am using to strip the time from the date (@datetime is the getdate() when the process was started)


convert(varchar(10),CAST(FLOOR(CAST(@datetime AS float)) AS datetime),103)

but why would it run native, but not as a job?

The script for the job is below.

USE [msdb]
GO
/****** Object:  Job [Run_DQ_Reports]    Script Date: 07/31/2009 13:09:43 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 07/31/2009 13:09:44 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Run_DQ_Reports', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Schedule to run the DQ Reporting Process automatically', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'PCT\My.Username', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [run DQ Reports Stored Procedure]    Script Date: 07/31/2009 13:09:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run DQ Reports Stored Procedure', 
		@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 guest.spDQ_Reports
', 
		@database_name=N'blueFISH', 
		@database_user_name=N'PCT\My.Username', 
		@output_file_name=N'DQ_LOG', 
		@flags=2
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'DQ Reports', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20090729, 
		@active_end_date=99991231, 
		@active_start_time=80053, 
		@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:

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-31 : 08:52:49

insetead of

convert(varchar(10),CAST(FLOOR(CAST(@datetime AS float)) AS datetime),103)

use

dateadd(day,datediff(day,0,@datetime),0)

Madhivanan

Failing to plan is Planning to fail

obiron
Starting Member

23 Posts

Posted - 2009-07-31 : 11:44:05

I need today's date without the time element. Will that not still have the time element.

Sorry, don't have time to test it now...

Obiron

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources