Where SQL causes me some mild irritation...
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
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 )
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!
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]
The SQL script has slipped in a different collation! The web server appears totally happy - all it's doing is inserts.
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
"Microsoft made this change to reduce the scope of differing results that the incompatible SQL Server and Windows code pages cause."
I got round the problem by changing the query, as shown above. I could have re-collated the entire survey database
or just the table column
ALTER COLUMN Email varchar(50) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL
CP1 - Codepage 1?
CI case insensitive
AS Accent Sensitive.