Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

DISCLAIMER: I know that this question has been asked a hundred times before, but I just wanted to check that there wasn't an easier solution I might have missed before I went ahead and wrote/sourced lots of code to do it.

Our software uses a database that was originally designed for SQL Server 7, and as such, all the scripts that create it do not specify any explicit collation for any character columns. Instead, when a database is created/restored to SQL Server 2000 or above, every column inherits the database collation (which happens to be SQL_Latin1_General_CP1_CI_AS since that was the SQL Server 7 default).

Theoretically, this would not matter too much, since if our database is created from scratch on a customer's server, it inherits the customer's server collation (which is normally the modern installation default, Latin1_General_CP1_CI_AS) and everything just works. However, this scenario breaks down when they send us a database backup, or we send them a database backup, and either we or they get the dreaded collation mismatch error whenever the code tries to access temp tables etc.

We have tried educating customers to install or rebuild their SQL Server instances to use our preferred collation, but of course that doesn't always happen and it's not always possible.

Solutions that involve creating a new database and copying the data are not really practical for us, we need a "magic wand" that we can wave at a live database to correct all columns in-place without disturbing the data. I am thinking about writing a utility to do this, but since it will be quite a big job, does anyone have any simpler suggestions?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

One option is to "proof" your code against collation mismatch.

You can use the special collation "DATABASE_DEFAULT" to coerce without knowing what the actual collation is. You use it on char type columns in temp tables, table variables and system tables you need to use.

Example:

CREATE TABLE #Currency (CCY CHAR(3))
GO
INSERT #Currency VALUES ('GBP')
INSERT #Currency VALUES ('CHF')
INSERT #Currency VALUES ('EUR')
GO
SELECT Something
FROM myTable M JOIN #Currency C ON M.CCY = C.CCY --error!
GO
-- in join too
SELECT Something
FROM myTable M JOIN #Currency C ON M.CCY = C.CCY COLLATE DATABASE_DEFAULT --no error
GO
DROP TABLE #Currency
GO


CREATE TABLE  #Currency (CCY CHAR(3) COLLATE DATABASE_DEFAULT)
GO
INSERT #Currency VALUES ('GBP')
INSERT #Currency VALUES ('CHF')
INSERT #Currency VALUES ('EUR')
GO
SELECT Something
FROM myTable M JOIN #Currency C ON M.CCY = C.CCY --no error!
GO

DROP TABLE #Currency
GO

This also means that when your clients migrate their DB onto a new whizzy SQL Server box with yet another different collation, it works too...

share|improve this answer
    
Excellent idea, thanks very much! –  Christian Hayter Jul 30 '09 at 7:38

Short answer is there is not an easy way. I've had the same issue in the past.

What I would say is 2 things, first when your customer sends you a database with an unexpected collation, install a new SQL instance with a default collation that matches their DB and work with it within that.

The second is make sure you app will work with other collations then the defaults (since those could change in the future) and work correctly as long as the collation on the SQL server and DB match. Then its fairly easy to have the customer install SQL server with a collation that matches their DB and will then work.

Or write a utility that will update all the tables, etc in your database as you said, but that might be more work then you want.

share|improve this answer

If all the columns in the database are of the same collation then it will only cause problems for you when making cross-database queries (or your application is sort-order sensitive).

The sticky point comes when you realise that joining to temporary tables is cross-database, as they are in tempdb. That is easy to sort though - just make sure than any text columns in temporary tables are explicitly created with the COLLATE database_default directive. This means that the column will be created with the current database's default collation instead of tempdb's default collection (which will be the same as the server's default).

share|improve this answer
    
Excellent idea, thanks very much! –  Christian Hayter Jul 30 '09 at 7:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.