1 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: SQL collation error

I am getting a collation error on running some SQL within an xml report (a really neat routine developed for us by Mike Wideili that splits imported measurement text into a number of measurements), using SQL Server 2005 Express. The information box says ... There is an error in the SQL code. The error message is: 'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation'.

Further investigation shows that the master, tempdb, model and msdb databases all have the Latin1_General_CI_AS collation, but NBNData has SQL_Latin1_General_CP1_CI_AS, so I am guessing that this is a difference between MSDE and SQL Server 2005 Express. The question is, what to do about it? I don't want to start meddling with the NBNdata database!

Any suggestions?

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: SQL collation error

Because of the way Recorder is installed, the collaction sequence is specified as SQL_Latin1_General_CP1_CI_AS which may differ from the server you are installed on - this is not a problem. It does mean, however, that Mike's code may need to specify a collaction sequence to use when comparing columns from the NBNData database and any additional columns that his script has created, for example if the script puts data into a temporary table then the collation sequence will need to be specified.

Mike, take a look at the SQL keyword COLLATE in order to fix this.

Best Wishes

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: SQL collation error

An update on the collation errors, having finally found time to get to grips with the problem.

As John suggested, they occur whenever a temporary table is created in SQL if the collation sequence of the temporary database TEMPDB is different to that of NBNDATA. In my case this was caused by my taking the default options (not knowing any better) when installing SQL Server 2005 Express, which I did when moving R6 onto a Vista machine as MSDE is not supported. The new default collation is the Windows default Latin1_General_CI_AS, rather than the older standard SQL_Latin1_General_CP1_CI_AS. This is not a problem so long as the SQL includes a COLLATE SQL_Latin1_General_CP1_CI_AS statement for every column in the temporary table, but if any of these are missing the SQL fails with a collation error.

XML reports are easily amended, but when I found that batch updates and the delete survey add-in were also failing I gave in and reinstalled SQL Server 2005 Express, this time with the advanced options showing so that the SQL collation sequence could be specified. This worked and the collation errors have disappeared, but the reinstallation was traumatic and two days later I still don't have everything working as it should.

The moral of the story - Specify the correct collation sequence the first time round!

Janet Simkin
British Lichen Society

Janet Simkin's Website