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

nharris

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.

Brian Donahue

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.

nharris

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.

Brian Donahue

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.