Cannot resolve collation conflict for equal to operation.

I've stumbled across the above error and am a little stuck.

I've just installed SQL2000 (sp3) on my PC and restored a database from
our Live server. On a simple Update statement on a #temp table, it
fails with the above message. I think I understand what it means and
found some old posts suggesting using the following :

select name,  databasepropertyex(n
ame, 'collation')
from master..sysdatabases
select  serverproperty('coll
ation')

All of the databases that are there by default are set to
 'Latin1_General_CI_A
S' and the restored db is
 'SQL_Latin1_General_
CP1_CI_AS'.

The live server has all of these set to the 'SQL...' version, but a
standard install points to the other. So, how do I change mine to the
'newer' setting ? All I need to do is mimic the live environment for
testing and development. There is only me using it, and it's not a
problem to bin it and re-install, or tweak if I need to.

I've tried using :

ALTER DATABASE Northwind COLLATE  SQL_Latin1_General_C
P1_CI_AS

as a test (thought this was the best example to show), but it fails
stating the following :

Server: Msg 5075, Level 16, State 1, Line 5
The object  'CK_Products_UnitPri
ce' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_ReorderLevel' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_UnitsInStock' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_UnitsOnOrder' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_Discount' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_Quantity' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_UnitPrice' is dependent on database collation.
Server: Msg 5075, Level 16, State 1, Line 5
The object 'CK_Birthdate' is dependent on database collation.
Server: Msg 5072, Level 16, State 1, Line 5
ALTER DATABASE failed. The default collation of database 'Northwind'
cannot be set to  SQL_Latin1_General_C
P1_CI_AS.

On the Live server, the Northwind database is set to the 'SQL...'
version, so it MUST be do-able somehow.

Any pointers would be appreciated.

Thanks in advance


Ryan

Hello, Ryan

In SQL Server, collations can be configured at several levels:
- the default collation for the entire server (instance)
- the default collation of the database
- the collation of the column in some table

I understand that you get the "Cannot resolve collation conflict for
equal to operation" message when you try to compare (by joining, for
example) a column from a temporary table to a column from a normal
table. In this, case the problem is caused by the fact that temporary
tables are stored in the tempdb database, so (unless you explicitly
specify a collation) they get the default collation of the tempdb
database, which is the default collation of the server.

To solve this problem without changing any code, you need to change the
default collation of the server to match the default collation of your
restored database (which is also the default collation of the live
server). In order to change the default collation of the server, you
need to "rebuild the master database" or reinstall SQL Server. See:
http://msdn.microsoft.com/library/e..._ar_da_3zw3.asp
http://msdn.microsoft.com/library/e...buildm_6dbt.asp

Razvan

I've looked further into this and tried re-installing SQL2000. Nothing
was any different, and nor was there any option to change the
collation. Now from what I understand, the SQL_ collation was the
default in SQL7 and this then changed in SQL2000. Our server shows SQL_
so I assume it would have been upgraded to SQL2000 at some point
(although I'm told otherwise). We don't have a copy of SQL 7 (which
backs up the none upgrading).

I was thinking of installing 7, then upgrading as this 'should' do the
trick. However, without a copy of SQL7 I'm stuffed. I can't change the
server, so can't figure what the next step would be. All I'm interested
in is matching the server. If I can't do that, then I can't work.

Even if I get a new server, I expect this will give me the same
problems.

I tried using RebuildM.exe and this doesn't give me the option. Is
there any way of adding this ?

Any further ideas ?

Thanks


Ryan

Hi, Ryan

When you install SQL Server, make sure that you choose "Custom" in the
"Setup Type" screen, because the "Collation Settings" screen will not
appear for a "Minimal" or "Typical" installation. In the "Collation
Settings" screen, choose the "Dictionary order, case-insensitive, for
use with 1252 Character Set" SQL Collation.

Razvan

That looks spot on. Thanks for your help.

Ryan

Except that it doesn't fully work now........

All of the collations are set the same  (SQL_Latin1_General_
CP1_CI_AS)
and the error I originally got is now showing. It appears on the
following update statement :

UPDATE #Extract SET
PD1 = (SELECT DV.FIELD_VALUE
FROM
 DEALER_SOURCE_DATA_V
ALUES DV WITH (NOLOCK),
#Max M
WHERE
DV. DEALER_SOURCE_DATA_I
D = #Extract. DEALER_SOURCE_DATA_I
D AND
DV.FIELD_CODE = #Extract.Line_No AND  -- IT APPEARS ON THIS LINE
AS EXCLUDING IT WORKS, BUT FAILS THE LOGIC NEEDED
#Extract.DSD_YEAR = M.MaxYear AND
#Extract.DSD_MONTH = 1 AND
DV.FIELD_VALUE <> 0.00000)

Server: Msg 446, Level 16, State 9, Line 5
Cannot resolve collation conflict for equal to operation.

Columns used (to help explain what I've done)
------------------------------------------------------------------
[Line_No] VarChar(75) in #Extract
[FIELD_CODE] VarChar(10) in  DEALER_SOURCE_DATA_V
ALUES

These should still match and not throw the error I would have
thought......

I've tried the alter database statement just in case it helped, but it
didn't make any difference (as expected).

Maybe I'm just having a bad day, and need a fresh think tomorrow.

Ryan

Hi, Ryan

Check the collations of the columns:

EXEC sp_help  'DEALER_SOURCE_DATA_
VALUES'
use tempdb
EXEC sp_help '#Extract'

Razvan

Sorted, looks like I must have selected case sensitive by mistake.
Re-installed and it all works a treat.

Ta

Ryan