Solved

COLLATION CONFLICT MS SQL 2000 URGENT HELP REQUIRED!

Posted on 2006-06-29
12
1,647 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
ID: 17007971
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
ID: 17008156
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
ID: 17008165
Apologies, i Misread, i thought i saw that the collations were different
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:BYRONJACKSON
ID: 17008184
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
ID: 17008236
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17008321
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
ID: 17008329
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
ID: 17008513
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
ID: 17008618
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
ID: 17008628
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
ID: 17528327
PAQed with no points refunded (of 500)

DarthMod
Community Support Moderator
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

828 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