September 24, 2008 at 5:49 pm
HI Folks,
I am migrating databases from 2000 to 2005 and I have spent a bit of time working out what my server collation should be. It seems I may have gotten it wrong though as SQL BPA is reporting a warning saying
collation of master and model does not match collation of user database.
My server collation is SQL_Latin1_General_CP1_CI_AS - I chose a SQL collation as the databases came from sql 2000
my database collation is Latin1_General_CI_AS
Am I in trouble? How can I check for sure.
September 26, 2008 at 7:27 am
I had the same reported issue by the bpa with the server I inherited at my new job. They also upgraded from 2000 to 2005. I have not migrated or changed anything to date as I have not had any reported issues, and the effort of moving is too great at this point. I would check your code, talk to developers, or keep an eye out for issues. IF you have a low use system then I would fix it to avoid any issues.
Per bpa...
The BPA has detected that one or more user-defined databases is defined by using a collation that is different from the master and model databases. We recommend that you not use this configuration because collation conflicts can occur that might prevent code from executing. For example, when a stored procedure joins one table to a temporary table, SQL Server might end the batch and return a collation conflict error if the collations of the user-defined database and the model database are different. This occurs because temporary tables are created in tempdb, which obtains its collation based on that of model.
If you experience collation conflict errors, consider one of the following solutions:
Export the data from the user database and import it into new tables that have the same collation as the master and model databases.
Rebuild the system databases to use a collation that matches the user database collation. For more information about rebuilding the system databases, see the procedure "To rebuild system databases and specify a new system collation" in How to: Install SQL Server 2005 from the Command Prompt.
Modify any stored procedures that join user tables to tables in tempdb to create the tables in tempdb by using the collation of the user database. To do this, add the COLLATE database_default clause to the column definitions of the temporary table
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply