Solved

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

Posted on 2013-12-27
9
703 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: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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 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