Cannot resolve collation conflict for equal to operation

Cannot resolve collation conflict for equal to operation

Post by Stijn Verrep » Wed, 18 Feb 2004 14:41:20



I get this error on the following line:


The #PayList table looks like this:

CREATE TABLE #PayList
(
PL_ID [smallint] IDENTITY (1, 1) NOT NULL ,
PL_COName varchar(50),
PL_Desc varchar(100),
PL_Remarks varchar(200),
PL_PlannedDate SmallDateTime,
PL_Amount Numeric(9,2),
PL_PocketAmount Numeric(9,2),
PL_Qty SmallInt,
PL_SNID int,
PL_IsAdvance bit,
PL_Income bit,
PL_IsMedic bit,
PL_IsHousing bit,
PL_Category varchar(50),
PL_SubCat varchar(50)
)

I looked in google groups and this error mostly applies when using
different servers, but this is on the same server.  And it's a very
straightforward select statement.

What would be wrong?

--
Kind regards,

Stijn Verrept.

 
 
 

Cannot resolve collation conflict for equal to operation

Post by Jacco Schalkwij » Wed, 18 Feb 2004 15:13:35


Hi Stijn,

This collation conflict can only occur when comparing character datatypes
with different collations, not with integers as they do not have a
collation.

The collation conflict can happen on one server though. It is most common
when a database is created with a different collation form the server
(although technically you only need one column with a different collation
form the server default). What happens is usually is that a comparison is
made between a column in a permanent table (which has the database
collation) and a column in a temporary table (which will be created with the
server collation, unless you explicitly specify a colaltion or COLLATE
DATABASE DEFAULT). This comparison between two columns with a different
collation will fail with the error in the title of your post.

But as I said this error only occurs when the data types involved are
character datatypes, and you have integers, so the error might actually be
in a different line of code.

--
Jacco Schalkwijk
SQL Server MVP


> I get this error on the following line:



> The #PayList table looks like this:

> CREATE TABLE #PayList
> (
> PL_ID [smallint] IDENTITY (1, 1) NOT NULL ,
> PL_COName varchar(50),
> PL_Desc varchar(100),
> PL_Remarks varchar(200),
> PL_PlannedDate SmallDateTime,
> PL_Amount Numeric(9,2),
> PL_PocketAmount Numeric(9,2),
> PL_Qty SmallInt,
> PL_SNID int,
> PL_IsAdvance bit,
> PL_Income bit,
> PL_IsMedic bit,
> PL_IsHousing bit,
> PL_Category varchar(50),
> PL_SubCat varchar(50)
> )


> I looked in google groups and this error mostly applies when using
> different servers, but this is on the same server.  And it's a very
> straightforward select statement.

> What would be wrong?

> --
> Kind regards,

> Stijn Verrept.


 
 
 

Cannot resolve collation conflict for equal to operation

Post by Tibor Karasz » Wed, 18 Feb 2004 15:14:55


The temp table is created in tempdb and hence gets tempdb's collation. The
message generally creeps up when you do, say, a join between a tamp table
and one in your connection's database and they have different collations. In
this case, I would imagine that the variables created and compared against
could case this. However, they seem to be integer datatypes. Perhaps if you
create a repro, we could see what is happening?

--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> I get this error on the following line:



> The #PayList table looks like this:

> CREATE TABLE #PayList
> (
> PL_ID [smallint] IDENTITY (1, 1) NOT NULL ,
> PL_COName varchar(50),
> PL_Desc varchar(100),
> PL_Remarks varchar(200),
> PL_PlannedDate SmallDateTime,
> PL_Amount Numeric(9,2),
> PL_PocketAmount Numeric(9,2),
> PL_Qty SmallInt,
> PL_SNID int,
> PL_IsAdvance bit,
> PL_Income bit,
> PL_IsMedic bit,
> PL_IsHousing bit,
> PL_Category varchar(50),
> PL_SubCat varchar(50)
> )


> I looked in google groups and this error mostly applies when using
> different servers, but this is on the same server.  And it's a very
> straightforward select statement.

> What would be wrong?

> --
> Kind regards,

> Stijn Verrept.

 
 
 

Cannot resolve collation conflict for equal to operation

Post by Stijn Verrep » Wed, 18 Feb 2004 17:11:20



> Hi Stijn,

> This collation conflict can only occur when comparing character
> datatypes with different collations, not with integers as they do not
> have a collation.

Thanks for the reply. That's what I thought as well, but the SP gives
me this error when executing that line (while debugging the SP in query
analyser).  It does name another line where the error happens, so I'll
check that line out as well.

--
Kind regards,

Stijn Verrept.

 
 
 

Cannot resolve collation conflict for equal to operation

Post by Stijn Verrep » Wed, 18 Feb 2004 17:25:03


Ok I found the error :).  I added COLLATE database_default to the
columns of the temp table :)
Thanks everyone for the replies.

--
Kind regards,

Stijn Verrept.

 
 
 

1. Cannot resolve collation conflict for equal to operation.

I am getting this error message when performing this simple query:

select t1.member_number, t2.customer
from table1 t1, table 2 t2
where t1.member_number = t2.customer

The data originates from an informix database that I have brought over
via odbc. I have not had this problem before and do not understand why
I should have this problem now. These tables reside in the same
database, thus, there should not be collation issues.

Any ideas?

Thanks

Si

2. ADO and Non Pentium Pro Processors

3. Succeeded or Failed?

4. Cannot resolve collation conflict for equal to operation

5. pgsql-server/src/backend executor/nodeAgg.c ut ...

6. Personal Oracle + Oracle SQL Net

7. Cannot resolve collation conflict for equal to operation.

8. Cannot resolve collation conflict for equal to operation

9. resolve collation conflict for equal to operation?????

10. Cannot resolve collation conflict for %ls operation.????