I was late to the game having discovered the Blog Post the day after entries were allowed. Despite that, I will trackback to the Adam Machanics Blog Post. I read the rules and fully understand that it will only count for me having done the exercise and my own personal hoorah. That said, I had a stumper that came up recently that the TSQL Tuesday challenge made me think of.
The challenging script ended up being very easy to fix, but it took me a bit to find the issue. The setup comes from a UDF written to print out timestamps. When called directly from SSMS – it works as expected. When called from a stored proc it works as expected. When called from a SQL Agent Job it does not work as expected.
CREATE FUNCTION [dbo].[CurTime]()
RETURNS Char(22)
AS
BEGIN
DECLARE @DateString AS Char(23)
SELECT @DateString = '[' + CONVERT(Char(10),GETDATE(),101) + ' ' +
CONVERT(Char(8) ,GETDATE(),108) + '] '
RETURN (@DateString)
ENDWhen you call this Function as follows:
PRINT dbo.CurTime() + 'some status text'You should receive the a printed statement formatted as “[current time] some status text”. If you create a stored procedure and then call it from the proc you will get the same results.
Create procedure sometest
as
Begin
declare @curtime varchar(32)
select @curtime = dbo.curtime()
PRINT @curtime + 'some status text'
Select * from Quote
PRINT dbo.CurTime() + 'some status text'
EndExecute Proc:
And now to setup a job and continue testing. As said earlier, this is where the problem is seen.
USE [msdb]
GO
/****** Object: Job [sometest] Script Date: 01/13/2010 18:39:32 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/13/2010 18:39:32 ******/
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'sometest',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [tst] Script Date: 01/13/2010 18:39:33 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'tst',
@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 dbo.sometest',
@database_name=N'TestA',
@output_file_name=N'C:\sometest.txt',
@flags=12
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_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:
GONow, the database listed in this job probably does not exist for you. Replace that database with a valid database name. Run the job and the job will complete successfully. The results of executing the above job show the following output in the job history (step history):
” some status text”
This is missing the date and time that the function should provide. I verified correct database names, function was firing, etc., etc., etc. It had to be something in the function. And then finally it dawned on me while staring at the code.
The Job was escaping out of the string due to the ‘[.' By changing the '[' to a '(' and ']‘ to ‘)’ the job ran and the expected output was returned.
Revised function is as follows:
I would have expected the same results between SQL Agent and SSMS. However, the Agent was more strict in the execution of the SQL statements. This little adventure was posted in the forums where a User was asking for assistance. It stumped for a bit, so decided I would create a POST about it.
