[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL_Latin1_General_CP1_CS_AS error

Posted on 2013-01-14
8
Medium Priority
?
759 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

656 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