SQL Server Collations Settings

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

nutnutAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Dale BurrellConnect With a Mentor DirectorCommented:
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
 
Dale BurrellDirectorCommented:
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
 
nutnutAuthor Commented:
Sorry didn't explain very well, that is what it is doing
0
 
Dale BurrellDirectorCommented:
I understand that is what is happening - but are you 100% sure its caused by the collation difference?
0
 
nutnutAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.