Solved

SQL_Latin1_General_CP1_CS_AS error

Posted on 2013-01-14
8
687 Views
Last Modified: 2013-07-08
We created a data warehouse that gets data from our Management System.  We were getting errors then realized that the Management System was created as Case Sensitive.  when we changed our database from CI to CS all worked fine.  However, now when we add new tables to our database and try to use them in queries we get:

"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation."

We are getting this error in Enterprise and in MS Access.

Why are we getting when we work with the new tables?
0
Comment
Question by:KCTechNet
  • 4
  • 4
8 Comments
 
LVL 40

Expert Comment

by:RQuadling
ID: 38774990
Are you joining tables together, one from each collation sequence?

Well. Yes. You are.

There are 2 things here.

1 - The issue.
2 - The solution.

The issue is that internally, the data being held cannot be compared without it being cast. So, rather than automatically casting it, it throws an error.

The solution ... well, there are several.

Quick'n'dirty.

select cs.*,cs.* from cs inner join ci on cs.col COLLATE SQL_Latin1_General_CP1_CI_AS = ci.col

Open in new window


sort of thing.

But, you are going to have to add that to EVERY query to get the join.

The better (and harder) solution is to change everything to use utf8_unicode_ci.

The database, the tables, the indexes, etc. The whole lot.

This will allow you to use data from anywhere in the world in a clean way. Case insensitive comparisons and indexing.

It isn't as simply as changing the collation. All the data needs to be re-imported and all the indexes need to be re-made.

Depending upon how far down your development route you are, ... well, ... this could be a lot of work.

I've never had to do this (I always ALWAYS use utf8_unicode_ci). It MAY be easy for you to create a new DB and recreate the tables (structure and data).

I hope this helps.

Good luck.

Richard.
0
 

Author Comment

by:KCTechNet
ID: 38775162
but why is this an issue with only newly created tables?  The tables that existed when we switched to Case Sensitive work fine without the need to use COLLATE.

when you ask if we are joining tables together, one from each collation sequence, won't all tables created have the same collation sequence?
0
 

Author Comment

by:KCTechNet
ID: 38775171
also, we would like to keep this database as CS, not CI, since we are loading data daily from our Management System tables, which are CS.
0
 

Author Comment

by:KCTechNet
ID: 38776533
I am getting this on Union Queries also.  If I look at each part separately, they work fine.  When I add them together with a Union I get the error. How do I resolve this?
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 40

Accepted Solution

by:
RQuadling earned 500 total points
ID: 38783198
When you changed the collation sequence, this only applied to NEW tables.

You also need to change the inherited collation sequence for the existing tables and the existing columns.

The underlying data and indexes will also need to be validated (export/import most likely, but please check/test before you use this in a live/production environment).

The issue you have is that the old tables and the new tables have different collation sequences, so, when you try to join the tables in any way, their is a conflict and casting is required (casting by the use of COLLATE).

It is only (IME/AFAIT) an issue when you are connecting data from multiple tables (joins, queries, CTE's, etc.). If you are querying tables with the same collation (i.e. all old tables or all new tables), then you have no issue.

Fundamentally, if the data you are holding is marked as CS, where should data marked as CI  go when sorted (or vice-versa). SQL will not decide for you as there is no "right" answer for everyone.

Try these steps.

1 - Make a copy of your entire DB as it stands.
2 - Build a simple query between a CS and CI table. It should currently fail.
3 - Amend the table that is CI to CS.
4 - Rerun query - may/should still fail - you may be given warnings about data loss - I don't know and can't test at the moment, so this is why you are doing it on a COPY of the live data (PLEASE DO NOT DO ANYTHING ON THE LIVE DATA UNTIL YOU ARE SURE - PLEASE HEED THIS WARNING).
5 - Amend the columns and indexes from CI to CS.
6 - Rerun the query. It should work.

In this though, you may be told about the data loss.

Personally though, I would go through the export/import route. Export the data to XML (or TAB delimited if appropriate).

I'd then clone the DB and amend all the tables/indexes to be CS (utf8_unicode_cs) and then reimport the data. That way, everything is clean and separate.

Can you check to see what the server collation sequence is? If this is incorrect for you, then you are going to have to be very careful as temporary tables and the like are created using the system collation sequence, and this could give you further unexpected results.

Basically. What you are asking to do is a MAJOR issue. It may seem minor, but it really isn't.

Worse case scenario is a server reinstall to the correct collation sequence (server collation may not be changeable once installed).
0
 

Author Comment

by:KCTechNet
ID: 38788494
thank you for such a detailed explanation.  We will need to chew on this for a little but i would like to keep this open as I may have questions as I work these steps.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 38794754
That's fine. Good luck.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 39308155
I'd be interested in knowing what you've done with regard to your issue. Did you do something other than I suggested? How did it all work out?
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now