Cannot resolve collation conflict for equal to operation

[collierd]

Hello

I am using SQL Server 2000

I have looked around at various posts regarding this problem
Basically, I have 2 databases 1 internal (MDW) with Collation set to SQL_Latin_General_CP1_CI_AS - which I believe to be the default setting
The other database has come from an external source and has been uploaded into the SQL environment
This is called cts_paperco_export and has collation set to Latin1_General_CI_AS

Initially when using the code:

insert into EPM_PRODUCT
select distinct st001.product, st001.[description], st001.[analysis gp],
	null, null, null, null, null, null, null from CTS_PaperCo_Export.dbo.st001_ns_product_data st001
left outer join EPM_PRODUCT epm on (st001.product = epm.PRODUCT_CODE and st001.[analysis gp] = epm.PRODUCT_DESC)
where st001.[analysis gp] is not null
and st001.[file] = 'north'
and epm.PRODUCT_CODE is null

This returned Cannot resolve collation conflict for equal to operation

So I amended the collation using:

use master
go
alter database cts_paperco_export
collate SQL_Latin1_General_CP1_CI_AS

Properties for the database suggest this amendment took place

It still returns Cannot resolve collation conflict for equal to operation when I run the insert statemant

Anybody got any thoughts?

Thanks

Damian.

 

[vongrunt]

Unfortunately, columns with previously specified collation do not inherit DB collation after it gets changed.

See data from INFORMATION_SCHEMA.COLUMNS view to check that.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[collierd]

ok, thanks

Tried this and it works
Just a bit long winded as I need this database for a lot of info and will have to convert every time
Oh well at least there is a work around

select distinct product, [description], [analysis gp],
	null, null, null, null, null, null, null from CTS_PaperCo_Export.dbo.st001_ns_product_data st001
left outer join EPM_PRODUCT epm on 
	(st001.product collate SQL_Latin1_General_CP1_CI_AS = epm.PRODUCT_CODE and st001.[analysis gp] collate SQL_Latin1_General_CP1_CI_AS = epm.PRODUCT_DESC)
where st001.[analysis gp] collate SQL_Latin1_General_CP1_CI_AS is not null
and st001.[file] collate SQL_Latin1_General_CP1_CI_AS = 'north'
and epm.PRODUCT_CODE is null

Damian.