Solved

Collation conflict

Posted on 2004-10-26
3,015 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
Question by:SpencerSteel
    10 Comments
     
    LVL 6

    Expert Comment

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

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    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.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    857 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now