Solved

MS SQL SERVER 2005 - Cannot resolve the collation conflict between...

Posted on 2013-12-27
9
672 Views
Last Modified: 2014-03-22
Hi Experts,

I have uploaded my website with its SQL SERVER 2005 database to the web server.

I got the following error, although, I had created the db with a script from my local database that is done with SQL 2005, so why would this conflict occur:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Arabic_CI_AS" in the like operation.

Any idea?
0
Comment
Question by:feesu
  • 6
  • 3
9 Comments
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 500 total points
ID: 39742902
You should check the default collation on the server.  Most likely, you will find out that it is set to the Arabic_CI_AS collation in Windows.

Resolution to this issue can be difficult.  The simplest solution requires code changes something like this:

SELECT *
FROM MyTable mt
WHERE MyValue LIKE @FilterValue COLLATE SQL_Latin1_General_CP1_CI_AS
0
 

Author Comment

by:feesu
ID: 39742907
Hi bhess1,

The problem is how would I know where to change?

I have generated the whole database with a script from my local which runs perfectly, and the new hosting server is by GoDaddy, which they also say that there is nothing to be done from there side!
0
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 500 total points
ID: 39742969
The first thing to do is to identify where you are getting the error.  Some debugging code should identify the query generating the error.

You can also look at the collation of the server and the databases on the server.  Try running these statements

SELECT CONVERT (varchar, SERVERPROPERTY('collation')); -- Gives the Server Default collation

SELECT name, collation_name FROM sys.databases; -- Gives the collation of each DB

Obviously, if some particular DB is set to the Arabic collation, you will need to fix that DB.  If neither of these turn up anything, we'll need to check the tables next.
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:feesu
ID: 39742991
On GoDaddy's server:
1- SQL_Latin1_General_CP1_CI_AS
2- SQL_Latin1_General_CP1_CI_AS

On my local server:
1- SQL_Latin1_General_CP1_CI_AS
2- Arabic_CI_AS
0
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 500 total points
ID: 39743011
Ah.  So, your tables are probably configured with Arabic_CI_AS collation on at least some of the text fields.

I recommend this article:

http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

It is complete and understandable.  Run the collation changes on GoDaddy's server, and figure out how your DB collation locally got set to Arabic....
0
 

Author Comment

by:feesu
ID: 39743017
It's important that you know before I follow that article, that my db has got some Arabic rows. They have to be there.

Do I go ahead with the article?
0
 

Accepted Solution

by:
feesu earned 0 total points
ID: 39745626
bhess1,

Please respond! If I change the collation, will I still be able to read the Arabic content?
0
 

Author Comment

by:feesu
ID: 39745677
I have run the script, it showed that there were only 2 tables that do not have Arabic collation. I have updated these, but then how do I update the database's collation? Cuz when I do that from the property pages I get permission error as it is goDaddy's server.

I am still getting that error on the homepage.
0
 

Author Closing Comment

by:feesu
ID: 39947210
It was a partial solution.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

831 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