Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Collations Settings

Posted on 2007-04-11
5
Medium Priority
?
1,812 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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