Sql-server – SQL Server collation mismatch reducing the amount of data visible to an application

collationsql server

One of our servers died recently, it was rebuilt with a wrong collation and so it looks as follows:

SQL Server – SQL_Latin1_General_CP1_CI_AS
master db – SQL_Latin1_General_CP1_CI_AS
model db – SQL_Latin1_General_CP1_CI_AI
msdb db – SQL_Latin1_General_CP1_CI_AI
temp db – SQL_Latin1_General_CP1_CI_AS
every user db – SQL_Latin1_General_CP1_CI_AI

The databases are accessible, my question is: will the data visible to users via an app be limited because of the mismatching collation? Some users have said that they think they have lost a lot of data even though the database was restored from a very recent backup.

It is SQL Server 2008 R2 with SP3 on Windows Server 2012 R2 (compatible only due to service packs)

Thank you

Best Answer

will the data visible to users via an app be limited because of the mismatching collation?

Assuming you are speaking of scenarios that do not error (because an error does not cause people to wonder if something is missing), but instead just return data that might not be ALL of the expected data, then it depends on how the queries are being done. If ALL of the following conditions are true, then yes, some data can be filtered out now when it wasn't before:

a query uses a temporary table (local or global)
the Collation for string columns in the CREATE TABLE #TempTable statement did not explicitly specify the Collation via the COLLATE keyword
the query is filtering at least one such string column in the temporary table on a string literal or local variable
there is an accent mismatch between the data in that column and the string used for filtering (in the literal or variable)

Meaning, previously someone could have executed:

SELECT * FROM #TempTable WHERE Name = 'a';

and gotten back rows containing the following: A a À Á Â Ã Ä Å à á â ã ä å. That same query, with the new tempdb Collation, will now return only rows containing: A a.

Also, outside of the unexpected filtering that is happening in your user-facing code, there are other potential problems waiting for you. If master is a different Collation, then names of system-level entities (e.g. Databases, Logins, etc) will behave outside of expectations and differently than the rest of the system and could cause odd behavior similar to what your users are reporting. Meaning, if you have accents in Database and/or Login and/or variable/parameter/cursor names and have code that works because it is expecting accent-insensitive comparisons, then you / your application might experience what your users are experiencing (though it could take a while to notice, and even longer to debug). AND, it is assumed that all four system DBs are the same Collation, and some stored procedures and views in msdb JOIN to master on string columns and do break in this scenario. Hence this does really need to be fixed.

SO, since it is just the system DBs that are "incorrect" (it is best to have master and msdb match as there are some stored procedures that JOIN between them and error when their Collations don't match), you should change only those back to the expected Collation of SQL_Latin1_General_CP1_CI_AI. You can do this easily enough by using SETUP.EXE /ACTION=Rebuilddatabase. It allows you to change just the system DBs and does not touch user Databases.

Also see the MSDN documentation for: Rebuild System Databases and Set or Change the Server Collation.

At the very least the command-line should look like (but all as one line, or you need to use ^ at the end of each line in order to continue the command on the following line):

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME={InstanceName}
      /SQLSYSADMINACCOUNTS={accountsToAddToSysadminRole} 
      /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI [/otherOptions]

For this situation you do not need to use the other recommendation of the undocumented -q switch for sqlservr.exe. That option rebuilds ALL databases, and there is no need to make modifications to databases that are already correct. The less you touch (especially with an undocumented feature) the better off you are.

Related Solutions

Sql-server – How to change SQL Server collation

Yes.

You can change the default collation of SQL Server 2008 R2 express instance and individual databases, but it is a complex task.

Sadly, there is no visual option to do it via SSMS.

SQL Server 2008 supports setting collations at the following levels:

Server

Database

Column

Expression

The default installation settings are determined by the Windows system locale. The server-level collation can either be changed during setup, or by changing the Windows system locale before installation. more...

Setting and Changing the Server Collation - SQL Server 2008

Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.
Drop all the user databases.
Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command
Create all the databases and all the objects in them.
Import all your data.

Setting and Changing the Database Collation - SQL Server 2008

Set the COLLATION option in the CREATE DATABASE statement while creating a new database.
Similarly, set the COLLATION options in the ALTER DATABASE statement to change the collation of an existing database.

ALTER DATABASE [database_name] COLLATE SQL_Latin1_General_CP1_CI_AS;

Setting and Changing the Column Collation

Some of the column collations will remain the same even after you alter the database collation. In that case, you have to modify the collation of the individual columns.

Sql-server – SQL Server 2008 collation arabic

From a quick test this end none of the built in collations can represent all characters in that string in a single byte code page so you would need to use a unicode datatype.

SET NOCOUNT ON;

DECLARE @StringToTest nvarchar(2047) = N'بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ'

--The below without the problematic character returns 7 results
--DECLARE @StringToTest nvarchar(2047) = N'بِسْمِ اللَّهِ الرَّحْمَنِ الرَّحِيمِ'


CREATE TABLE #Results
(
name sysname
)

CREATE TABLE #Chars(
C NCHAR(1)
)
INSERT INTO #Chars
SELECT DISTINCT SUBSTRING(@StringToTest,number,1)
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND LEN(@StringToTest)


CREATE TABLE #Collations
(
     code TINYINT PRIMARY KEY
);

WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),   --2
        E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
        E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
        E08(N) AS (SELECT 1 FROM E04 a, E04 b) --256
INSERT INTO #Collations
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM E08    

DECLARE @AlterScript NVARCHAR(MAX) = ''

SELECT @AlterScript = @AlterScript + ' 
RAISERROR(''Processing' + name + ''',0,1) WITH NOWAIT;
ALTER TABLE #Collations ADD ' + name + ' CHAR(1) COLLATE ' + name + ';
EXEC(''UPDATE #Collations SET ' + name + '=CAST(code AS BINARY(1))'');
EXEC(''UPDATE #Collations SET ' + name + '=NULL WHERE ASCII(' + name + ') <> code'');
EXEC(''
IF NOT EXISTS(
SELECT *
FROM #Chars
WHERE NOT EXISTS(SELECT * FROM #Collations WHERE ' + name + ' = C COLLATE ' + name + '))
INSERT INTO #Results
SELECT ''''' + name + '''''
'');
'
FROM   sys.fn_helpcollations()
WHERE  name LIKE '%CS_AS'
       AND name NOT IN    /*Unicode Only Collations*/
                        ( 'Assamese_100_CS_AS', 'Bengali_100_CS_AS',
                         'Divehi_90_CS_AS', 'Divehi_100_CS_AS' ,
                         'Indic_General_90_CS_AS', 'Indic_General_100_CS_AS',
                             'Khmer_100_CS_AS', 'Lao_100_CS_AS',
                         'Maltese_100_CS_AS', 'Maori_100_CS_AS',
                         'Nepali_100_CS_AS', 'Pashto_100_CS_AS',
                         'Syriac_90_CS_AS', 'Syriac_100_CS_AS',
                         'Tibetan_100_CS_AS' )


EXEC (@AlterScript)


SELECT * FROM #Results

DROP TABLE #Collations
DROP TABLE #Results
DROP TABLE #Chars

Best Answer

Related Solutions

Sql-server – How to change SQL Server collation

Sql-server – SQL Server 2008 collation arabic

Related Question