COLLATION CONFLICT MS SQL 2000 URGENT HELP REQUIRED!

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

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

Apologies, i Misread, i thought i saw that the collations were different

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

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

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?

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....

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

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 :-)

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....