problems with datetime - behaviour different to sql 2000

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  June 15, 2009 at 9:26 am

  #206264

  Hi guys

  we are currently testing a migration from sql 2000 to 2008 and have come across a behaviour change that i can't understand.

  we use the workflow engine to call off reporting services reports. The state of this request is stored in the workflowpersistence database (as supplied by ms)

  by default the workflow code sends datetimes in a higher precision than sql datetimes can handle. - in sql 2000 this was not an issue, but in 2008 it fails misserably

  for a single call to the db to get the status you would see the following

  RPC started

  exec [dbo].[GetActivityEventsWithDetails] @WorkflowInstanceInternalId=2916,@BeginDateTime='Jan 1 1753 12:00:00:000AM',@EndDateTime='Jun 15 2009 3:03:44:683PM'

  RPC completed

  exec [dbo].[GetActivityEventsWithDetails] @WorkflowInstanceInternalId=2916,@BeginDateTime='1753-01-01 00:00:00',@EndDateTime='2009-06-15 15:03:44.6830000'

  notice the underlined differences -

  if you run the same on sql 2008 we see that RPC started and RPC completed are exactly the same as each other

  sql 2000 is somehow intercepting the invalid number of millisecond digits and smoothing it out - but sql 2008 is not.....

  any thoughts (other than the obvious which is to alter our workflow code and fix the invalid dates)

  i'd like to know if this is something we can patch , or if there is a setting we can change

  Many thanks

  Mike Vessey

  MVDBA

• Johan Bijnens

  SSC Guru

  Points: 135376

  More actions

  June 15, 2009 at 10:34 am

  #1009764

  - Is the issue causing problems ? (What error do you get)

  - Is the client configured exactly the same as the current production system ?

  - did you capture and post bot RPC by just changing the connection string or do they originate from different servers ?

  - on calling the sproc, how did you declare the sproc variables in your calling application ? (did you specify the db_datatype correctly ?)

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me