SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / Collation Error 468 / Latest Posts

RE: Collation Error 468

mrpolecat — Wed, 26 Aug 2009 12:11:18 GMT

I didn't see it mentioned here but your database must be in 80 compatability for the collate to work in a select statement. I spent some time trying to figure out why I was getting a syntax error near the word collate.

RE: Collation Error 468

CyclingRabbit — Thu, 01 May 2008 15:18:33 GMT

When installing SQL Server you have two options:1) Collation designator and sort order (default is Latin1_General)2) SQL Collations (default is Dictionary order, case-insensitive).It looks like you are seeing the difference between the two. Did you by any chance install SQL using Windows Collation designator one system and checked off "Accent - sensitive", but used SQL Collations on the other system?

RE: Collation Error 468

Rachel Byford — Mon, 18 Feb 2008 07:59:41 GMT

I've had occasion to use COLLATE when I needed an accent insensitive search (I needed to find é as well as e when the user typed e), when all of our data has accent sensitive collation. The collation name has _AI on the end instead of _AS.

RE: Collation Error 468

Marcus Malinow — Mon, 15 Oct 2007 10:35:40 GMT

Personally I came across a similar issue when I was involved with the transfer of an application from a site in Holland to my office in the UK. It seems that the application had originally been developed in SQL Server v7 and over the years the instance had been upgraded to SQL Server 2000. I believe (I may be wrong here) the default collation settings had changed between versions, thus when I restored the database into a fresh instance of 2000 I started getting these odd collation errors.What seemed to be happening was that the collation settings were different on the application database as opposed to the tempdb database, which we were using for temporary tables. I had to figure out what the necessary collation setting was for my tempdb, something that unfortunately can't be changed after SQL Server has been installed. Armed with this knowledge I had to reinstall my SQL Server instance with the correct collation settings set up at install time.

RE: Collation Error 468

Steve Jones - SSC Editor — Sat, 13 Oct 2007 08:46:33 GMT

Found an explanation here:http://blogs.msdn.com/michkap/archive/2007/10/10/5391882.aspxYou have to dig through a few posts, but basically the SQL team chose this collation to make things run a little quicker from when the setting was server wide. I'm not sure I completely understand the differences though.

RE: Collation Error 468

rudy - Doctor "X" — Thu, 11 Oct 2007 13:12:44 GMT

I've run into this error a number of times because I usually use a MSX server to keep track and schedule maintenance amongst multiple servers. The offending database(s) are always vendor supplied. PeopleSoft and their usage of 'binary' for 'performance' being the most frequent offender.

RE: Collation Error 468

chris webster — Thu, 11 Oct 2007 07:54:06 GMT

All my research pointed to them being the same, SQL just can't get over the name difference.

RE: Collation Error 468

Steve Jones - SSC Editor — Thu, 11 Oct 2007 07:51:09 GMT

I pinged a few language people to see if they knew, but no response.I can't find a difference either. They should be the same, and could be, but I think SQL Server's response is the error if there's any naming difference.

RE: Collation Error 468

Carolyn Richardson — Thu, 11 Oct 2007 07:02:52 GMT

The same but not the same, SQL Server doesn't recognise them as the same.

RE: Collation Error 468

Jack Corbett — Thu, 11 Oct 2007 06:55:24 GMT

I had this issue pop up on my once and found the fix in BOL. I was under the impression that the 2 collations you encountered were the same. I would be interested to know what the differences are. I have not found a good resource on the differences in collations anywhere yet.

RE: Collation Error 468

Carolyn Richardson — Thu, 11 Oct 2007 06:24:37 GMT

I most often come across the issue when the server install gets completed and the guys don't alter the regional settings to UK, then when SQL Server gets installed it takes the US settings from the server default, I think in 2000 even if you changed the collation on the install you still had the issue if I remember rightly its some time since I've looked into this.

RE: Collation Error 468

Steve Jones - SSC Editor — Thu, 11 Oct 2007 06:08:42 GMT

Probably because we're lazy in the US. Stick with what was working, etc. ;)I'm sure many of you in the UK and elsewhere deal with this, but I was going on the stats for this site. We're 80% US readership. So I used the "most".Note that I didn't imply "best" :hehe:

RE: Collation Error 468

chris webster — Thu, 11 Oct 2007 04:32:58 GMT

This problem is excaserbated by 'part-time' DBAs in the UK blindly installing SQL Server with defualts without referring to the regional settings of the server which influence the default collation of the sql server. The UK default is the Windows collation introduced with SQL 2000 which according to microsoft gives performance benifits as it matches that of the OS. I have no idea why the US defaults to a collation that was supposed to be only there for backwards compatability.

RE: Collation Error 468

Neil Evans-Mudie — Thu, 11 Oct 2007 04:01:00 GMT

Steve, nice article and a nice pointer ref to Tony Rogerson. I ran in to this issue also here in the UK after I restored a DB built on a UK locale server to a US locale-built server which itself had DBs created using default US-locale led collation. Tony's article help me fix the resultant mixed-collation issues (as I wanted to union results from several source DBs with differing collations). Anyway one addition I wanted to mention was watch the collation on your Tempdb aswell as your User DBs, as it can affect your query results in a mixed collation DB environment. Here's a couple URLs:Kimberley Tripp article titled 'Changing Database Collation and dealing with TempDB Objects'@ http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=7b4c9796-66d0-4ed2-b19d-bef6bb1e3e1d#a7b4c9796-66d0-4ed2-b19d-bef6bb1e3e1dMichael Kaplan's blog entry @ http://blogs.msdn.com/michkap/archive/2006/05/30/610889.aspxHope this helps.Cheers, Neil (DBA in UK)

RE: Collation Error 468

Dean Hill — Thu, 11 Oct 2007 03:48:30 GMT

Hi, we've had similar problems with collations across different data sources, but have also found one bonus. As part of the validation for data imports we check for personal info being in upper case by using a case sensitive collation--Does the data need to be proper cased select 'Employee- ' +d.EMP_REF+ ' name- ' +d.SURNAME from DOWNLOAD_Employee d where substring(d.SURNAME, 2, 1) --get second character in SURNAME <> lower(substring(d.SURNAME, 2, 1)) --get second character again and lowercase it collate sql_latin1_general_cp1_cs_as --compare the two using a case-sensitive collation

RE: Collation Error 468

Carolyn Richardson — Thu, 11 Oct 2007 03:40:13 GMT

"Most DBAs don't ever deal with multiple languages or different collation and sort order settings in SQL Server" The majority of UK DBA's will have come across collation issues, its a common problem for me. I currently have a server having 9 databases with various different collations at table and row level and am trying to clean up the mess.... To identify whether I had issues:-USE MASTERGOSET NOCOUNT ONDECLARE @DB VARCHAR (150),@Counter INT,@Rec VARCHAR (150),@SQL VARCHAR (1000),@SQL1 VARCHAR (1000),@SQL2VARCHAR (1000)SELECT database_id, name INTO #TempFROM sys.databasesWHERE name NOT IN ('Master', 'tempdb','msdb','model')SET @Counter = (SELECT MIN(database_id) FROM #Temp)/*Work out if a database has more than one collation, assumes only interested if more than one*/CREATE TABLE #ctr( NumRows int )WHILE @Counter <= (SELECT MAX(database_id) FROM #Temp)BEGINSET @DB = (SELECT name FROM #TempWHERE database_id = @Counter)--Alter 'Latin' below if not just comparing US/UKSET @SQL = 'INSERT INTO #ctr SELECT count(distinct COLLATION_NAME) FROM '+ @DB +'.INFORMATION_SCHEMA.columns WHERE COLLATION_NAME LIKE ''%Latin%'' ' EXEC (@SQL)SET @Rec = (SELECT NumRows FROM #ctr)DELETE FROM #ctrIF (@Rec > 1)BEGINPRINT @DBSET @SQL1 = 'SELECT TABLE_CATALOG AS [DATABASE], 'SET @SQL1 = @SQL1 +'TABLE_NAME, 'SET @SQL1 = @SQL1 +'COLLATION_NAME, 'SET @SQL1 = @SQL1 +'COLUMN_NAME, 'SET @SQL1 = @SQL1 +'DATA_TYPE 'SET @SQL1 = @SQL1 +'FROM '+ @DB +'.INFORMATION_SCHEMA.columns 'SET @SQL1 = @SQL1 +'WHERE TABLE_NAME <> ''dtproperties'' 'SET @SQL1 = @SQL1 +'AND COLLATION_NAME LIKE ''%Latin%'' 'SET @SQL1 = @SQL1 +'ORDER BY COLUMN_NAME'EXEC (@SQL1)ENDSET @Counter = @Counter + 1END DROP TABLE #ctrGODROP TABLE #TempGO

RE: Collation Error 468

Richard Dragossy-168541 — Thu, 11 Oct 2007 01:55:40 GMT

Hello Steve!I've nearly the same problem with collation. I've had to build a "data warehouse" from four different different databases of four systems, one of them is multilingual.The solution slightly differs only:SELECT ...............FROM [DB1].[dbo].[TABLE1] as T1 inner join [DB2].[dbo].[TABLE2] as T2 on T1.name SQL_Latin1_General_CP1_CI_AS = T2.table_name SQL_Latin1_General_CP1_CI_ASIn this case both sides of join are forced to use the same collation :-)I hadn't time to check the theoretical fundamentals or efficiency aspects, but in practice it's works.Best regards: Richard

RE: Collation Error 468

Nebojsa Ilic — Thu, 11 Oct 2007 01:18:09 GMT

Good practice is to use same collation whenever it's possible. If you have databases with different collations on the same sever use COLLATION database_default in CREATE/ALTER TABLE statement to not confuse yourself. When import objects from other sources check collation of destination tables.

RE: Collation Error 468

DanKennedy — Thu, 11 Oct 2007 00:29:14 GMT

I've come across this one quite a bit at one contract I had as a third-party vendor insisted that the performance of a binary collation was much quicker than a case-insensitive one and so set the default to Latin1_General_Bin for everything. I'm not sure if there are tests/stats to back this claim up but I do know that the majority of queries against the data were name related and so most of their queries were written as UPPER([NameColumn]) = UPPER(@Criteria). Brilliant !! Index seek to index scan in one easy step.After seeing this I wasn't inclined to believe their performance enhancement claim.

RE: Collation Error 468

James Stover — Wed, 10 Oct 2007 23:41:51 GMT

"Most DBAs don't ever deal with multiple languages or different collation and sort order settings in SQL Server"Now there's a sweeping statement. In fact, I would say many (if not all) non-USA DBA's have had to deal with this. Convert to SQL_Latin1_General_CP1_CI_AS or die, Latin1_General_CI_AS scum!

Collation Error 468

Steve Jones - SSC Editor — Wed, 10 Oct 2007 23:29:16 GMT

Comments posted to this topic are about the item [B]Collation Error 468[/B]