SQL Server and Collation settings

12 08 2004

SQL Server and Collation settings

Posted by: Richard in Databases

Google Search Results

You arrived here after searching for the following phrases:

at
in
collate
database_default
database
level
mssql

Click a phrase to jump to the first occurrence, or return to the search results.

Well, a fine and productive day today spent adjusting Collation settings inside MS SQL Server 2000. I’d been moving some developers databases between servers to enable an upgrade and rebuild on the existing system. The ‘new’ database server had been built on Windows 2003, and SQL Server installed, by somebody else.

Unfortunately, despite initially promising comments from the developers, the developers were then hit with the wonderful message. Server: Msg 446, Level 16, State 1, Procedure SPNAME , Line 153 Cannot resolve collation conflict for equal to operation.

The problem, it seems, was that the new server was Microsoft SQL Server 2000 - 8.00.818 and the old server Microsoft SQL Server 2000 - 8.00.760 - despite my understanding that both systems were SP3a. Not in itself a problem, but what seems to have changed is the Collation default to bring it in line with Windows’ collation/locale settings, and as I was dump/loading the databases between the two the collation setting from the old came across for each of the databases.

The old system was running SQLLatin1GeneralCP1CIAS, the new system Latin1GeneralCIAS - this is enough to throw the above error, and a developer was seeing this when running stored procedures. Everywhere seemed to be at the same collation level, but the subtlety was that master, model and tempdb were at different collations to the user databases, and of course temporary tables reside in tempdb which was at the different collation setting from the user database, and rebuilt every time from model.

So, we had two options. Change the collation settings of our databases to this new default, or change the system databases collation setting. The former just wasn’t practical, as COLLATE statements riddle our SP’s and table definitions. So, we needed to change the system database collations, and this meant rebuilding SQL Server with the rarely used ‘rebuildm.exe’, the steps of which were:

Make sure you’ve a recent backup of everything!!!
Get copies of sysaltfiles (which locates the underlying data file and log files)
Detach all the user databases (spdetachdb)
Shut down SQL Server
Run rebuildm.exe selecting the correct collation (The ‘SQL Collations’ “Dictionary order, case-insensitive, for use with the 437 (U.S. English) Character Set” is the match for SQLLatin1GeneralCP1CI_AS),
Recreate logins
Reattach the databases with spattachdb ‘dbname’,'file1′,’file2′ using the data from sysaltfiles

All in all, it took a good hour of fiddling that just wouldn’t have been necessary if the collation hadn’t been changed. The lesson I take from this is that you should check every subtly before moving databases between servers. It’s also been a very hard problem to track down and resolve. Hopefully this post will help others who find themselves in a similar situation.

Post comments if you’ve anything to add.

This entry was posted on Thursday, August 12th, 2004 at 1:44 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

19 Responses to “SQL Server and Collation settings”

1
Abhilash Nair Says:
September 6th, 2004 at 3:22 pm
Me too facing the same problem. pls let me know if any one of you find solution for the above problem.
2
Richard Leyton Says:
September 6th, 2004 at 3:36 pm
Hi Abhilash,

The entry you posted your comment to has the solution I used, which is to rebuild the system databases with the ‘rebuildm.exe’ utility, and ensure you use the correct collation setting.

If there’s something not clear in what I’ve described, please let me know and I’ll endevour to clarify things,

Regards,

Richard.
3
kimpton Says:
September 13th, 2004 at 6:01 pm
hello , we are having the same fun here….so thanks for writing this down.

‘Dictionary order, case-insensitive, for use with the 437 (U.S. English) Character Set’ seems to match ‘SQLLatin1GeneralCp437CI_AS’ though.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/incollation3oa6.asp
4
Richard Leyton Says:
September 16th, 2004 at 7:10 pm
Thanks for the comments. Glad to see it’s of use. Seems my recollections and notes were wrong as suggested by kimpton - discovered this myself when running through the process on a new server with the various SP’s being applied to it.

Seems that “Dictionary order, case-insensitive, for use with 1252 Character Set” is the right one for SQLLatin1GeneralCP1CI_AS

Oh, the fun.
5
Brendon Webber Says:
October 21st, 2004 at 10:50 am
I’m trying desperately to get this to work since we are having major problems with collation. However I’m getting an error “Cannot Copy Master: It is being used by another person or program…” nothing is using master???

Any suggestions?
6
Kenya Takahashi Says:
October 22nd, 2004 at 2:28 pm
Hello Brendon,

are you talking about an error during rebuilding Master?
I got the same error and in my case it caused by choosing the destination directory of Data files for “Source Directory containing Data Files”.
When I chose the source directory, from which I installed SQL Server (perhaps CD-ROM or share), then it worked fine.
I hope it would help you something.

Hello Rechird,

thank you for your useful article. I would have read it before trying to rebuild Master without detaching user databases, which concluded a rebuild failure and re-installation of SQL Server.

I am very interested in when you would get problems in collation conflict.
I would appreciate if you could tell me an easy and concrete example.

Best regards,
Kenya
7
Ajith Says:
March 30th, 2005 at 4:41 pm
Note from Richard Leyton:
I’ve umm-ed and ahhh-ed about approving the script below, which was authored by Ajith, not me, and was posted as a comment some time ago. It’s a TABLE level operation, but I’m sceptical that it’s either a sensible or appropriate course of action in a properly managed environment, given that collation settings are often an important setting.

That said, it’s possible somebody might want to do this. I’d question that you’re doing the right thing, to be honest. So, please understand that this is posted with ABSOLUTELY NO WARRANTY OR ASSURANCE OF FITNESS FOR PURPOSE. USE AT YOUR OWN RISK. Any issues should be taken up with Ajith.

I can’t say it more clearly than that.
//END NOTE

– =============================================
–Script to remove all COLLATION Settings
–only on VARCHAR DATA TYPES
–SET the right COLLATION FOR the database and –then execute this script to change any collation –settings at the table column level
–WARNING: USE THIS SCRIPT AT YOUR OWN RISK
–NOTE: THIS SCRIPT DOES NOT WORK FOR COLUMNS WITH –CONSTRAINTS. FEEL FREE TO ENHANCE THE SCRIPT TO –HANDLE CONSTRAINTS
–@@
–@@ 03/30/2005
– =============================================
DECLARE COLCURSOR CURSOR READONLY FOR
select tableschema, tablename, columnname,
columndefault, isNullable, Datatype,
charactermaximumlength, collationname
from informationschema.columns
INNER JOIN
(SELECT TABLENAME TN FROM
informationschema.tables where TABLETYPE=’BASE TABLE’ ) IT ON (TABLENAME=TN)
where Datatype = ‘varchar’
DECLARE @tableschema varchar(10), @tablename varchar(100), @columnname varchar(100),
@columndefault varchar(100), @isNullable varchar(5), @Datatype varchar(100),
@charactermaximumlength varchar(10), @collationname varchar(200)

DECLARE @Execstr VARCHAR(2000)
OPEN COLCURSOR
FETCH NEXT FROM COLCURSOR INTO @tableschema, @tablename, @columnname,
@columndefault, @isNullable, @Datatype,
@charactermaximumlength, @collationname
WHILE (@@fetchstatus > -1)
BEGIN
IF (@@fetchstatus > -2)
BEGIN
SET @Execstr = ‘ALTER TABLE ‘ + @tableschema + ‘.’ + @tablename
+ ‘ ALTER COLUMN [' + @columnname + '] ‘ + @Datatype + ‘ (’+ @charactermaximumlength + ‘) ‘
+ CASE WHEN @isNullable=’no’ THEN ‘ NOT NULL’ ELSE ‘ NULL ‘ END
–EXEC (@Execstr)
–UNCOMMENT the above if statements look satisfactory
```
    PRINT ('Executing -->'+ @Execstr )
    PRINT ('Orig COLLATION WAS -->'+ @collation_name )

END
FETCH NEXT FROM COL_CURSOR INTO @table_schema, @table_name, @column_name,
@column_default, @is_Nullable, @Data_type,
@character_maximum_length, @collation_name
```
END
CLOSE COLCURSOR
DEALLOCATE COLCURSOR
GO
8
Dave A-W Says:
February 15th, 2006 at 11:29 am
Wouldn’t it also be possible just to change the collation on the model db using ALTER DATABASE. Then, next time the server reboots, tempdb is recreated using the new collation, which should sort out the incompatibility problems sorted?
9
Richard Says:
February 15th, 2006 at 11:38 am
Hi Dave,

It’s a good point, and I’d be interested to hear if that’s possible. But I have a vague recollection that system database collations couldn’t be changed, hence all the hassle: It certainly would be much simpler!!!

Unfortunately, I’m a very long way away from any sort of MS SQL 2000 installation to be able to verify your suggestion, so I’d be grateful if you - or somebody reading this on the website - could advise!
10
Dave A-W Says:
February 15th, 2006 at 4:35 pm
I’ve got a SQL 2005 installation, and went for the rebuild option rather than risking having different master and model/temp db collations as commented above. There are a couple of differences in the steps:
1. You shouldn’t need 2005’s sysaltfiles equivalent
2. You can use SQL Configuration Manager to shut down the services
3. rebuildm.exe doesn’t exist, instead you have to run the Setup.exe from your original install disk.
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=[new strong-named SA password - must contain numbers] SQLCOLLATION=[new collation name]

See http://msdn2.microsoft.com/en-us/library/ms144259(SQL.90).aspx for more details.
1. Reattaching can be done from within SQL Management Studio - just right click databases, attach, and browse to the files you want to reattach.
11
Kim Says:
July 24th, 2006 at 11:19 pm
Changing the collation of the model databases using the ALTER statement doesn’t work. You get an error message saying you can’t alter the system databases.
12
Rod Says:
October 2nd, 2006 at 10:54 pm
http://www.codeproject.com/vb/net/ChangeCollation.asp
13
Asghar Says:
November 7th, 2006 at 2:45 pm
I am replicating three databases between two sql 2005 servers two of them are replication fine even though they are different collations but one db is giving me this error so I believe I would be reinstalling sql again :(.
14
Rose Says:
February 26th, 2007 at 4:37 pm
OK .. that is when the SQL installation was wrong … I have things reversed. My new SQL environment is SQL_Latin1 … but I have an older database that is Latin1-general … how do I change just one db?
15
Craig LB Says:
April 11th, 2007 at 5:34 pm
Hi Rose,

I’ve used this before to accomplish that :-

ALTER DATABASE MyDatabase COLLATE

Hope it helps

Regards
16
Patrice Says:
April 16th, 2007 at 5:37 pm
Hi,

I add to choose the “1252 Character Set” for me to get the SQLLatin1GeneralCP1CI_AS collation.

Apart of this, your information did work great for me.

Best regards,
17
Seb Says:
October 7th, 2007 at 5:52 am
Yeah, I agreed with Patrice with selecting “1252 Character Set” instead of installing MS SQL Server 2005 Developer Edition with “default General Latin…”.. helps me.

In short, I now understands that the default “General Latin…” in my Notebook will install my server collation as “Latin1GeneralCP1CIAS” not what I needed (preferred “SQLLatin1GeneralCP1CI_AS:”)

Just to ellaborate a bit… Using MS SQL Server 2005 Developer Edition installation CD, What I tried installing with the default “General Latin..” and I ended up with the server collation as Latin1GeneralCP1CIAS.. With this collation it caused all of my ASP.Net projects to have “Collation conflicts” problems whenever I exec the Stored Procedures…

This really fixed it.. Thanks Patrice !
18
info Says:
November 11th, 2007 at 3:41 pm
How to get rid of this message when using application thru SQL ODBC DNS-connection using English and Arabic with SQL 2005 server..

[Microsft] ODBC SQL Server Deriver Cannot resolve the collation conflict between “ArabicBIN” and “SQLLatin1GeneralCP1256CSAS” in the equal to operation. ODBC SQL Server Driver cannot resolve the collation conflict

any suggestion
thanks in advance
19
Grant S Says:
November 29th, 2007 at 9:03 pm
Another approach in SQL Server 2005 is to simply assign the desired collation to whichever column(s) in the temporary tables require it. You can use the COLLATE keyword with “database_default” to explicitly assign the current database’s collation setting to a column in a temporary table. Ex:

create table #MyTemp
(
ListItem varchar (200) COLLATE database_default
)

This solves the “Cannot resolve collation conflict for equal to operation.” error.