SQL Collation

Where SQL causes me some mild irritation...

Background

I run a membership database. I recently sent out a survey as a web page. The survey is to close in a week's time so I want to remind those members that have not replied to do so; and to thank those members that have done so.

Emails are sent out using a custom application, honed in C# .NET, which I like and wholeheartedly recommend.

I am using MS SQL 8.0, known as "SQL Server Desktop Engine".

Here's the "thank you" query that I expected to work:

select * from email

where Address in (select email from bb4mk_q.dbo.results )

No - I get the following error:

Cannot resolve collation conflict for equal to operation.

Here's the fix I used:

select * from email

where Address collate SQL_Latin1_general_CP1_CI_AS
in (select email from bb4mk_q.dbo.results )

What's going on and who's to blame?

Understanding where the problem took a bit of head scratching. There are 3 database servers involved. The email script accesses a SQL SQL Server Desktop Engine. The survey runs on the web site on Server 2003. The web survey form was designed with SQL Server Desktop Engine lurking in the background - its default collation set to "Windows Collation"

We all know who to blame don't we - Bill!

Cutting a long story short....

The web survey populates a results table. Here's part of the script that created the original table:

CREATE TABLE [dbo].[Results] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
...
[Email] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
...
) ON [PRIMARY]
GO

The SQL script has slipped in a different collation! The web server appears totally happy - all it's doing is inserts.

The Reason

The SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS. For SQL Server 2000 default code page is Latin1_General_CI_AS.

"Microsoft made this change to reduce the scope of differing results that the incompatible SQL Server and Windows code pages cause."

Fixes

I got round the problem by changing the query, as shown above. I could have re-collated the entire survey database

http://www.sqlservercentral.com/columnists/mpearson/changingroguedatabasecollations.asp
http://www.sqlservercentral.com/columnists/glarsen/collate_part1.asp

or just the table column

ALTER TABLE Results
ALTER COLUMN Email varchar(50) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL

Some clues:

CP1 - Codepage 1?

CI case insensitive

AS Accent Sensitive.