May 4, 2005 at 1:21 am
hi,
i am executing the ff statement. cm3rm1 resides in a separate database. but both db are on the same server.
INSERT [KPI SD TEMP2] SELECT dbo.[KPI SD Operators].dscription, dbo.[KPI SD Operators].category, [servicecentermapped].dbo.cm3rm1.orig_date_entered,
[servicecentermapped].dbo.cm3rm1.updated_date from dbo.[KPI SD Operators]
left outer join [servicecentermapped].dbo.cm3rm1 on dbo.[KPI SD Operators].dscription = [servicecentermapped].dbo.cm3rm1.company
where [servicecentermapped].dbo.cm3rm1.category = dbo.[KPI SD Operators].category --and year(updated_date)>1900
order by [servicecentermapped].dbo.cm3rm1.company,[servicecentermapped].dbo.cm3rm1.category
i am getting the error:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
what gives?
thanks
ann
May 4, 2005 at 2:00 am
Within EM right-click on your database and select properties.
Under the general tab look at the Collation name (at the bottom), the chances are it is different in one database to the other.
I had a similar problem when I had to design a system that integrated with a remote database on a different server.
My database was LATIN_General_CI_AS where as the remote database was SQL_LATIN1_General_CP1_CI_AS.
In my particular case I needed to create a table variable for the particular operation I needed as below
DECLARE @tbl TABLE (SerialNumber VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY CLUSTERED)
May 4, 2005 at 7:55 am
As David Posted you are going to need the COLLATE statement
and you will have to use it either at the "join" , "where" or at both clauses depending on the datatypes. Ex :
INSERT [KPI SD TEMP2]
SELECT dbo.[KPI SD Operators].dscription
, dbo.[KPI SD Operators].category
, [servicecentermapped].dbo.cm3rm1.orig_date_entered
, [servicecentermapped].dbo.cm3rm1.updated_date
from
dbo.[KPI SD Operators]
left outer join
[servicecentermapped].dbo.cm3rm1
on
dbo.[KPI SD Operators].dscription = [servicecentermapped].dbo.cm3rm1.company collate ...
where
[servicecentermapped].dbo.cm3rm1.category = dbo.[KPI SD Operators].category collate ...
--and year(updated_date)>1900
order by
[servicecentermapped].dbo.cm3rm1.company,[servicecentermapped].dbo.cm3rm1.category
hth
* Noel
May 4, 2005 at 7:46 pm
i used the collate syntax and my query is working now.
i was just thinking, although i havent tried it yet, is it possible to just edit the collation setting of the other database so that the 2 db that i'm using will have the same collation setting? how will i go about in doing it? is this a better idea instead of using the collate in my query statement?
thanks.
ann
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply