Solved

SQL Server Collations Settings

Posted on 2007-04-11
5
1,782 Views
Last Modified: 2008-01-09
Hi,

I have 2 SQL Server 2000's.  One is a DEV_Server and one is a LIVE_Server.  
I have 2 Databases one is a DEV_DB and one is a LIVE_DB.

My problem is to do with the "Collation" settings.

LIVE_Server is set to Latin_General_CI_AS
DEV_Server is set to SQL_Latin1_General_CP850_CI_AS

LIVE_DB is set to SQL_Latin1_General_CP850_CI_AS
DEV_DB is set to SQL_Latin1_General_CP850_CI_AS

When I run SQL scripts againist DEV_DB on the DEV_Server all is perfect.
When I run SQL scripts againist LIVE_DB on the LIVE_Server I get all sorts of unexpected locks etc etc (it is just not happy!!)

I have put this down to the Collations being different.  

My questions are "What is the quickest way to get the LIVE_DB to run on the LIVE_Server"?  And what (if any) are the implications to the servers, my databases or any other databases on the servers?

Please bear in mind also I CANNOT change to collations of either of the servers (company rules!)

Hope someone can help please!

nutnut

0
Comment
Question by:nutnut
  • 3
  • 2
5 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 18888241
I would be very surprised if the slowdown/locks are due to the collation differences - when you have a collation problem it normally doesn't work at all and throws a collation mismatch error.
0
 

Author Comment

by:nutnut
ID: 18888297
Sorry didn't explain very well, that is what it is doing
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 18888400
I understand that is what is happening - but are you 100% sure its caused by the collation difference?
0
 

Author Comment

by:nutnut
ID: 18888501
I think so because if I change the collation on the DEV_DB to be Latin_General_CI_AS and run it on the DEV_Server I get the same error.

Any ideas please?
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 total points
ID: 18893793
Usually the collation problem occurs when you have a query/view that requires the use of tempdb - tempdb will be collated in the server default which in this case is not the same as your database collation. If you are not able to change the database or the server to match then one way to fix the problem is to find the query/view that is causing the problem and explicitly change the collation of the column that is causing the problem.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

749 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