[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL_Latin1_General_CP1_CS_AS error

Posted on 2013-01-14
8
Medium Priority
?
774 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:Richard Quadling
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 2000 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:Richard Quadling
ID: 38794754
That's fine. Good luck.
0
 
LVL 40

Expert Comment

by:Richard Quadling
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

873 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