Call us Today! 201-728-8809 |
Barcode Tracking Products
Barcode File Tracker
Barcode Medical Chart Tracker
Traverse Barcode Asset Tracking Software
Traverse Barcode Inventory Tracking Software
Barcode Point of Care
Barcode Point of Care Hospital / Nursing Homes
Barcode Point of Care Schools
VICS Bill of Lading
At a Glance
SQL Database Health System
SQL Database Health Monitor
SQL Database Data Cleaner
SQL Backup and Copy and FTP
Full Disk? Find Largest Directory
Dynamic DNS Utility
Analysis and Design
Scope Creep Mitigation
Database Troubleshooting and Tuning Series
SQL Collations vs Windows Collations
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
November 2015. Volume 2 - Issue 3
By Tom Romeo
I thought my database was collation agnostic.
Featured on SQL Server Central
After having written several articles, I noticed that while the intent was to help, they had an underlying tone that I didn't like.
All of the articles were my way of venting about things that I had seen in my travels that had made my life difficult.
Essentially I was ranting and raving about things that annoyed me.
I've decided that I don't want to be that guy, I'd rather be the guy that helps, which was always my intention.
Funny thing, on my way to the office, I saw written on the back windshield of the car in front of me, the words,
"pissing on my lawn does not make your lawn greener"
While this may have many interpretations, I associate it with the ranting guy, the guy I don't want to be.
I'm not going to tell you to do anything or to not do anything. I'm just going to tell you what happened to me and how I got past it.
Also this was not a PCS customer. It happened somewhere else.
Working backwards, a customer had an issue with our application and sent me a copy of their database. I restored the database on our SQL Server and attempted to start the application to reproduce the customer's errors at our office. I was not able to start the application.
This frustrated me because I know the application works. The first thing I always do is look in the log file for errors, but what I saw was a new and surprised me.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
At first glance I was perplexed, but after a little digging the answer became obvious. I know the suspense is killing you so if you want to just skip to the solution,
scroll down to the solution.
When you install SQL server, there are many collations available, but the primary characteristic that our application needs is case insensitivity. Our application requires the database to be case insensitive. I don't know that any of the other attributes of collation really matter as far as the application is concerned. They might, but no other collation has ever been tested.
Every time I have ever installed SQL Server, this collation seems to be the default "SQL_Latin1_General_CP1_CI_AS". Then again my regional settings on the server are usually Eastern Time zone and US English.
To break collation set down:
Latin1 makes the server treat strings using charset Latin 1, basically ASCII
CI case insensitive comparisons so 'ABC' would equal 'abc'
AS accent sensitive, so 'ü' does not equal 'u'
After SQL Server 2005 it seems that the SQL group is deprecating the SQL collations and recommending Windows collations.
This technet article from Microsoft explains it
Clearly the error is stating that there is a conflict between two collations. One would think that "SQL_Latin1_General_CP1_CI_AS " and "Latin1_General_CP1_CI_AS ", should be identical with the exception that one is a SQL collation and the other is a Windows collation.
The customer must have installed his/her SQL server using the Windows collation. When he/she installed our application, the resulting database used the SQL Server's collation, which is how the application is designed. Also, it is important to note here that the customer did not experience this issue. Collation was not their problem, they had a different problem which I was still unable to test.
Still this does not explain why I received the error.
Per the title of this article, our application does not impose any particular collation, but we do require that the customer uses a collation that supports case insensitivity.
So why then would a stored procedure within our application that does not specify anything about collation throw a collation exception?
Many of our stored procedures use temp tables which reside in tempdb. That is, some of our stored procedures issue the command create table #myTempTable …
When the customer's database which uses the collation "Latin1_General_CP1_CI_AS" was restored on our SQL server which uses the collation "SQL_Latin1_General_CP1_CI_AS" all temp tables created by stored procedures by default used the SQL Server's collation "SQL_Latin1_General_CP1_CI_AS" because tempdb inherits the SQL Server's default collation. The collation of the temp tables in tempdb did not match the database's collation of "Latin1_General_CP1_CI_AS".
My first reaction was to change the collation of my SQL server, but this is a bad idea because all of the other databases on that SQL server would be dropped, or at least no longer referenced by the master database and no longer present in sys.databases.
Then I thought it might be easier to change the collation of the database. This proved to be tricky because the foreign keys prevented me from changing tables that were part of the dependency chain. I would have needed to drop all the foreign keys and any other constraints and then re-create them.
So to get around the problem we installed another SQL instance with the "Latin1_General_CP1_CI_AS" collation, restored the database on the new instance, pointed the application to that database and we were done. This however is not practical because who knows from where the next backup will come.
The Real Solution
The real solution was to locate the stored procedures and stored functions that create temp tables and append this string, "
", to the definition or each
char, varchar, nchar,
Notice that we are still collation agnostic. We don't specify a particular collation but rather specify in the creation of the temp tables to use the collation of the database and not the collation of the SQL Server.
You would also need to employ similar techniques for cross database queries.
Also, as a side note, know with great benefit that CTE's (Common Table Expressions) are immune to this issue. You can test any of the AdventureWorks CTE examples to see for yourself.
Hope you enjoyed my wild ride and of course I welcome your comments. If you have found a better way to achieve this, please share.
How can we help you with your applications?
Click here to see other articles in the series.
VICS Bill of Lading
QuikVixBOL ™ Overview
QuikVixBOL ™ Technical Details
QuikVixBOL ™ Success Stories
Barcode tracking solutions
← Traverse → ™ Overview
Bar-Code File Tracking System
Bar-Code Medical Chart Tracking System
Prevent Medication Errors with barcode technology
Barcode Point of Care for Schools
Barcode POC Overview
What Is My IP
DB Health Assistant
IT Consulting New York City Metro Area
Custom Software Solutions
Scope Creep Rescue
IT Expert Witness Services
Pragmatics - PCS eNewsLetter Articles
Database Performance Series - Proper SQL Transactions
Database Performance Series - Collecting Empirical Data
Database Performance Series - SQL Execution Plan Analysis
Database Performance Series - SQL Server Troubleshooting
Using GUIDs in the database
What is all this CRUD?
I thought my database was collation agnostic.
Free Stuff From PCS
Where Did My Disk Go Utility to find lost Disk space
Print a photo ID for your child
Math Hound - Help your children master basic arithmetic
True Cost Mortgage Calculator
True Cost Shows how much things actually cost
SimpleQ a Simple SQL Database Query Tool
Our About Page
Our Contact Page
Full Site Map
Practical Compliance Solutions
One Orient Way - Suite F156
Connect With Us
News and Articles
I thought my database was collation agnostic
© 2002 - 2019 - Copyright Practical Compliance Solutions - All rights reserved