Solved

SQL Server Collations Settings

Posted on 2007-04-11
5
1,771 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

708 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

13 Experts available now in Live!

Get 1:1 Help Now