Function output doesn't show up with PRINT when called from within a Job

Question

Function output doesn't show up with PRINT when called from within a Job

lee.eidson

SSC Rookie

Points: 28
More actions
January 8, 2010 at 1:52 pm

#94238

Hi
We have a simple scalar UDF (see CurTime() snippet below) that returns a formatted string with the current date and time. It's intended for logging a timestamp in long running procs, and usually is called like:
PRINT dbo.CurTime() + 'some status text'
This works fine when called interactively from a query window, or when used in a stored proc that is called interactively and produces the expected output of:
[01/08/2010 15:48:17] some status text
However, when used in a stored proc that is called by a Job with the output logged to a file the timestamp is missing and the output becomes just:
some status text [SQLSTATE 01000]
Any suggestions on what may be causing this? Thanks in advance for any help!
CREATE FUNCTION [dbo].[CurTime]()
RETURNS Char(22)
AS
BEGIN
DECLARE @DateString As Char(22)
SELECT @DateString = '[' + CONVERT(Char(10),GETDATE(),101) + ' ' +
CONVERT(Char(8) ,GETDATE(),108) + '] '
RETURN (@DateString)
END

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

What database is the function in? Does the SQL Job have access to it? Is it called from the job itself or from the proc?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

Dreaded double-post

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

The cause is due to the '[' and ']' . The brackets will either need to be escaped or you will need to change them. I was able to get it to successfully work in a sql agent job by changing the brackets to parenthesis ( and ).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

lee.eidson SSC Rookie Points: 28 More actions · Answer 4

Thanks Jason - you're right, without the brackets it works fine...

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

You're welcome. It stumped me for a little while - whilst recreating and trying to fix the issue.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events