|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Cannot resolve collation conflict for equal to operationerror: Server: Msg 446, Level 16, State 9, Line 2 Cannot resolve collation conflict for equal to operation I can select the data from either of the databases without a problem. When I try to join the selection the error arises. Further, if I export the table from the other database into the first database then the join works OK. The join fields are both type varchar. The only difference is that one is 255 characters long whereas the other is 16. I am using SQL Server 2000 What is causing this problem and how do I solve it? SQL used - SELECT * FROM SpaceData.afm.dv RIGHT OUTER JOIN dbo.tblDivisions ON SpaceData.afm.dv.dv_id = dbo.tblDivisions.divisionId Paul
> SELECT * FROM SpaceData.afm.dv You are trying to join two columns (from two different databases) that have> RIGHT OUTER JOIN dbo.tblDivisions > ON SpaceData.afm.dv.dv_id = dbo.tblDivisions.divisionId a different collation. What is a datatype of divisionId? Why is should be VARCHAR(n)/CHAR(n)? SELECT * FROM SpaceData.afm.dv RIGHT OUTER JOIN dbo.tblDivisions ON SpaceData.afm.dv.dv_id = dbo.tblDivisions.divisionId COLLATE LATIN......(put here your default collation) Show quote "Paul Robinson" <alitor99@NOSPAMHEREbtinternet.com> wrote in message news:Og0DvW5gFHA.2852@TK2MSFTNGP15.phx.gbl... > I am trying to join 2 tables from different databases and keep getting the > error: > Server: Msg 446, Level 16, State 9, Line 2 > Cannot resolve collation conflict for equal to operation > I can select the data from either of the databases without a problem. > When I try to join the selection the error arises. > Further, if I export the table from the other database into the first > database then the join works OK. > > The join fields are both type varchar. > The only difference is that one is 255 characters long whereas the other is > 16. > I am using SQL Server 2000 > > What is causing this problem and how do I solve it? > > SQL used - > > SELECT * FROM SpaceData.afm.dv > RIGHT OUTER JOIN dbo.tblDivisions > ON SpaceData.afm.dv.dv_id = dbo.tblDivisions.divisionId > > Hi
Check the collations on the 2 column as they are different. SELECT * FROM INFORMATION_SCHEMA.COLUMNS Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "Paul Robinson" wrote: > I am trying to join 2 tables from different databases and keep getting the > error: > Server: Msg 446, Level 16, State 9, Line 2 > Cannot resolve collation conflict for equal to operation > I can select the data from either of the databases without a problem. > When I try to join the selection the error arises. > Further, if I export the table from the other database into the first > database then the join works OK. > > The join fields are both type varchar. > The only difference is that one is 255 characters long whereas the other is > 16. > I am using SQL Server 2000 > > What is causing this problem and how do I solve it? > > SQL used - > > SELECT * FROM SpaceData.afm.dv > RIGHT OUTER JOIN dbo.tblDivisions > ON SpaceData.afm.dv.dv_id = dbo.tblDivisions.divisionId > > > The distant database has the collation SQL_Latin1_General_CP1_CI
The other database uses collation Latin1_General_CI I haven't a clue what this is all about. How are the different collations set? I guess it is at the database level because the table that I copied over is collated the same as the other tables in that database, not as the tables in its original database. Show quote "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message news:C3C6E39D-9B7F-432A-9FC5-6DF7D01F4CE6@microsoft.com... > Hi > > Check the collations on the 2 column as they are different. > > SELECT * FROM INFORMATION_SCHEMA.COLUMNS > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > > > "Paul Robinson" wrote: > > > I am trying to join 2 tables from different databases and keep getting the > > error: > > Server: Msg 446, Level 16, State 9, Line 2 > > Cannot resolve collation conflict for equal to operation > > I can select the data from either of the databases without a problem. > > When I try to join the selection the error arises. > > Further, if I export the table from the other database into the first > > database then the join works OK. > > > > The join fields are both type varchar. > > The only difference is that one is 255 characters long whereas the other is > > 16. > > I am using SQL Server 2000 > > > > What is causing this problem and how do I solve it? > > > > SQL used - > > > > SELECT * FROM SpaceData.afm.dv > > RIGHT OUTER JOIN dbo.tblDivisions > > ON SpaceData.afm.dv.dv_id = dbo.tblDivisions.divisionId > > > > > >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_1pwz.asp
--
Show quote
-------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ "Paul Robinson" wrote:
> The distant database has the collation SQL_Latin1_General_CP1_CI > The other database uses collation Latin1_General_CI > > I haven't a clue what this is all about. How are the different collations > set? I guess it is at the database level because the table that I copied > over is collated the same as the other tables in that database, not as the > tables in its original database. > > > "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message > news:C3C6E39D-9B7F-432A-9FC5-6DF7D01F4CE6@microsoft.com... > > Hi > > > > Check the collations on the 2 column as they are different. > > > > SELECT * FROM INFORMATION_SCHEMA.COLUMNS > > > > Regards > > -------------------------------- > > Mike Epprecht, Microsoft SQL Server MVP > > Zurich, Switzerland > > > > MVP Program: http://www.microsoft.com/mvp > > > > Blog: http://www.msmvps.com/epprecht/ > > > > > > > > "Paul Robinson" wrote: > > > > > I am trying to join 2 tables from different databases and keep getting > the > > > error: > > > Server: Msg 446, Level 16, State 9, Line 2 > > > Cannot resolve collation conflict for equal to operation > > > I can select the data from either of the databases without a problem. > > > When I try to join the selection the error arises. > > > Further, if I export the table from the other database into the first > > > database then the join works OK. > > > > > > The join fields are both type varchar. > > > The only difference is that one is 255 characters long whereas the other > is > > > 16. > > > I am using SQL Server 2000 > > > > > > What is causing this problem and how do I solve it? > > > > > > SQL used - > > > > > > SELECT * FROM SpaceData.afm.dv > > > RIGHT OUTER JOIN dbo.tblDivisions > > > ON SpaceData.afm.dv.dv_id = dbo.tblDivisions.divisionId > > > > > > > > > > > > 
|
|||||||||||||||||||||||
Other interesting topics