Datetime2(0) columns get deployed as Datetime2(7)

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Datetime2(0) columns get deployed as Datetime2(7)

Postby nharris » Mon Mar 12, 2012 7:08 pm

SQL Compare version 10.0.0.179 - when deploying changes from source control to a database, columns with data type datetime2(0) get created simply as datetime2. This uses the default precision, making the column datetime2(7).

Re-running a compare shows up the discrepancies and deploying again fixes them.
nharris
 
Posts: 4
Joined: Wed Oct 05, 2011 12:07 pm

Postby Brian Donahue » Thu Mar 15, 2012 4:09 pm

This can happen when you are comparing a source control folder to SQL 2008 and the version of the scripts folder is set to SQL 2005. Because SQL2005 did not have any precision or scale for the datetime2 datatype, they are scripted in the way they would be for SQL 2005.

Changing the databaseVersion in the RedGateDatabaseInfo.xml file in the scripts folder to 10 should fix the issue.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby nharris » Thu Mar 15, 2012 7:26 pm

OK, guess it was because I was taking a shortcut by deploying from TFS to a new, empty database on the server without it being linked. Presumably it assumes an earlier version of SQL Server than 2008?

I just tried linking the DB and creating a test table containing a datetime2(0) column and SQL Compare created a deployment script with the correct column data type definition.

Thanks,
Nigel.
nharris
 
Posts: 4
Joined: Wed Oct 05, 2011 12:07 pm

Postby Brian Donahue » Fri Mar 16, 2012 10:16 am

Hi Nigel,

It's all about the RedGateDatabaseInfo.xml file - it's created by SQL Compare and SQL Source Control so that the comparison can generate the scripts in a format acceptable to the version of SQL. That would mean the database was originally SQL 2005 when you checked it into source control, or maybe the file didn't exist. I suspect if the file is missing, it may default to DatabaseVersion=9.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests