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'
> >
> >
>

Collapse cell if empty and

Whoops. Sent too quick.
=Iif(IsNothing(Fields!ADDRESS3.Value),True,False)
Part 2 of question. I've got city, state, and zip columns evenly spaced in
a table. How do I get the columns to collapse to the width of their data?
"Colin" <legendsfan@.nospam.nospam> wrote in message news:...
> I'm creating a Reporting Services form within VS 2003. I can't figure out
> how to collapse a cell if there is no data returned. As an example I've
> got three lines for an Address field but most only use two lines. How can
> I collapse the third cell on my form so city, state, and zip will be next
> to last line of address? I figure I need to use an "expression" but this
> doesn't seem to be working. I applied this under visibility.
>Use something like this expression =IIF( Fields!STREET2.Value =nothing,True,False) as the Hidden property value under Visibility for
the row or cell you want to conditionally hide.
As for your part 2, I don't know the answer to that. SRS allows for
increased/decreased height based on data but not width. If anybody has
a fix for this, I'd like to see that too.|||How do I put static data in a field with something dynamically generated? I
tried "Street Name:" & =IIF( Fields!STREET2.Value => nothing,True,False)
That didn't work.
This will hide the field but it doesn't collapse it or make it not show on
the page. I have four rows in a table for the address field but usually
only 3 are used. This is what happens when the third row is nothing. Under
Willow there is a blank space because this field rarely has data associated.
You can also see the spacing problem between city and state.
Target Stores
TARGET #1167
2241 WILLOW
GLENVIEW IL 60025
"toolman" <toolman_2000@.yahoo.com> wrote in message
news:1139939869.370244.104680@.o13g2000cwo.googlegroups.com...
> Use something like this expression =IIF( Fields!STREET2.Value => nothing,True,False) as the Hidden property value under Visibility for
> the row or cell you want to conditionally hide.
> As for your part 2, I don't know the answer to that. SRS allows for
> increased/decreased height based on data but not width. If anybody has
> a fix for this, I'd like to see that too.
>|||Hello,
To understand the issue better, will you provide a simple sample of the
report with backend data?
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Colin" <legendsfan@.nospam.nospam>
>References: <eHxiKZOMGHA.2416@.TK2MSFTNGP15.phx.gbl>
<1139939869.370244.104680@.o13g2000cwo.googlegroups.com>
>Subject: Re: Collapse cell if empty and
>Date: Tue, 14 Feb 2006 14:37:02 -0800
>Lines: 29
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#c0tfbbMGHA.2124@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: mail.topics-ent.com 64.122.1.82
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68725
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>How do I put static data in a field with something dynamically generated?
I
>tried "Street Name:" & =IIF( Fields!STREET2.Value =>> nothing,True,False)
>That didn't work.
>This will hide the field but it doesn't collapse it or make it not show on
>the page. I have four rows in a table for the address field but usually
>only 3 are used. This is what happens when the third row is nothing.
Under
>Willow there is a blank space because this field rarely has data
associated.
>You can also see the spacing problem between city and state.
> Target Stores
> TARGET #1167
> 2241 WILLOW
> GLENVIEW IL 60025
>"toolman" <toolman_2000@.yahoo.com> wrote in message
>news:1139939869.370244.104680@.o13g2000cwo.googlegroups.com...
>> Use something like this expression =IIF( Fields!STREET2.Value =>> nothing,True,False) as the Hidden property value under Visibility for
>> the row or cell you want to conditionally hide.
>> As for your part 2, I don't know the answer to that. SRS allows for
>> increased/decreased height based on data but not width. If anybody has
>> a fix for this, I'd like to see that too.
>
>|||Are you using the =IIF( Fields!STREET2.Value = > nothing,True,False)
expression in the property for the entire row or just a cell in the
row? If not, try that. It should work for you. I have a couple
reports using it and it always draws the city, state zip row up to the
last visible address row.
The expression for Street: Fields!Street.value would be
="Street: " & Field!Street.Value
If Fields!Street.Value is Nothing you'll get 'Street: ' otherwise
you'll get 'Street: 123 Street Value' (without the quotes)|||Thanks toolman. I was setting the hidden value in the cell instead of row.
"toolman" <toolman_2000@.yahoo.com> wrote in message
news:1140038132.121332.42660@.g47g2000cwa.googlegroups.com...
> Are you using the =IIF( Fields!STREET2.Value = > nothing,True,False)
> expression in the property for the entire row or just a cell in the
> row? If not, try that. It should work for you. I have a couple
> reports using it and it always draws the city, state zip row up to the
> last visible address row.
> The expression for Street: Fields!Street.value would be
> ="Street: " & Field!Street.Value
> If Fields!Street.Value is Nothing you'll get 'Street: ' otherwise
> you'll get 'Street: 123 Street Value' (without the quotes)
>