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

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

Post by Federic » Fri, 02 May 2003 00:16:12



hi,

I have a stored procedure with two tempTables and when i
do a insert on the second temp table i receive this error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
resolve collation conflict for equal to operation.

how can i solve this problem?

thanks

 
 
 

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

Post by BP Margoli » Fri, 02 May 2003 05:03:58


Federico,

You presumably have data with different collations. For example, something like the following:

-- case insensitive, accent insensitive
create table t1 (c1 varchar (2) collate SQL_Latin1_General_CP1_CI_AI  NOT NULL)

-- case sensitive, accent sensitive
create table t2 (c2 varchar (2) collate SQL_Latin1_General_CP1_CS_AS  NOT NULL)
go

insert into t1 values ('ab')
insert into t2 values ('AB')
go

select *
from t1, t2
where t1.c1 = t2.c2

The problem is that SQL Server does not know whether the string comparison should be done using case insensitive, accent insensitive logic, or case sensitive, accent sensitive logic. The results would differ depending upon which collation is chosen. Thus you have to tell SQL Server which collation to be use when doing the string comparison. For example:

select *
from t1, t2
where t1.c1 = t2.c2 collate SQL_Latin1_General_CP1_CI_AI

select *
from t1, t2
where t1.c1 = t2.c2 collate SQL_Latin1_General_CP1_CS_AS

Additionally, since you mention that you are using temp tables, check out the section "Mixed Collation Environments" (architec.chm::/8_ar_da_8uer.htm) in the SQL Server 2000 Books Online. In particular, pay attention to the "database_default" option available when creating temp tables.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> hi,

> I have a stored procedure with two tempTables and when i
> do a insert on the second temp table i receive this error.

> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
> resolve collation conflict for equal to operation.


> how can i solve this problem?

> thanks


 
 
 

1. Cannot resolve collation conflict for equal to operation

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.

2. MSSQL DB-Library error handling

3. Cannot resolve collation conflict for equal to operation.

4. Kill Process : Rollback Transaction

5. ValueList Condition Help

6. Cannot resolve collation conflict for equal to operation

7. Whats wrong with RecSet?

8. Cannot resolve collation conflict for equal to operation.

9. Cannot resolve collation conflict for equal to operation

10. no temp tables! Cannot resolve collation conflict for equal to operation.