Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Collation conflict

Posted on 2004-10-26
10
Medium Priority
?
3,021 Views
Last Modified: 2008-01-09
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
Comment
Question by:SpencerSteel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 6

Expert Comment

by:OlegP
ID: 12408660
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12408877
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
 

Author Comment

by:SpencerSteel
ID: 12408913
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:Hilaire
ID: 12408939
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
 

Author Comment

by:SpencerSteel
ID: 12409035
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
 
LVL 26

Accepted Solution

by:
Hilaire earned 1500 total points
ID: 12409135
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
 

Author Comment

by:SpencerSteel
ID: 12409186
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12409748
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
 

Author Comment

by:SpencerSteel
ID: 12410289
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
 
LVL 1

Expert Comment

by:jjoz
ID: 34315931
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question