Solved

SQL Server Collations Settings

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

895 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