SA0096 : The collation of the current database does not match that of the model database
The topic describes the SA0096 analysis rule.
The collation of the current database does not match that of the model database
The rule verifies that the collation of the current database is the same with the collation of the [model] database.
Having a database collations different from the model database can cause collation conflicts which may prevent code from executing. For example, when a table is joined to a temporary table, SQL Server may experience a collation conflict error if the user-defined database collation and the model database collation are different. This is because temporary tables are created in tempdb, which is created at server startup with the collation of the model database.
You can consider the following:
Create a new database with the same collation as the model database and import the data form the given database in the new database.
Rebuild the system databases to use the same collation as your database.
Review and modify any SQL code that joins user tables to temporary tables. To prevent collation conflicts, you can add and the 'COLLATE database_default' clause to the joined string columns of the temporary table, or by modifying the temporary table definition script and explicitly set the collation to each string column using the COLLATE clause to be the same as your database’s collation.
1CREATE TABLE #EmployeeResumes 2( 3 LName nvarchar(25) COLLATE database_default, 4 FName nvarchar(25), 5 Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) 6); 7 8SELECT * 9FROM HumanResources.EmployeeResumes er 10INNER JOIN #EmployeeResumes tmp ON er.FName = tmp.FName COLLATE Latin1_General_CI_AS
The rule has a ContextOnly scope and is applied only on current server and database schema.
Rule has no parameters.
The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.
© Ubitsoft Ltd. All Rights Reserved.