• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3024
  • Last Modified:

Collation conflict

Hello guys,

I have a just totally rebuilt a SQL Server 2000 server and, due to a bit of an oversight, used 'sp_attach' to chop in the database.

I'm getting an error when running a specific SP

"Cannot resolve collation conflict for equal to operation."

This SP uses tempdb and as I understand it from Googling, my problem is caused by a conflict between the tempdb and the SP fields 'collation' (this is all totally new to me)

All the 'standard' databases on my new Server, including tempdb, are LATIN1_General_CI_CA

My database application with the conflicting sp, is SQL_LATIN1_General_CP1_CI_CA

My question is, what is the best way to resolve this.

It's been suggested that perhaps I create a backup, create a new blank database and import the backup.

Thoughts on this, or perhaps an easier solution that doesn't require any 'downtime'.

S.S.
0
SpencerSteel
Asked:
SpencerSteel
1 Solution
 
OlegPCommented:
Try the following:
1)delete blank DB
2) execute retstore statment from QA

RESTORE DATABASE YOUR_DB_NAME
FROM DISK = 'YOUR_DB_BACKUP'
WITH
MOVE 'YOUR_DATA_NAME' TO 'YOUR_PATH_TO_MDF',
MOVE 'YOUR_LOG_NAME' TO 'YOUR_PATH_TO_LDF',
 REPLACE

But Collation of your new db and Server will be differenet
0
 
HilaireCommented:
Hi SpencerStill

Another low-cost option is to modify the stored procedure
The faulty stored procedure most likely uses a #temp table

Collation conflits should not occur if the temp tabel is built using a select ...into #temp from ...
Collation conflit will occur if you explicitly create a temp table.
It will then use the collation settings from the tempdb

The first way to get rid of the problem is to explicitly create the temp table using your new db's collation settings

create table #temp (id int identity,
       customer_name varchar(200) SQL_LATIN1_General_CP1_CI_CA,
       customer_gender varchar(10) SQL_LATIN1_General_CP1_CI_CA,
       and so on for each and every varchar/char/nvarchar/nchar field/column
)

Another way is to force collation in the queries that join the #temp table and columns of your new DB.

select * from #temp a inner join tblCustomers b
on a.customer_name = b.customer_name COLLATE SQL_LATIN1_General_CP1_CI_CA
where ....

The bad thing is that it somehow hard-codes the collation rules.
The good thing is that you don't even have to put the DB offline.

HTH

Hilaire
0
 
SpencerSteelAuthor Commented:
Thanks guys

I ideally would like to fix this in the shortterm using the above and fix the whole database in the long term so they are all the same.

Here is the temp table creation (I didn't write this SP) ... perhaps Hilaire would like to correct this DECLARE statement ?

DECLARE @Temp TABLE (SentCount int, SentUser varchar(50))

INSERT INTO @Temp (SentCount, SentUser)
SELECT Count(TblSent.SentID) AS CountOfSentID ............

Many, many thanks

S.S.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
HilaireCommented:
try

DECLARE @Temp TABLE (SentCount int, SentUser varchar(50) QL_LATIN1_General_CP1_CI_CA)

INSERT INTO @Temp (SentCount, SentUser)
SELECT Count(TblSent.SentID) AS CountOfSentID ............

If it does not work like this, please post the whole SP code
0
 
SpencerSteelAuthor Commented:
That's what I tried, the declare statement doesn't parse ... i've attempted to do some looking up on this and I can't see an example for using COLLATE .. with DECLARE, although there are examples with CREATE

However, all the examples say to use the COLLATE statement, thus

DECLARE @Temp TABLE (SentCount int, SentUser varchar(50) COLLATE SQL_LATIN1_General_CP1_CI_CA)

...but that doesn't parse either.

The SP is too big to paste really .. but heres that entire block

DECLARE @Temp TABLE (SentCount int, SentUser varchar(50) COLLATE )

INSERT INTO @Temp (SentCount, SentUser)
SELECT Count(TblSent.SentID) AS CountOfSentID, TblSent.SentBy
FROM TblSent INNER JOIN TblSentivStatusHistory ON TblSent.SentID = TblSentivStatusHistory.SentID
WHERE TblSentivStatusHistory.ivStatus='1st I.V.'  AND  TblSentivStatusHistory.DateAdded > @myDate
GROUP BY TblSent.SentBy



... Dont worry too much if we can't fix this - i'm think i'm going to have to go for a clean export/import tonight. *sigh*

I would love to know how it got in this state in the first place :)

Thanks again,

S.S.
0
 
HilaireCommented:
This syntax works with any valid collation

DECLARE @Temp TABLE (SentCount int, SentUser varchar(50) COLLATE SQL_LATIN1_General_CP1_CI_AS)

Are you sure of your collation name ?
I just tried
SELECT *
FROM ::fn_helpcollations()
where name like '%latin%'

on my sql box, and I don't get SQL_Latin1_General_CP1_CI_CA
I only get
SQL_Latin1_General_CP1_CI_AI
SQL_Latin1_General_CP1_CI_AS
SQL_Latin1_General_CP1_CS_AS
0
 
SpencerSteelAuthor Commented:
Doh !

I am an idiot and you are totally correct ... sorry ... I mistype what was written ... that has solved that problem perfectly - thanks so much !

However, just before you go ... I think it would be a good idea to recreat the whole database in the Latin1 default, which Oleg was saying, but - and at the risk of sounding a bit rude here - I didn't quite get what he was getting at ... could you clarify ... I'm needing to know the easier way of recreating this whole database wit the standard collation. None of my columns use collation in the tables explicitly, so I think I can DTS it as one. Not sure.

Thanks again for all your help so far - the quick n' dirty method has worked.

S.S.
0
 
HilaireCommented:
AFAIK, there's no magic solution to change collation of an existing DB.

You could easily make a script that loops through table columns in information_schema views and changes collation for each and every column of char/varchar/nchar/nvarchar datatype.

alter table <tablename> alter column <columnname>....

see sample script below

declare @collatefrom      varchar(100), @collateto varchar(100)
set @collateto = 'Latin1_General_CP1_CI_AS'
set @collatefrom = 'SQL_Latin1_General_CP1_CI_AS'

declare C1 cursor for
select table_name, column_name,
data_type + '(' + cast(character_maximum_length as varchar(4)) + ')' as datatype,
collation_name, case is_nullable when 'No' then ' NOT NULL' else ' NULL' end as str_null
from information_schema.columns
where data_type in ('varchar', 'char', 'nvarchar', 'nchar')
and collation_name = @collatefrom

--declaration des variables réceptrices du curseur
declare @table_name varchar(255),
      @column_name varchar(255),
      @datatype      varchar(20),
      @collation varchar(255),
      @strnull varchar(10)
open c1
fetch next from c1 into @table_name ,@column_name, @datatype, @collation, @strnull
while @@fetch_status = 0
begin
      exec ('alter table ' + @table_name + ' alter column ' + @column_name + ' ' + @datatype + ' collate ' + @collateto + @strnull)
      fetch next from c1 into @table_name ,@column_name, @datatype, @length, @collation, @strnull
end
close C1
deallocate C1

unfortunately this will fail if you have either indexes on these columns or foreign key referencing these columns ....
(if it works you'll have to change the default collation - for new tables - of this DB in Enterprise Manager or with an alter database statement)

If it doesn't work, you could script the DB out completely, do a search-and-replace to change the collation, create a new DB with this script and then import the data with DTS.

Maybe there are better ways to do that, here's what I can think of for the moment (I'm not a real DBA, just a developper/architect with minor admin skills.

Maybe arbert or ScottPletcher could come with better ways to do that...

HTH

Hilaire
0
 
SpencerSteelAuthor Commented:
OK, I think i'll have to try and create a new blank database and bring in the old one somehow, without porting in the COLLATE settings.

I'll have a play and let you know.

I'll give you the points as you got me out of this hole.

Thanks

S.S.
0
 
jjozCommented:
yes I also wondering if there is a way to convert from: SQL_Latin1_General_CP1_CI_AS into: Latin1_General_CP1_CI_AS
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now