TSQL Tuesday – But I was Late

Categories: News, Professional
Comments: 1 Comment
Published on: January 13, 2010

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.

1 Comment - Leave a comment
  1. [...] out the event is Jason Brimhall, who shares an interesting tale of woe: his UDF worked fine in SSMS and not so fine in SQL Agent. An all-too-familiar situation. Read his post to find out how he solved [...]

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






Calendar
January 2010
M T W T F S S
« Dec   Feb »
 123
45678910
11121314151617
18192021222324
25262728293031
Content
SQLHelp

SQLHelp


Welcome , today is Thursday, April 17, 2014