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

NathanWhat does this give you

SELECT LTRIM(RTRIM(CONVERT(varchar(255),DATABASEPROPERTYE X('CompDB','Collation'))))|||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|||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|||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.

collation error for patindex (sql 2000)

Hi, what do I do with this error please:
Server: Msg 446, Level 16, State 9, Line 3
Cannot resolve collation conflict for patindex operation

Here is the script in question.

use mosaikDB737
exec sp_MSforeachDB
'
use [?]
insert into mosaikDB737.dbo.SearchOutput2 select sk.loginname as searchedTxt, object_name(id) AS ProcName ,Len(SubString(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.loginname))+ ''%'', o.text)))-Len(Replace(SubString(o.text,1, PatIndex
(''%'' + ltrim(rtrim(sk.loginname)) + ''%'', o.text)),char(13),''''))+1 AS Line,
PatIndex(''%'' + ltrim(rtrim(sk.loginname)) + ''%'', o.text) AS Position, ''[?]'' as dbName
from syscomments as o inner join mosaikDB737.dbo.loginListInput as sk on o.text like ''%'' + ltrim(rtrim(sk.loginname)) + ''%''
ORDER BY searchedTxt,ProcName, Line, position'
select * from mosaikDB737.dbo.SearchOutput2

Thanks a lot

Check collation of loginname and text columns. Depending on your installation / configuration you may have to modify one or the other to the corresponding collation using the COLLATE clause. See the COLLATE topic in Books Online for more details on collations, how they are used in string comparisons etc.|||they re different in some databases man. Can I cast loginname to the collation of o.text on the fly dynamically in my script?|||You can't use COLLATE clause dynamically. So you will have to cast both columns to a common collation and then do the comparison. Of course, this means that you will have to pick a collation that will work with your data otherwise you will get incorrect results.|||

that s the pb man. as u can see. using "?" my script goes through all the databases. So if one of the databases has a different collation for the column syscomments.text than the one i will choose then all my script might fail.

|||As I suggested, you change each column that you are comparing to a common collation. This may or may not work depending on the data.|||That was perfect|||

Umachandar Jayachandran - MS wrote:

Check collation of loginname and text columns. Depending on your installation / configuration you may have to modify one or the other to the corresponding collation using the COLLATE clause.

P.S: First I want to let you know that the collation issue has been solved thanks to your help.

I read Collate in BOL but i m still not sure how that works exactly for my script.

for loginname column I have control over it since it s in a database that I create my self. But as you can see text column represents the text for all the SPs of all the databases in the instance.

After checking we found that the text column is 29 times of collation Latin1_General_BIN and 13 times of collation SQL_Latin1_General_CP1_CI_AS.

So my question pls is this:

as you can see that in my script I have the columns sk.LoginName and o.text many times. If I choose to cast both columns to the collation SQL_Latin1_General_CP1_CI_AS. Will I have to put your suggestion COLLATE SQL_Latin1_General_CP1_CI_AS after each occurence of those 2 columns in my script or just in one occurence.

Tell me please if this is correct. I put your suggestion in all the loginname and text occurences in the script (I mean whenever there is loginname or o.text in the script I put COLLATE SQL_Latin1_General_CP1_CI_AS ) as in teh following:

use mosaikDB737
exec sp_MSforeachdb
'
use [?]
insert into mosaikDB737.dbo.SearchOutput1 select sk.LoginName COLLATE

SQL_Latin1_General_CP1_CI_AS as searchedTxt , object_name(id) AS ProcName ,Len(SubString

(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.LoginName COLLATE

SQL_Latin1_General_CP1_CI_AS))+ ''%'', o.text)))-Len(Replace(SubString(o.text,1, PatIndex
(''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'', o.text

COLLATE SQL_Latin1_General_CP1_CI_AS)),char(13),''''))+1 AS Line,
PatIndex(''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'',

o.text COLLATE SQL_Latin1_General_CP1_CI_AS) AS Position, ''[?]'' as dbName
from syscomments as o inner join mosaikDB737.dbo.LoginListInput as sk on o.text like

''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%''
ORDER BY searchedTxt,ProcName, Line, Position'
select * from mosaikDB737.dbo.SearchOutput1 order by dbName, ProcName

I just chose to cast every thing to the collation SQL_Latin1_General_CP1_CI_AS. I hope that s Ok.

Muchas Gracias

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'
Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>
|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>
|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
>

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
>

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> > Why do i get the error below:
> >
> > Server: Msg 446, Level 16, State 9, Line 1
> > Cannot resolve collation conflict for equal to operation.
> >
> > What should i do?
> >
> >
> >
> >
> >
> > INSERT INTO tkl_proposals(
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, transfer_date, from_server)
> > SELECT
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, getdate(), 'KIRMIZI'
> > FROM MD_F.MidasLocal.dbo.tkl_proposals b
> > WHERE
> > (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> > b.proposal_id=a.proposal_id AND
> > b.oc_initial=a.oc_initial AND
> > b.oc_place=a.oc_place)
> > )
> > AND b.oc_initial='MD'
> > AND b.oc_place='F'
> > AND b.proposal_date >= '1-1-2005'
> >
> >
>