Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

TSQL Tuesday – But I was Late

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)
END

When you call this Function as follows:

SSMS Call   
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'
 
End

Execute Proc:

Exec Proc   
Exec sometest

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:
 
GO

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

Tada   
SELECT @DateString = '[' + CONVERT(Char(10),GETDATE(),101) + ' ' +
CONVERT(Char(8) ,GETDATE(),108) + '] '

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:

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)
END

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.

Comments

Posted by Steve Jones on 14 January 2010

That's a good catch. I bet that would have driven me crazy for awhile as well.

Posted by Jason Brimhall on 14 January 2010

I just noticed that my revised function script shows the brackets rather than parenthesis.

Hmmm...will fix in the original post.

http://jasonbrimhall.info

Thanks Steve.

Leave a Comment

Please register or log in to leave a comment.