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(name, 'collation')
from master..sysdatabases
select serverproperty('collation')
All of the databases that are there by default are set to
'Latin1_General_CI_AS' 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_CP1_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_UnitPrice' 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_CP1_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
7  85099 
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/en...ar_da_3zw3.asp http://msdn.microsoft.com/library/en...uildm_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_VALUES DV WITH (NOLOCK),
#Max M
WHERE
DV.DEALER_SOURCE_DATA_ID = #Extract.DEALER_SOURCE_DATA_ID 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_VALUES
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Bob Bedford |
last post by:
I've no access to mysql NG, so I ask here:
I've two identical tables (wich are created by usins the same script, but
must be separated).
When I execute a query wich return fields and do a...
|
by: Chris |
last post by:
Has anyone ever accomplished a mass update off all databases, tables and
columns for collation?
If I try to change the collation/character set for the mysql daemon it
breaks all of our queries...
|
by: John Jayaseelan |
last post by:
Hi,
The tempdb db is having different collation than the application db.
I rebuilt the master db with the appropriate collation after backing up
master, model, msdb, appln databases.
On...
|
by: pemigh |
last post by:
I experienced the same problem that I found posted from summer 2005
(apparently too long ago to continue the thread). See below for full
description.
I found that reinstalling Access 2000 did...
|
by: Peter Nurse |
last post by:
I have just upgraded to SQL Server 2005 from SQL Server 2000.
In Microsoft SQL Server Management Studio, when I click on database
properties, I receive the following error:-
Cannot resolve the...
|
by: Carl Gilbert |
last post by:
Hi
I am trying to get a simple ASP.NET web application to work on my 1&1
(1and1) web space. I managed to get something working a while ago, with
thanks to Karl Seguin, but for some unknown...
|
by: Frederick Darko |
last post by:
Please I Have This Server Running Pefectely Till This Morning An Error Displayed : Server Source Collation Conflict On Equal. This Was Refering To A Particular Table But It Seems That Table Is Not In...
|
by: Yas |
last post by:
Hello,
I currently have Table1 and View1.
View1 is a query from 2 or 3 tables that works fine on its own.
However in my current query if I try to use it...something like...
SELECT a.col1,...
|
by: Uncle Dickie |
last post by:
Hi All,
I am getting the error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
when I run a query I have...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
