Solved

SQL_Latin1_General_CP1_CS_AS error

Posted on 2013-01-14
8
708 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 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: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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.

829 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