April 30, 2008 at 1:58 pm
Folks,
I have a weird situation. I have a query which I am running on both Prod and test environments. The server specs on both are the same except that the prod is clustered.
Here is the query
select tr.policy_expiration_date_key,ex.cal_date
, earned_end_date_last = dateadd(d, -1, ex.cal_date)
from
tr
inner join
..datedim ex
on ex.date_key = tr.policy_expiration_date_key
where transaction_audit_key = 178
order by tr.policy_expiration_date_key,ex.cal_date
This query runs perfect returning the required data in the test server. When I run in prod it gives me this error.
Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime' column caused overflow.
Now I tried to go on different dates which might be causing this to fail and went over the range of 17530101 to 99991231. It failed on the date 17530101. The strange thing is why it is not failing in test.
Also when I modify my sql not to include the date 17530101 it works well in prod again.
I am totally out of answers as to why it is failing only in prod....and why is it failing in the first place..as the dates which it is trying to work on are in the range of 2000-2008..
Please let me know your thoughts on this.
April 30, 2008 at 2:04 pm
I think you will find that the following statement will fail on any SQL Server:
select dateadd(dd,-1,'17530101')
You will need to look at your data to find the rows it fails on.
April 30, 2008 at 3:00 pm
Thank You..I tried diff data sets but it did not fail..
I did a dbcc reindex on the table...and then it worked..pages were torn somewhere i guess..Incidentally we had a network issue yesterday night. this sql is part of a whole big job...which failed at the same time.
April 30, 2008 at 3:06 pm
lavani.sari (4/30/2008)
Thank You..I tried diff data sets but it did not fail..I did a dbcc reindex on the table...and then it worked..pages were torn somewhere i guess..Incidentally we had a network issue yesterday night. this sql is part of a whole big job...which failed at the same time.
None of those things would cause the problem you described.
April 30, 2008 at 4:06 pm
one of the [font="system"]ex.cal_date[/font] column values in the database that fails has probably been set to zero or possibly NULL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 2, 2008 at 8:33 am
Folks,
This is still not solved. When we tried to wrap this in a cursor to find a potentially bad row but it din't return anythign..it worked well...
Any ideas here...
May 2, 2008 at 9:47 am
Try the following query:
[font="Courier New"]select tr.policy_expiration_date_key,ex.cal_date
from
tr
inner join
..datedim ex
on ex.date_key = tr.policy_expiration_date_key
where transaction_audit_key = 178
AND (EX.Cal_Date < Cast('1900-01-01' as datetime) OR EX.Cal_Date > Cast('2079-01-01' as datetime))
order by tr.policy_expiration_date_key,ex.cal_date
[/font]
What are the results?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply