[sys].[date] being added by the Compare Tool when reading a

rjbook

I am running the latest version of SQL Compare but when I compare a SQL database table against the actual script used to create it they are not matching because the Compare tool is adding the [sys]. schema name in front of the date datatype in SQL 2008. Basically it looks like this as a nonmatch

[EFFECTIVE_DATE] [sys].[date] null, >>> [EFFECTIVE_DATE] [date] null,


Again the script itself does not have the [sys] schema so why would the Compare tool add it when reading the script? Is there a workaround for it? I have tried ignoring different options but haven't found the right combination.

Linda Hawksworth

If you are using a scripts folder, in the root of the scripts folder, you should have an xml file. It'll be called something like RedGateDatabaseInfo.xml

In that, it should have the version of SQL, and I think it will have defaulted to 2005 (9.xxx), rather than 2008. So when SQL Compare reads the file, it converts the Date datatypes to [sys].[date] as Date isn't a valid datatype on 2005.

The easiest way to sort it would be to create a new script folder from your 2008 database to a new location (look on File > Create Scripts Folder). That should create a correct xml which you can copy to the folder you want to use.

Alternatively, modify the comparison settings for scripts folders to SQL Server 2008.