Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Angry Unanswered: Collation Errors

    All my tables have the same collation, the groups default collation is the same as all the tables I have copied over but I still get the :

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


    error

    The stored procedure is this:
    declare @enqid int, @count int ,@launchNextPage int

    set @count = (select count(enqid) from TT_EnquiryTable where REPLACE(EnqPhone,' ','') = @phone)
    set @count = @count + (select count(enqid) from TT_ENquiryTable where REPLACE(EnqPhone,' ','') = @phone)
    IF NOT EXISTS(select * from TT_EnquiryTableLocal where enqphone = @phone)

    BEGIN
    IF NOT EXISTS(select * from TT_EnquiryTable where REPLACE(enqphone,' ','') = @phone )
    BEGIN
    insert into traveltime..TT_enquiryTableLocal(enqphone, opcomid)
    values (@phone, @opcomid)
    set @enqid = @@identity
    END
    ELSE
    BEGIN
    insert TT_EnquiryTableLocal(enqphone, opcomid)
    values(@phone, @opcomid)

    set @enqid = @@identity
    update l
    set l.enqentrydate = getdate(),l.enqname= e.enqname, l.enqaddress = e.enqaddress,
    l.enqtown = e.enqtown,l.enqcounty = e.enqcounty, l.enqpostcode = e.enqpostcode,
    l.enqemail = e.enqemail, l.enqcomments = e.enqcomments, l.enqfutureaccept = e.enqfutureaccept,
    l.officeid = e.officeid,l.oldenqid = e.enqid
    from traveltime..TT_enquiryTableLocal l , internet..TT_enquiryTable e
    where REPLACE(e.enqphone,' ','') = @phone and l.enqphone = e.enqphone
    and l.enqid = @enqid


    END
    END
    ELSE
    BEGIN

    IF EXISTS (Select * from TT_enquirytableLocal where enqphone = @phone and sent >= 1 )
    --and datediff(dy,enqentrydate,getdate()) >=1 )
    AND NOT EXISTS (Select * from TT_enquirytableLocal where enqphone = @phone and sent = 0)
    --and datediff(dy,enqentrydate,getdate()) >=1 )
    BEGIN
    INSERT TT_EnquiryTableLocal
    select TOP 1 '',getdate(),enqname,enqaddress,enqtown,enqcounty, enqpostcode,@phone,enqemail,
    enqcomments, enqfutureaccept,officeid, 0 /*sent*/,7,'',@opcomid
    from TT_EnquiryTableLocal
    where enqphone = @phone

    set @enqid = @@identity
    set @count = 0
    set @launchNextPage =0
    END
    ELSE
    BEGIN

    IF EXISTS(select top 1 *
    from TT_EnquiryTable e, TT_enquiryTable l
    where e.enqphone = @phone and l.enqphone = e.enqphone
    and (e.enqphone is not null and e.enqphone <> ''))
    BEGIN
    select top 1 @count as 'count',*
    from TT_EnquiryTable e, TT_enquiryTable l
    where e.enqphone = @phone and l.enqphone = e.enqphone
    and (e.enqphone is not null and e.enqphone <> '')

    order by e.enqid desc

    END
    END
    END
    IF @enqid >=1
    BEGIN
    SELECT @count as 'count',@enqid as 'enqid', * FROM TT_EnquiryTableLocal where enqid = @enqid and sent =0 order by e.enqid desc
    END
    ELSE
    BEGIN
    SELECT TOP 1 @count as 'count',@enqid as 'enqid', * FROM TT_EnquiryTableLocal where enqphone = @phone and sent =0 order by e.enqid desc
    END




    If anybody can help I will be hugely grateful..


    Thanks in advance

    Nathan
    Last edited by NathanFuller; 10-25-05 at 13:17.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What does this give you

    SELECT LTRIM(RTRIM(CONVERT(varchar(255),DATABASEPROPERTYE X('CompDB','Collation'))))
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2005
    Posts
    4

    Unhappy Collation problems

    I ran the code you gave me against each database and it returned

    Latin1_General_CI_AS


    for each database.

    Originally the databases were :
    SQL_Latin1_General_CP1_CI_AS

    the code all worked fine on that SQL server

    but we moved to a new server were the collation on the sql database is set to Latin1_General_CI_AS

    And now we are having all sorts of problems

  4. #4
    Join Date
    Oct 2005
    Posts
    4

    Tables and columns......

    I checked all the databases and they have all been sucessfully changed to Latin1_General_CI_AS

    however when I check the tables and columns within those databases they are still:
    SQL_Latin1_Genaeral_CP1_CI_AS

    Could this be where my problems are stemming from?

    If so is there a way of updating all the tables and columns within to be the the new collation of Latin1_General_CI_AS?

    PLEASE PLEASE HELP!! Tight Deadline on this.

    Nathan

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sorry, but the server was built with a different collation than the standard.

    Your best bet (easiest) is to reinstall sql server with the correct collation.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •