October 23, 2008 at 2:21 am
Hi guys, I got a strange behaviour executing a stored procedure. The SP works fine when executed from Management Studio, but fail with "Error converting data type varchar to datetime. [SQLSTATE 42000] (Error 8114)." when executed inside a scheduled job by SQL Agent.
The script to reproduce the problem is
/*
declare @sql as varchar(255)
declare @table_var table (data datetime)
set @sql='select ''' + convert(varchar(10), getdate(), 103) + ''''
insert into @table_var execute (@sql)
*/
dont mess with different ways of rewrite the code above it's just an example to isolate the buggy statement.
I guess the problem is caused by a different way to handle datatype conversion by the two account (account running management studio and the account running the job).
Someone knows how to fix?
Regards,
umbiomar
October 23, 2008 at 4:36 am
Without knowing anything about your installation, I would guess it's the 103 format in your convert statement. That will return dd/mm/yyyy which when converted back to datetime fails. Had the format been mm/dd/yyyy then all would be well so...
select convert(datetime,'10/23/2008') should work
select convert(datetime,'23/10/2008') should fail
It's all to do with default date formats. Use for 112 in your convert statement instead of 103. That returns the ISO standard YYYYMMDD.
Thing is, it should fail in your stored procedure as well.
Mike
October 24, 2008 at 2:53 am
This is the reason of my question, why job fails and stored procedure doesn't ?
Beside i manage another server mirror of the first (same installation cd, server version, service packs, settings, language, database, hardware...) where the STORED works as stored and job !
Someone knows what i'm missing?
regards,
umbiomar
mdowns (10/23/2008)
Thing is, it should fail in your stored procedure as well.
Mike
October 24, 2008 at 3:30 am
Hmmm... starting to get suspcious now. Can I ask a very silly question? In the stored procedure which fails / works does the date it is converting sometimes have a month AND day value of 12 or less?
If your installations use default language 0 (us_english, date format mdy) then converting a date to a string using format 103 and back to a datetime will always fail if the day is 13 or higher because the conversion back will use that value as the month.
Mike
October 24, 2008 at 3:41 am
In my experience when dealing with dates it is best to be explicit about the format. Although format 112 (ISO) is nice, I Have found that format 120 (ODBC Cononical) is unambiguous.
it has the structure yyyy-mm-dd hh-mm-ss-xxx
No format has yyyy-dd-mm so there is no mix up with day and month.
October 24, 2008 at 4:32 am
Yes it is, actually we got proof of the problem on 13oct when the stored (inside a job) failed for the first time.
I solved changing the 103 in 112, the reason was to truncate the time part of datetime. Thanks all.
The reason why job fail and SP run fine is still an open point, i belive Management Studio takes care of data conversion
behind the scenes.
mdowns (10/24/2008)
Hmmm... starting to get suspcious now. Can I ask a very silly question? In the stored procedure which fails / works does the date it is converting sometimes have a month AND day value of 12 or less?If your installations use default language 0 (us_english, date format mdy) then converting a date to a string using format 103 and back to a datetime will always fail if the day is 13 or higher because the conversion back will use that value as the month.
Mike
October 24, 2008 at 8:18 am
I have seen something similar before where there is a difference in operating system localisation between the user account under which SQL Management Studio is run and the service account under which the SQL Agent Service runs and/or the account under which SQL Server was installed.
Might this apply here?
October 30, 2008 at 10:22 am
That is what i think too, anyone knows how to identify the two accounts?
Regards,
umbiomar
eharper (10/24/2008)
I have seen something similar before where there is a difference in operating system localisation between the user account under which SQL Management Studio is run and the service account under which the SQL Agent Service runs and/or the account under which SQL Server was installed.Might this apply here?
October 30, 2008 at 10:28 am
Well keep in mind that the code in one instance is running under your credentials, and in the other, under the SQL Agent's service account. That's two accounts right there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 30, 2008 at 1:09 pm
Now for a dumb question, why are you using dynamic sql in this to strip the time.
/*
declare @sql as varchar(255)
declare @table_var table (data datetime)
set @sql='select ''' + convert(varchar(10), getdate(), 103) + ''''
insert into @table_var execute (@sql)
*/
When it would be just as simple to do this.
declare @table_var table (data datetime)
insert into @table_var select dateadd(dd,datediff(dd,0,getdate()),0)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply