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
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 ) |
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] GO |
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
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."
See original article here: http://www.microsoft.com/sql/techinfo/tips/administration/changingcodepage.asp
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 |
CP1 - Codepage 1?
CI case insensitive
AS Accent Sensitive.