Solved

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

Posted on 2013-12-27
9
658 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL LINE CONTINUATION ISSUE 12 33
Loop to go backward 90 days 2 18
SSRS 2013 - Creating a summarized report 19 35
Sql server, import complete table, using vb.net 9 34
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 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