Solved

COLLATION CONFLICT MS SQL 2000 URGENT HELP REQUIRED!

Posted on 2006-06-29
12
1,635 Views
Last Modified: 2008-04-25
Hi,
Have recently migrated from a 32 to a 64 bit machine, moving a SQL database as part of the exercise.
In both instances the database is SQL 2000 32bit, now using WOW (windows on windows) on the new server.
We have develped a number of applications which are performing selects between two database, and since the migration these are no longer fuctioning, giving the following error: -
Cannot resolve collation conflict fr equal to operation.

The confusion is that the collation settings on the database pre-migration are the same as the settings on the new database. Have checked the Linked Server settings and these are also the same.
Although we can bypass the problem by using COLLATE on the select statements this does mean having to change many thousands of lines of code, test and release. I understand how to change collation settings at a server and database level, but as they're the same it may point to something else (64bit machine?).

The two main servers have the following collation settings

Original - SQL_Latin1_General_CP1_CI_AS
Second - Latin1_General_BIN
(selects worked)

New - SQL_Latin1_General_CP1_CI_AS
Second - Latin1_General_BIN
(selects fail)

Can you help?

Byron



0
Comment
Question by:BYRONJACKSON
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 23

Expert Comment

by:apresto
Comment Utility
you can change the db collation like this:

ALTER DATABASE MyDatabase COLLATE YourCollation

i.e

ALTER DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1_CI_AS

run it in query analyser
0
 

Author Comment

by:BYRONJACKSON
Comment Utility
Thanks for the reponse.

The problem is not how to change the collation setting, either at the database or server level, but the fact that what was working doesn't work now.

The only difference is the move from a 32 to 64bit machine. Has anyone had any experience of a similar problem?

Best regards Byron
0
 
LVL 23

Expert Comment

by:apresto
Comment Utility
Apologies, i Misread, i thought i saw that the collations were different
0
 

Author Comment

by:BYRONJACKSON
Comment Utility
No problems at all ... WOW does not seem to be working which is now a real pain in the butt! - not sure what to do next - well I know but feel that there has to b an easier way around?

BJ
0
 
LVL 23

Expert Comment

by:apresto
Comment Utility
Afraid, i'm not help here then, not too familiar with WOW or 64bit MS Sql, however i'll set up a pointer question to this question to attract an expert who can

Apresto
0
What Is Threat Intelligence?

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

 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
Are you doing a join query on the two servers? (distributed transactioin)?

can you directly select from any or all of the tables in both databases without joins?
0
 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
one more thing, post a query that is failing (if possible)... I understand that you believe it's related to the migration, but let's elminate all possible factors first....
0
 

Author Comment

by:BYRONJACKSON
Comment Utility
Yes, the query is between two servers, one server remains unchanged, the other being replaced with a 64bit machine and OS layered with WoW (windows on windows) using 32bit SQL 2000.

There are no problems without joins.

The below examples show an example query. The second example worked before the migration to 64bit, the second with explicit collate works under 64bit.
Problem is I don't want to change all the applications. Lol!

---- CCC WIP (Promis) - COLLATE
select wp.lotid, wp.partid, wp.stage, wp.location, wp.cr2, wp.customername, wp.curmainqty, wp.State, wp.tech, wp.techcode,
  wp.FECode, wp.BECode , wp.ProcCode, wp.lottype, cu.name, cu.alpha, st.description, st.analysis_b,
  st.analysis_c, stt.text01, sn.Notation, st.mps_type, xp.CustOrderNo, xp.CustLineNo, xp.OSD, xp.NewLSD, xp.SageOrderNo, xp.SageLineNo
from ReporterDB.dbo.tWip wp
left outer join PLNT10.plymlive.scheme.slcustm cu on cu.customer collate SQL_Latin1_general_cp1_CI_AS = wp.customername
left outer join PLNT10.plymlive.scheme.stockm st on substring(wp.partid,1,charindex('.',wp.partid)-1) = st.product collate SQL_Latin1_general_cp1_CI_AS
left outer join PLNT10.plymlive.scheme.sttechm stt on st.product = stt.product and stt.page_number = '001'
left outer join PLNT10.plymlive.dbo.xPlan_Tech_Stage_Notation sn on sn.Technology collate SQL_Latin1_general_cp1_CI_AS = wp.tech and sn.Tech_Module collate SQL_Latin1_general_cp1_CI_AS = left(wp.stage,2)
left outer join PLNT10.plymlive.dbo.xPlan_LSD xp on xp.lotid collate SQL_Latin1_general_cp1_CI_AS = wp.lotid
where cu.name is not null order by wp.lotid
---- CCC WIP (Promis)
select wp.lotid, wp.partid, wp.stage, wp.location, wp.cr2, wp.customername, wp.curmainqty, wp.State, wp.tech, wp.techcode,
  wp.FECode, wp.BECode , wp.ProcCode, wp.lottype, cu.name, cu.alpha, st.description, st.analysis_b,
  st.analysis_c, stt.text01, sn.Notation, st.mps_type, xp.CustOrderNo, xp.CustLineNo, xp.OSD, xp.NewLSD, xp.SageOrderNo, xp.SageLineNo
from ReporterDB.dbo.tWip wp
left outer join PLNT10.plymlive.scheme.slcustm cu on cu.customer = wp.customername
left outer join PLNT10.plymlive.scheme.stockm st on substring(wp.partid,1,charindex('.',wp.partid)-1) = st.product
left outer join PLNT10.plymlive.scheme.sttechm stt on st.product = stt.product and stt.page_number = '001'
left outer join PLNT10.plymlive.dbo.xPlan_Tech_Stage_Notation sn on sn.Technology = wp.tech and sn.Tech_Module = left(wp.stage,2)
left outer join PLNT10.plymlive.dbo.xPlan_LSD xp on xp.lotid = wp.lotid
where cu.name is not null order by wp.lotid


Hope you can understand what I mean? I you need me to explain some more will do...


Best regards and also thanks to Apresto

Byron
0
 
LVL 12

Expert Comment

by:Einstine98
Comment Utility
OK... can you double check the column level on all the tables that are failing, for example when you check the column table in the design view in EM... you should see what is the collation used....

check this column  PLNT10.plymlive.dbo.xPlan_LSD.lotid

If the collation is different (which I believe it is) then that's your problem.. and you need to resolve it, one of the easy ways is to generate a script that would change back all the collation on all the string fields back to the database default...

Can't do it from the top of my head now, but would be able to help you out tomorrow... I have to go to sleep now :-)
0
 
LVL 11

Expert Comment

by:deroby
Comment Utility
I'm a bit surprised that it worked on the original setup, joining fields that have a different collation should always give you an error IMHO.
Anyway, what might have an impact too is the collation used in the tempdb. Might be worth checking if the new setup was installed identical to the old setup.

("EXEC sp_helpdb" should give you the collations of tempdb/master/model etc)

Can't really come up with anything else right now....
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
Comment Utility
PAQed with no points refunded (of 500)

DarthMod
Community Support Moderator
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 108
sql query 7 34
MS SQL Backup 24 69
sql server query? 6 26
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.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 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

7 Experts available now in Live!

Get 1:1 Help Now