Solved

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

Posted on 2013-12-27
9
709 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
[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
  • 6
  • 3
9 Comments
 
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess 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:Brendt Hess
Brendt Hess 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

626 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