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

Collation Conflict with Snapshot Rep

I'm trying to setup replication from an external web server to internal sql
box. The internal box with do a pull subscription to external webbox. I ran
snapshot and all works successfully. As soon as I try to run Pull
subscription, I get an error a Collation Conflict on a table. I tweaked the
settings and copy the collation to destination in the publication properties
tab. This runs but gives me the following:
Error Message: Cannot resolve collation conflict for equal to operation.
Error Detail: Cannot resolve collation conflict for equal to operation.
(Source: WIN2K3 (Data source); Error number: 446)
ANy ideas on how to correct this?
Thanks in Advance
Rob
Is there anything odd about the particular table you are having a problem
with? Could you post its schema here? What sort of content is inside this
table?
Also it sounds like you are doing filtering. Are you?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:18B8B552-CC29-4F84-B073-34FF3FF6BBED@.microsoft.com...
> I'm trying to setup replication from an external web server to internal
> sql
> box. The internal box with do a pull subscription to external webbox. I
> ran
> snapshot and all works successfully. As soon as I try to run Pull
> subscription, I get an error a Collation Conflict on a table. I tweaked
> the
> settings and copy the collation to destination in the publication
> properties
> tab. This runs but gives me the following:
> Error Message: Cannot resolve collation conflict for equal to operation.
> Error Detail: Cannot resolve collation conflict for equal to operation.
> (Source: WIN2K3 (Data source); Error number: 446)
> ANy ideas on how to correct this?
> Thanks in Advance
> Rob
>
|||No filtering, straight snapshot. Nothing odd about the table or its data.
Its a third
party program so I cannot change the table structure.
Heres the table setup.
CREATE TABLE [TABLE1] (
[nm_entity] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[no_next_internal_id] [int] NOT NULL ,
[no_next_parameter_id] [int] NULL ,
[no_next_code_suffix] [int] NULL ,
CONSTRAINT [Table1_PK] PRIMARY KEY CLUSTERED
(
[nm_entity]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
When SQL creates snapshot this is the table structure on the
pull subscriber:
CREATE TABLE [THINQ_ID_MANAGER] (
[nm_entity] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[no_next_internal_id] [int] NOT NULL ,
[no_next_parameter_id] [int] NULL ,
[no_next_code_suffix] [int] NULL
) ON [PRIMARY]
GO
thanks
rob
"Hilary Cotter" wrote:

> Is there anything odd about the particular table you are having a problem
> with? Could you post its schema here? What sort of content is inside this
> table?
> Also it sounds like you are doing filtering. Are you?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:18B8B552-CC29-4F84-B073-34FF3FF6BBED@.microsoft.com...
>
>
|||What is the default collation sequence on your publisher and the default
collation sequence on your subscriber?
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:4B47392D-0477-4842-9173-762D00AE5267@.microsoft.com...[vbcol=seagreen]
> No filtering, straight snapshot. Nothing odd about the table or its
> data.
> Its a third
> party program so I cannot change the table structure.
> Heres the table setup.
> CREATE TABLE [TABLE1] (
> [nm_entity] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
> [no_next_internal_id] [int] NOT NULL ,
> [no_next_parameter_id] [int] NULL ,
> [no_next_code_suffix] [int] NULL ,
> CONSTRAINT [Table1_PK] PRIMARY KEY CLUSTERED
> (
> [nm_entity]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> When SQL creates snapshot this is the table structure on the
> pull subscriber:
> CREATE TABLE [THINQ_ID_MANAGER] (
> [nm_entity] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
> [no_next_internal_id] [int] NOT NULL ,
> [no_next_parameter_id] [int] NULL ,
> [no_next_code_suffix] [int] NULL
> ) ON [PRIMARY]
> GO
> thanks
> rob
>
>
> "Hilary Cotter" wrote:

collation conflict resolution

I have a number of databases with quite similar structure, but with
different COLLATION
settings. Unfortunately when the structure (including keys and constraints)
has been created
no one took care of COLLATION at all.
At the moment statements like:
--
SELECT
. . .
(case FieldA
when 1 then (select Source1 from Table1 WHERE ...)
when 2 then (select Source2 from Table2 WHERE ...)
end) as Result,
--
gives COLLATION CONFLICT because Table1 and Table2 have different collation
setting.
But for a half of number of tables it works fine.
What aa I asking about: How to change COLLATION SETTING "on fly" for
different tables.fields ?
May be someone knows any fancy tools or "script wizards" to do so?
Please don't waste this newsgroup space with offering me to insert "COLLATE
DATABASE_DEFAULT"
to each statement which causes such problem. Ok?
Thanks a lot!
GnumHi,
CREATE VIEW FROM Table2
ADD COLLATE AFTER EACH FIELDS.
> when 1 then (select Source1 from Table1 WHERE ...)
(select Source1 from View1 WHERE ...)
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2004
"Gnum" <no-email@.mail.boo> wrote in message
news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> I have a number of databases with quite similar structure, but with
> different COLLATION
> settings. Unfortunately when the structure (including keys and
constraints)
> has been created
> no one took care of COLLATION at all.
> At the moment statements like:
> --
> SELECT
> . . .
> (case FieldA
> when 1 then (select Source1 from Table1 WHERE ...)
> when 2 then (select Source2 from Table2 WHERE ...)
> end) as Result,
> --
> gives COLLATION CONFLICT because Table1 and Table2 have different
collation
> setting.
> But for a half of number of tables it works fine.
> What aa I asking about: How to change COLLATION SETTING "on fly" for
> different tables.fields ?
> May be someone knows any fancy tools or "script wizards" to do so?
> Please don't waste this newsgroup space with offering me to insert
"COLLATE
> DATABASE_DEFAULT"
> to each statement which causes such problem. Ok?
>
> Thanks a lot!
> Gnum
>|||Thank you very much for so big
MISUNDERSTANDING!
Have you ever read the question up to the end?
I've written there "
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?"
I need a help, but not a collection of stupid wisdom-like erudition esseys!
Thanks!
"Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote in message
news:OOcY6TlvDHA.2880@.tk2msftngp13.phx.gbl...
> Hi,
> CREATE VIEW FROM Table2
> ADD COLLATE AFTER EACH FIELDS.
> > when 1 then (select Source1 from Table1 WHERE ...)
> (select Source1 from View1 WHERE ...)
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2004
> "Gnum" <no-email@.mail.boo> wrote in message
> news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> > I have a number of databases with quite similar structure, but with
> > different COLLATION
> > settings. Unfortunately when the structure (including keys and
> constraints)
> > has been created
> > no one took care of COLLATION at all.
> > At the moment statements like:
> > --
> > SELECT
> > . . .
> > (case FieldA
> > when 1 then (select Source1 from Table1 WHERE ...)
> > when 2 then (select Source2 from Table2 WHERE ...)
> > end) as Result,
> > --
> > gives COLLATION CONFLICT because Table1 and Table2 have different
> collation
> > setting.
> > But for a half of number of tables it works fine.
> >
> > What aa I asking about: How to change COLLATION SETTING "on fly" for
> > different tables.fields ?
> > May be someone knows any fancy tools or "script wizards" to do so?
> >
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?
> >
> >
> > Thanks a lot!
> >
> > Gnum
> >
> >
>

Collation Conflict on sysname? Really?

Okely dokely, here it is.

I have a database that has a differing collation to that of the tempdb. And obviously because of this I've run into problems when referencing table variables and temp tables.

BUT! Given the following example, what am I doing wrong, or is there no solution to this.

<Start Example>

/*================================================= ================================================== ==========================
fnPM_ForeignKey
================================================== ================================================== =========================*/

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnPM_ForeignKey]')) BEGIN
DROP FUNCTION [dbo].[fnPM_ForeignKey]
END
GO

CREATE FUNCTION fnPM_ForeignKey (@.ChildTable as sysname, @.ChildColumn as sysname)

RETURNS @.ForeignKey TABLE (
FKName sysname,
ParentTable sysname,
ParentColumn sysname,
DescriptionColumn sysname COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN

INSERT INTO @.ForeignKey (FKName, ParentTable, ParentColumn)
SELECT FK.Name, Parent.Name, ParentCol.Name
FROM sysforeignkeys
INNER JOIN sysobjects FK on sysforeignkeys.constid = FK.ID
INNER JOIN sysobjects Parent on sysforeignkeys.rkeyid = Parent.id
INNER JOIN syscolumns ParentCol on ParentCol.id = Parent.id and sysforeignkeys.rkey = ParentCol.colid
INNER JOIN sysobjects Child on sysforeignkeys.fkeyid = Child.id
INNER JOIN syscolumns ChildCol on ChildCol.id = Child.id and sysforeignkeys.fkey = ChildCol.colid
WHERE Child.Name = @.ChildTable and ChildCol.Name = @.ChildColumn

--> UPDATE @.ForeignKey Set DescriptionColumn = syscolumns.Name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM @.ForeignKey ForeignKey
INNER JOIN sysobjects on ForeignKey.ParentTable = sysobjects.Name
INNER JOIN syscolumns on syscolumns.id = sysobjects.id
WHERE syscolumns.Name like '%Name%'

UPDATE @.ForeignKey Set DescriptionColumn = syscolumns.Name
FROM @.ForeignKey ForeignKey
INNER JOIN sysobjects on ForeignKey.ParentTable = sysobjects.Name
INNER JOIN syscolumns on syscolumns.id = sysobjects.id
WHERE ForeignKey.DescriptionColumn is null and syscolumns.Name like '%Description%'

RETURN

END
GO

< end example >

So here I'm defining my function which basically returns the parent table details of a given foreign key relationship. But when trying to run the above script I get this,

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

First up I find this weird because isn't sysname equvalent to nvarchar(128)? And aren't nvarchar fields independant of collation? But it gets better,

I thought, ok maybe I'm wrong about sysname needing collation, so I figured I'll check the collation of the syscolumns.name column,

Heres the query

select sysobjects.Name, syscolumns.name, syscolumns.collation
from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name ='syscolumns' and syscolumns.name = 'name'

This is what it returns,

syscolumns name SQL_Latin1_General_CP1_CI_AS

So, let me get this straight, I've explicitly stated that the table variable column is collation SQL_Latin1_General_CP1_CI_AS and the column I'm comparing it to also has a collation of SQL_Latin1_General_CP1_CI_AS, and it doesn't work. . . .

I smell Microsoft, or is there something equally as hideous going on here.

Regards

Adam.Under SQL 2000, a table variable is collated the same as tempdb. Under SQL 2005, table variables (and temp tables) are collated the same as the current DB context. In order to fix this, just add a collate statement to any join involving the table variable, and a system table. (in the above statement, you have the collate statement on the assignment)

The collation is part code page, and mainly sort order. The code page part is the only part that nvarchar, nchar and ntext are "immune" to. They are still bound by the sort order and comparison rules. In a case sensitive or binary collation, "A" does not equal "a".

Clear as mud?

Collation conflict isssue

Hi,

The tempdb db is having different collation than the application db.

I rebuilt the master db with the appropriate collation after backing up
master, model, msdb, appln databases.

On restore of the databases (master, model, msdb, appln) from the backup
restores the backed up database collation.

Is there any idea to restore the db with different collation than the
backed up collation.

Thanks,

*** Sent via Developersdex http://www.developersdex.com ***John Jayaseelan (john.jayaseelan@.caravan-club.co.uk) writes:
> The tempdb db is having different collation than the application db.
> I rebuilt the master db with the appropriate collation after backing up
> master, model, msdb, appln databases.
> On restore of the databases (master, model, msdb, appln) from the backup
> restores the backed up database collation.
> Is there any idea to restore the db with different collation than the
> backed up collation.

The only way to change the collation of a database, is to bulk out
all data, and rebuild the database from scripts. Well, you could
alter the collation of all character columns, but that would require
you to drop indexes and constraints, so that would probably be more work.

The same applies to the system databases. Rather than restoring backups
of these, you should have entered the information by other means.

Another option may be to install a second instance with a matching
collation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

collation conflict for concatenation operation

Hi,
I'm facing the error as the subject stated , "Cannot resolve collation
conflict for concatenation operation".
Base on running "print cast(
databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
I want to do searching on records in tables with partial matching on the
keywords.
the sql statement is :
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? + '%' ) ) )
order by co.active_from_date desc
as I will run this statement in java, the "?" will be filled into string and
both fields are nvarchar.
how should I do to solve this problm? thank you.
Try adding collation designators to the LIKE predicates:
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
OR (title_en like '%' + ? + '%' collate
inese_Taiwan_Stroke_CI_AS) ) )
order by co.active_from_date desc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"little C" <little C@.discussions.microsoft.com> wrote in message
news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> Hi,
> I'm facing the error as the subject stated , "Cannot resolve collation
> conflict for concatenation operation".
> Base on running "print cast(
> databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
> collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
> I want to do searching on records in tables with partial matching on the
> keywords.
> the sql statement is :
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? +
' ) ) )
> order by co.active_from_date desc
> as I will run this statement in java, the "?" will be filled into string
and
> both fields are nvarchar.
> how should I do to solve this problm? thank you.
>
|||thanks Adam,
the problem is solved, I need to put "collate Chinese_Taiwan_Stroke_CI_AS"
right next to each "?". thanks a lot.
Chris C
"Adam Machanic" wrote:

> Try adding collation designators to the LIKE predicates:
>
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
> OR (title_en like '%' + ? + '%' collate
> inese_Taiwan_Stroke_CI_AS) ) )
> order by co.active_from_date desc
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "little C" <little C@.discussions.microsoft.com> wrote in message
> news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> ' ) ) )
> and
>
>
sqlsql

collation conflict for concatenation operation

Hi,
I'm facing the error as the subject stated , "Cannot resolve collation
conflict for concatenation operation".
Base on running "print cast(
databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
I want to do searching on records in tables with partial matching on the
keywords.
the sql statement is :
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? + '%' ) )
)
order by co.active_from_date desc
as I will run this statement in java, the "?" will be filled into string and
both fields are nvarchar.
how should I do to solve this problm? thank you.Try adding collation designators to the LIKE predicates:
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
OR (title_en like '%' + ? + '%' collate
inese_Taiwan_Stroke_CI_AS) ) )
order by co.active_from_date desc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"little C" <little C@.discussions.microsoft.com> wrote in message
news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> Hi,
> I'm facing the error as the subject stated , "Cannot resolve collation
> conflict for concatenation operation".
> Base on running "print cast(
> databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
> collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
> I want to do searching on records in tables with partial matching on the
> keywords.
> the sql statement is :
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? +
' ) ) )
> order by co.active_from_date desc
> as I will run this statement in java, the "?" will be filled into string
and
> both fields are nvarchar.
> how should I do to solve this problm? thank you.
>|||thanks Adam,
the problem is solved, I need to put "collate Chinese_Taiwan_Stroke_CI_AS"
right next to each "?". thanks a lot.
Chris C
"Adam Machanic" wrote:

> Try adding collation designators to the LIKE predicates:
>
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
> OR (title_en like '%' + ? + '%' collate
> inese_Taiwan_Stroke_CI_AS) ) )
> order by co.active_from_date desc
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "little C" <little C@.discussions.microsoft.com> wrote in message
> news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> ' ) ) )
> and
>
>

collation conflict for concatenation operation

Hi,
I'm facing the error as the subject stated , "Cannot resolve collation
conflict for concatenation operation".
Base on running "print cast(
databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
I want to do searching on records in tables with partial matching on the
keywords.
the sql statement is :
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? + '%' ) ) )
order by co.active_from_date desc
as I will run this statement in java, the "?" will be filled into string and
both fields are nvarchar.
how should I do to solve this problm? thank you.Have you tried converting the data to unicode before
concatination ?
Peter
"I favor the Civil Rights Act of 1964 and it must be
enforced at gunpoint if necessary."
Ronald Reagan
>--Original Message--
>Hi,
>I'm facing the error as the subject stated , "Cannot
resolve collation
>conflict for concatenation operation".
>Base on running "print cast(
>databasepropertyex( 'master', 'collation' ) as varchar
(128) )", the
>collation of sql2000 database
is "Chinese_Taiwan_Stroke_CI_AS" .
>I want to do searching on records in tables with partial
matching on the
>keywords.
>the sql statement is :
>select co.cms_content_id, co.title_en,
ca.cms_category_id,
>ca.category_name_en
>from cms_content co, cms_sub_category sc, cms_category ca
>where co.cms_sub_category_id = sc.cms_sub_category_id
>AND sc.cms_category_id = ca.cms_category_id
>AND co.status = 'active' collate
Chinese_Taiwan_Stroke_CI_AS
>AND(((title_ch like '%' + ? + '%' ) OR (title_en
like '%' + ? + '%' ) ) )
>order by co.active_from_date desc
>as I will run this statement in java, the "?" will be
filled into string and
>both fields are nvarchar.
>how should I do to solve this problm? thank you.
>.
>|||Try adding collation designators to the LIKE predicates:
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
OR (title_en like '%' + ? + '%' collate
inese_Taiwan_Stroke_CI_AS) ) )
order by co.active_from_date desc
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"little C" <little C@.discussions.microsoft.com> wrote in message
news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> Hi,
> I'm facing the error as the subject stated , "Cannot resolve collation
> conflict for concatenation operation".
> Base on running "print cast(
> databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
> collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
> I want to do searching on records in tables with partial matching on the
> keywords.
> the sql statement is :
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? +
' ) ) )
> order by co.active_from_date desc
> as I will run this statement in java, the "?" will be filled into string
and
> both fields are nvarchar.
> how should I do to solve this problm? thank you.
>|||thanks Adam,
the problem is solved, I need to put "collate Chinese_Taiwan_Stroke_CI_AS"
right next to each "?". thanks a lot.
Chris C
"Adam Machanic" wrote:
> Try adding collation designators to the LIKE predicates:
>
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
> OR (title_en like '%' + ? + '%' collate
> inese_Taiwan_Stroke_CI_AS) ) )
> order by co.active_from_date desc
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "little C" <little C@.discussions.microsoft.com> wrote in message
> news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> > Hi,
> >
> > I'm facing the error as the subject stated , "Cannot resolve collation
> > conflict for concatenation operation".
> >
> > Base on running "print cast(
> > databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
> > collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
> >
> > I want to do searching on records in tables with partial matching on the
> > keywords.
> >
> > the sql statement is :
> >
> > select co.cms_content_id, co.title_en, ca.cms_category_id,
> > ca.category_name_en
> > from cms_content co, cms_sub_category sc, cms_category ca
> > where co.cms_sub_category_id = sc.cms_sub_category_id
> > AND sc.cms_category_id = ca.cms_category_id
> > AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> > AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? +
> ' ) ) )
> > order by co.active_from_date desc
> >
> > as I will run this statement in java, the "?" will be filled into string
> and
> > both fields are nvarchar.
> >
> > how should I do to solve this problm? thank you.
> >
>
>

Collation Conflict Error 368 (I think)

We just upgraded an instance of SQL Server 2000 to SQL Server 2005. When using our application to login, we are receiving an error 368, which refers to a collation conflict. Has anyone seen this?

I'm moving your thread to the database engine forum, where it may get more attention.

Paul

|||Run the profiler to see which statement your application is issuing against the database. Are you sure you are using the same collation on the new server than on the old one ? Normally this happens if you are comparing two different collation with each other (like in joins or equal expressions).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Unfortunately, only this message got moved for those of us using NNTP. Any chance that when you move a thread, you can mention where the thread was moved *from*, so we can see it? That will certainly increase the chance that it gets more attention after being moved. Steve Kass Drew University Paul Nicholson - MSFT@.discussions.microsoft.com wrote:
> I'm moving your thread to the database engine forum, where it may get
> more attention.
>
> Paul
>
>

Collation Conflict (DHL)

Moving code from Sybase to SQL Server 2005. I have two tables. One table (vINID) is reset and populated right before this code runs. The other table (vINPA) is a table in the database used by the Front End App. This is the error I get when I try and run on our SQL Server 2005.

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.

Here is the SQL code:

SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT

FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated

LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID

WHERE vINID.BLIV_ID = '071600007594'

Thank you for your help.
DavidIdeally, you would change the collation on the oddball table. Was there a reason to make one of these tables with a collation different from the database default collation?|||Just noticed one of the tables is actually on a remote server. Try this:

SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID COLLATE database_default
WHERE vINID.BLIV_ID = '071600007594'|||Code fix works. Thank you. Not sure what "Collation" is or how to change it or what the database default collations is? New to SQL Server 2005.sqlsql

Collation conflict

Hi all,

I have a db server which was installed with Collation 'SQL_Latin_General_CP1_CS_AS' and now I have to replace this server with a new server and I want to install this server with Collation 'SQL_Latin_General_CP1_CI_AS'.

I am using Linked Servers to collect some data from my Sybase database which has "Code Page 850 (Multilingual) character set,us_english,Binary ordering, for use with Code Page 850 (cp850)."

But with new SQL server(case-insensitive settings), I am unable to run a select query (joining a local server and sybase server) and I get an error message "cannot resolve collation conflict for equal to operation"

I have tried changing property of linked server by specifying Remote server collation to true and Collation Name as 'SQL_Latin_General_CP1_CS_AS' as well as 'SQL_Latin_General_CP850_Bin' but I do get the same error message.

Could any of you please help me in this regard ?

Thanks in advance.

Wilson

Edit: Sorted out. Somehow it is working now after specifying Remote server collation to true and Collation Name as 'SQL_Latin_General_CP1_CS_AS'hi,
check this link and tell me.
http://support.microsoft.com/kb/325335/en-us

collation conflict

why do i get collation conflict when i used temp table ??

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

i solved it by using COLLATE Latin1_General_CI_AS (the column name)

will i have collation conflicts again when i put my web app on a web hosting company??

Hi Kakusei,

why do i get collation conflict when i used temp table ??

I assume your database use a different collation from the default one( the default collation is the one which is used by your sql instance). If you create a user database and specify a different default collation thanmodel, the user database has a different default collation thantempdb. All temporary stored procedures or temporary tables are created and stored intempdb. This means that all implicit columns in temporary tables and all coercible-default constants, variables, and parameters in temporary stored procedures have collations that are different from comparable objects created in permanent tables and stored procedures.

This could lead to problems with a mismatch in collations between user-defined databases and system database objects. As to fix it, just specify the collation name when you create temp tables, for example:

CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar COLLATE database_default -- use database_default option to explictly specify the temp table use the same collation as the user database )

You can find more detailed information at:http://msdn2.microsoft.com/en-us/library/ms190920.aspx

Hope my suggestion helps

|||

Hi Bo, thanks for your reply and solution.

I also want to know what is the default collation ? and how can i change it back to the default collation now?

|||

Hi Kakusei,

The default collation is the one which your selected when you install sql server. It diverse from the sql verstion, local region, etc. You can check it through Management Studio (After you have loggin to sql server , right click the instance name and select "properties", there is one entry called "server collation" which is the default collation i mean here). All the talbes, procedure, functions use default collation in tempdb. If you do not specify a collation when creating database/talbes in your user database, then it use default collation.

I suggest you reading that madn article again, it's highly explanatory. I'm sure you will learn pretty a lot after finish reading that. thanks.

Hope my suggestion helps

Collation conflict

I have 2 servers, both of them have the same collation.
Then I create temp table on 1st server:
create table #tmpNovi (country char(3) COLLATE database_default ,datum
datetime)
Then fill the table.
Then I join this table to second server:
select s.* FROM
[SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
ON s.RCO=n.country
and I get an error message:
Cannot resolve collation conflict for equal to operation.
Why? Both servers have the same collation, also temp table country field has
defined COLLATE database_default and still an error?
Thank you,
SimonRun below statement on both servers and post back the results:
SELECT DATABASEPROPERTYEX('pubs', 'Collation')
Substitute pubs with DW_Temp when you execute it on SERVER2 and with the dat
abase name from where
you create the temp table on the other server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:%23P5qSQeNFHA.2520@.tk2msftngp13.phx.gbl...
>I have 2 servers, both of them have the same collation.
> Then I create temp table on 1st server:
> create table #tmpNovi (country char(3) COLLATE database_default ,datum dat
etime)
> Then fill the table.
> Then I join this table to second server:
> select s.* FROM
> [SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
> ON s.RCO=n.country
> and I get an error message:
> Cannot resolve collation conflict for equal to operation.
> Why? Both servers have the same collation, also temp table country field h
as defined COLLATE
> database_default and still an error?
> Thank you,
> Simon
>|||Dear all,
As far as I know it's very simply: just for that is needed that both level o
f
COLLATION coinciding, i.e, level table, level database.
It's strange.
See you,
"simon" wrote:

> I have 2 servers, both of them have the same collation.
> Then I create temp table on 1st server:
> create table #tmpNovi (country char(3) COLLATE database_default ,datum
> datetime)
> Then fill the table.
> Then I join this table to second server:
> select s.* FROM
> [SERVER2].[DW_Temp].[dbo].[t_stanje_cube] s INNER JOIN #tmpNovi n
> ON s.RCO=n.country
> and I get an error message:
> Cannot resolve collation conflict for equal to operation.
> Why? Both servers have the same collation, also temp table country field h
as
> defined COLLATE database_default and still an error?
> Thank you,
> Simon
>
>|||Hi Tibor,
Great with DATABASEPROPERTYEX.I was wondering if it is possible to have
available a similar sentence but at table level?
thanx
"Tibor Karaszi" wrote:

> Run below statement on both servers and post back the results:
> SELECT DATABASEPROPERTYEX('pubs', 'Collation')
> Substitute pubs with DW_Temp when you execute it on SERVER2 and with the d
atabase name from where
> you create the temp table on the other server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:%23P5qSQeNFHA.2520@.tk2msftngp13.phx.gbl...
>
>|||Try sp_help.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:3B49DD33-1160-4069-A161-7A5313738DBC@.microsoft.com...
> Hi Tibor,
> Great with DATABASEPROPERTYEX.I was wondering if it is possible to have
> available a similar sentence but at table level?
> thanx
> "Tibor Karaszi" wrote:
>

collation conflict

Hello,
I try to run this sql:
DECLARE @.db_backup_path varchar(100)
SET @.db_backup_path = 'rightpath'
USE mydb
SET @.db_backup_path = @.db_backup_path + 'mydb'+ '.bak'
BACKUP DATABASE mydb TO DISK = @.db_backup_path WITH INIT
Got error:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21>
22> Msg 446, Level 16, State 8, Server MyServer, Line x
Cannot resolve collation conflict for concatenation operation.
What's it and how to fix? Thanks.Actually it is this line:
SET @.db_backup_path = @.db_backup_path + 'mydb'+ '.bak'
"js" <js@.someone@.hotmail.com> wrote in message
news:uMbCLn8tFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I try to run this sql:
> DECLARE @.db_backup_path varchar(100)
> SET @.db_backup_path = 'rightpath'
> USE mydb
> SET @.db_backup_path = @.db_backup_path + 'mydb'+ '.bak'
> BACKUP DATABASE mydb TO DISK = @.db_backup_path WITH INIT
> Got error:
> 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21>
> 22> Msg 446, Level 16, State 8, Server MyServer, Line x
> Cannot resolve collation conflict for concatenation operation.
> What's it and how to fix? Thanks.
>
>
>|||more info:
just notice mydb is SQL_Latin1_General_Cp1_CS_AS
and master is SQL_Latin1_General_CS_AS
what's SQL_Latin1_General_Cp1_CS_AS mean? how to fix? please help.
"js" <js@.someone@.hotmail.com> wrote in message
news:ui3FIs8tFHA.256@.tk2msftngp13.phx.gbl...
> Actually it is this line:
> SET @.db_backup_path = @.db_backup_path + 'mydb'+ '.bak'
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:uMbCLn8tFHA.460@.TK2MSFTNGP15.phx.gbl...
>|||Are you sure about 'SQL_Latin1_General_CS_AS'?
This is an invalid collation name.
Anyway, look up COLLATE in Books Online.
ML

Collation Conflict

Please I Have This Server Running Pefectely Till This Morning An Error Displayed : Server Source Collation Conflict On Equal. This Was Refering To A Particular Table But It Seems That Table Is Not In The Database.

Please Help Me.Provide more info. Exact error message, server version, etc.sqlsql

Collation Conflict

I am trying to move my system from SQL Server 7.0 to SQL Server 2000 and I a
m
getting a "Cannot resolve collation conflict" on query which joins a table
and a view.
The relevant field in the table is defined thus:
[ST_ACCOUNT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
and the linking field in the View is defined as
CONVERT(Char(8), regno) AS Account_No
Do I have to specifically declare a Collation type in the view. If so, how?
PeterYes you need to specify COLLATION
CREATE TABLE #t (col VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS )
INSERT INTO #t VALUES ('a')
--different collation
CREATE TABLE #t1 (col VARCHAR(10) COLLATE SQL_Latin1_General_CP1255_CI_AS)
INSERT INTO #t1 VALUES ('b')
select * from #t1 join #t on
#t.col=#t1.col
--Server: Msg 446, Level 16, State 9, Line 1
--Cannot resolve collation conflict for equal to operation
select * from #t1 join #t on
#t.col=#t1.col COLLATE SQL_Latin1_General_CP1_CI_AS
--No error
"Petet Tickler" <PetetTickler@.discussions.microsoft.com> wrote in message
news:D1019634-175A-45F2-B012-3B3787D001B7@.microsoft.com...
>I am trying to move my system from SQL Server 7.0 to SQL Server 2000 and I
>am
> getting a "Cannot resolve collation conflict" on query which joins a table
> and a view.
> The relevant field in the table is defined thus:
> [ST_ACCOUNT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> and the linking field in the View is defined as
> CONVERT(Char(8), regno) AS Account_No
> Do I have to specifically declare a Collation type in the view. If so,
> how?
> Peter
>

collation conflict

I have a strange problem. When I run a script which drops and rebuilds an entire
database on one machine, it runs fine. When I run it on another machine,
four procedures fail to create with this error message
Cannot resolve collation conflich for equal to operation.
Further investigation reveals that these 4 procedures (out of 30 odd) call a function
in the SQL inside the procedure. That function also is in the same script and
it gets created successfully.
The script is self containing and does not have any collate statement. So, all collates
is the database default which is Latin1_General_CI_AS.
what can be the cause?
Check if the two servers has different collations in the master database. If they do, watch out for
temp tables (search BOL for database_default). And of course other references outside your database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||The SQL Servers have a different collation, which means that tempdb uses a
different collation. Character columns in temporary tables and table
variables are created with the collation of tempdb, not with the collation
of the database that the user is connected to when the temporary tables and
table variables are created. When you then compare or join these columns in
the temporary tables or table variables to permanent tables, you get a
collation conflict. You can work around this by either specifying all the
character columns in temporary tables and table variables with either an
explicit collation or with COLLATE DATABASE_DEFAULT.
Jacco Schalkwijk
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3es0utF4lp0bU1@.individual.net...
>I have a strange problem. When I run a script which drops and rebuilds an
>entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call
> a function
> in the SQL inside the procedure. That function also is in the same script
> and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So,
> all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Check default collation for servers and databases.
AMB
"Data Cruncher" wrote:

> I have a strange problem. When I run a script which drops and rebuilds an entire
> database on one machine, it runs fine. When I run it on another machine,
> four procedures fail to create with this error message
> Cannot resolve collation conflich for equal to operation.
> Further investigation reveals that these 4 procedures (out of 30 odd) call a function
> in the SQL inside the procedure. That function also is in the same script and
> it gets created successfully.
> The script is self containing and does not have any collate statement. So, all collates
> is the database default which is Latin1_General_CI_AS.
> what can be the cause?
>
|||Thanks all. It was indeed the collate on tempdbs.

collation conflict

Hi, all
I am doing testing of my application, actualy one Store Procedure at the moment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in char columns.
When I change them to database default setting, the stored procedure works OK.
My question is:
how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
Elizabeta
Elizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:

>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the moment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in char columns.
>When I change them to database default setting, the stored procedure works OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>
|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
[vbcol=seagreen]
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "script
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
ening.
>

Collation Conflict

Modify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J

>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=
[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])

Collation Conflict

Hi,
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
Tim
Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>
sqlsql