Solved

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

Posted on 2013-12-27
9
634 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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now