SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Collation Error 468


Collation Error 468

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)

Group: Administrators
Points: 249247 Visits: 19809
Comments posted to this topic are about the item Collation Error 468

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
James Stover
James Stover
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 862
"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!


James Stover, McDBA

DanKennedy
DanKennedy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2392 Visits: 389
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.



Dan
www.firstcs.co.uk
Nebojsa Ilic
Nebojsa Ilic
Right there with Babe
Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)

Group: General Forum Members
Points: 789 Visits: 290
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.
Richard Dragossy-168541
Richard Dragossy-168541
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 31
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_AS



In 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
Carolyn Richardson
Carolyn Richardson
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4153 Visits: 3577
"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 MASTER

GO



SET NOCOUNT ON



DECLARE @DB VARCHAR (150),

@Counter INT,

@Rec VARCHAR (150),

@SQL VARCHAR (1000),

@SQL1 VARCHAR (1000),

@SQL2VARCHAR (1000)



SELECT database_id, name INTO #Temp

FROM sys.databases

WHERE 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)

BEGIN



SET @DB = (SELECT name

FROM #Temp

WHERE database_id = @Counter)



--Alter 'Latin' below if not just comparing US/UK



SET @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 #ctr



IF (@Rec > 1)

BEGIN

PRINT @DB

SET @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)

END

SET @Counter = @Counter + 1

END



DROP TABLE #ctr

GO

DROP TABLE #Temp

GO

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Dean Hill
Dean Hill
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 233
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
Neil Evans-Mudie
Neil Evans-Mudie
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 239
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-bef6bb1e3e1d



Michael Kaplan's blog entry @ http://blogs.msdn.com/michkap/archive/2006/05/30/610889.aspx



Hope this helps.



Cheers, Neil (DBA in UK)
chris webster
chris webster
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 323
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.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)SSC Guru (249K reputation)

Group: Administrators
Points: 249247 Visits: 19809
Probably because we're lazy in the US. Stick with what was working, etc. Wink

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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search