False warning message about Unsupported datatype

MarkThornton

Whilst trying to move a new view into our production database, we got the following, rather weird warning:

Severity: High
Object: cf_config_item
Title: The type NVarchar(max) on object [dbo].[cf_config_item].[usr_description] is not supported in the target database version.

The database is on a SQL Server 2008 server, and the error messages concern tables that already exist, and have happily contained nvarchar(max) fields for many months, if not years.

Has anyone else seen this kind of weird behaviour?

Brian Donahue

There is one explanation I can think of. When SQL Compare generates a synchronization script, it considers the compatibility level of the database.

If you have a database hosted on SQL Server 2008 and the compatibility level is 2000 or some level that predates the introduction of the VARCHAR(MAX) datatype, then SQL Compare will not allow the introduction of the unsupported datatype.

SQL Server, however, lets you introduce these objects into an incompatible database.

There is already a feature request to allow you to ignore or explicitly set the compatibility level, but this is still being discussed.

For now, it's necessary to use the appropriate compatibility level to the datatypes you want to introduce into the database.

MarkThornton

I have been astonished to find that the database concerned does have a compatibility level of SQL Server 2000! So, thanks to Brian Donahue, we now know how what is causing this problem.