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.

[codesyntax lang=”sql” lines_start=”1″ capitalize=”upper” title=”Create Function”]

[/codesyntax]

When you call this Function as follows:

[codesyntax lang=”php” title=”SSMS Call”]

[/codesyntax]

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.

[codesyntax lang=”php” title=”Create Proc”]

[/codesyntax]

Execute Proc:

[codesyntax lang=”php” title=”Exec Proc”]

[/codesyntax]

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

[codesyntax lang=”php” title=”SQL Agent Job”]

[/codesyntax]

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.

[codesyntax lang=”php” title=”Tada”]

[/codesyntax]

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:

[codesyntax lang=”php” title=”Revised Function”]

[/codesyntax]

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 class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








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

SQLHelp


Welcome , today is Thursday, December 18, 2014