Cannot resolve collation conflict for equal to operation
-
hi , I was copying data in tables from one database to another so I received this error "Cannot resolve collation conflict for equal to operation " if some one can help me please ... coz am stuck
thanks
-
Check the two databases collation settings (in EM right click db icon => properties) is one way to check. It may also be that certain columns in your copied tables have different collations between them. Depending on the query, tempdb may also come into play.
Have a look in BOL for 'COLLATE' for some more info.
/Kenneth
-
I'm not sure why just copying data would give you a problem, but if you're comparing data in 2 tables with different collation, then you need something like:
select * from TableA A join TableB B on A.KeyFld = B.KeyFld collate SQL_Latin1_General_CP1_CI_AS
This will allow you to join (or compare) fileds with different collation types.
-
Almost right.
But this must be better:
select * from TableA A join TableB B on A.KeyFld collate SQL_Latin1_General_CP1_CI_AS = B.KeyFld collate SQL_Latin1_General_CP1_CI_AS
_____________
Code for TallyGenerator -
You don't necessarily need the collate on both sides of the =
If TableA is CollationA, and TableB is CollationB, you only need to set the collation for one table to match the collation of other one, so
select * from TableA A join TableB B on A.KeyFld = B.KeyFld collate TableA_Collation
will work fine, because it sets TableB's key field to the same collation as TableA.
-
Yes, if you know TableA_Collation and sure it will never be different.
_____________
Code for TallyGenerator -
10x for ur reply and second yes am using join but the database am copying data to it am creating it at run time and the database am copying from it am choosing it at run time also so isn't there anything that i can do without changing the collate of 1 of these 2 databases ?
-
Can you post an example of your 'copy-code'?
ie what does the complete SQL statement look like?
Also, the complete actual error message might be informative as well.
/Kenneth
-
select *
from old.dbo.table1 left outer join
new.dbo.table2 on new.dbo.table2.id=old.dbo.table1.id
this is my SQL statement whil error is:
"Cannot resolve collation conflict for equal to operation"
-
Well, this looks very much like the two databases have different collations, therefore the server can't tell how to compare old.id against new.id.
The COLLATE syntax posted earlier will 'fix' it, at least in the sense that the error goes away. You need to substitute with the actual collation name the the offending database have.
Also, look up COLLATE in BOL (Books on Line) - quite a few things worth reading there about collations and sorting...
/Kenneth
-
can't i make something from database to specify collation for all new created databases and all old databases to be the same coz as i told u that old and new are not the actual names of database they are variables that hold database names
-
Ok, so the example wasn't quite correct then? (no variables in there)
Must confess that I lost the thread a bit here.. Can you expand a bit on your entire situation? Is this problem part of some larger operation you are trying to do?
New databases created will inherit their collation from model.
/Kenneth
-
first really 10x alot for ur help,
then, old is a variable that hold database name which the user will choose before begining copying data , and new is a variable that hold the name of new database that i will copy data too.
what i mean by a new database created at run time is that user can create a new empty database which contain same tables as tables in old database but empty tables .
i donno if am not clear
-
Starting to clear but still misty
Ok, so if we back off a little.. Can you post a part of the actual code that is used in this situation? (you may change names if they are sensitive, but retain the 'original' code anyway)
From the example
select *
from old.dbo.table1
left outer join
new.dbo.table2
on new.dbo.table2.id=old.dbo.table1.id
..there iare no sign of variables..? We need to see the authentic code.
Or do you mean by variables that they are variables on the client, and when the user has finished his selections, the client builds the SQL and sends that to the server? (which would explain why the example has no @variables
)If this is the case that SQL is built on the client, and you want to know which collation database n has, the client can find out with this query:
select DATABASEPROPERTYEX( 'master' , 'collation' )
...example returns the name of master's collation name.
With that in hand you can build the select statement including the collation name.
Though.. it would be easier if all db's had the same collations (if possible) Mixing collations gives a lot of troubles (as you've noticed
)/Kenneth
-
ok here is the code and i didn't use stored procedure .it is just an sql statement calling it from C#
select *
from " + old + ".dbo.table1
left outer join
" + new + ".dbo.table2
on " + new + " .dbo.table2.id=" + old + ".dbo.table1.id
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply