TSQL Tuesday – But I was Late

The puzzling problem I encountered in the article was a fun little job that kept giving different results than running the same code from SSMS. Check it out!

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.

When you call this Function as follows:

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.

Execute Proc:

And now to setup a job and continue testing.  As said earlier, this is where the problem is seen.

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.

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.

2 thoughts on “TSQL Tuesday – But I was Late”

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.