We’ve run into a problem moving an environment from SQL Server 2005 to 2008. Specifically, after we’ve moved the database and run our upgrade code (which includes a DataObjects “perform” schema upgrade), we’re running into errors like this:

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

I understand that DataObjects uses the datetime2 type available in 2008 by default, and we were a little surprised when the upgrade didn’t migrate the columns from datetime columns to datetime2 columns. It also appears that DataObjects chooses a datatype for querying based on something other than the column’s actual type (i.e. the database version, compatibility settings, or some other metadata), leading to the above error when querying.

Is there a way to prompt the DataObjects schema upgrade to change datetime columns into datetime2 columns? Have we missed a step updating the compatibility level or some other piece of metadata when moving the database to 2008? Should DataObjects continue to work / query correctly if we choose to keep using the old datetime type?

asked Dec 12 '12 at 19:46

Matthew%20Burnell's gravatar image

Matthew Burnell
11335


One Answer:

Hello Matthew,

Unfortunately DataObjects.Net currently can't distinguish datetime and datetime2 types. As a consequence it's unable to automatically upgrade columns to newer type. This is known limitation and we have plans to improve that in future versions.

DataObjects.Net uses server version to determine capabilities of the server. This includes available data types as well as their value ranges. When SQL Server 2008 is used DO starts using datetime2 for query parameters and relies on its wide value range.

The above statements leave two options for you:

  1. Manually upgrade columns in the database.
  2. Set ForcedServerVersion in DomainConfiguration to something like 9.0.0.0 which enables compatibility with SQL Server 2005 and prevents datatime2 type from being used.

answered Dec 13 '12 at 03:22

Denis%20Krjuchkov's gravatar image

Denis Krjuchkov
179325

Thanks for the info, Denis. We'll upgrade the columns manually.

(Dec 13 '12 at 17:39) Matthew Burnell Matthew%20Burnell's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×6
×3
×1
×1

Asked: Dec 12 '12 at 19:46

Seen: 3,035 times

Last updated: Dec 13 '12 at 17:39

powered by OSQA