Collation problem

Hello,

I think I'd might have a small collation problem.

Configuration:
Two SQL Srv 2000 SP3 (running on clusters).
Booth servers configured with SQL_Latin1_General_CP1_CI_AS
collation.

On each server, I have one database, which collation is
Latin1_General_CI_AS.

I've created a view on Server1.Database1, which is reading complete
table from Server2.Database2.

Checking the collation, the view has, I was surpriced, the collation
was the same as server collation.
Is it always, that building views between two different servers, the
object created will use default collation of server?

The problem is, this view is intergrated in other join-where query on
server1, where other objects used are from server1 and I get error
message:

select TABLE_FROM_SERVER1.Col1 from
TABLE_FROM_SERVER1,
VIEW_ON_SERVER1_BUT_ACCESSING_COMPLETE_SERVER2
where TABLE_FROM_SERVER1.Col1 = 'bubu_si_lala'

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

The sulution, of joining this view will be changed anyway (is not
enought fast) but I would like to know, how is it possible, to solve so
kind of problem.

Is it possible to set the collation for created view, and determine
collation the same the database have?

Greatings

Mateusz[posted and mailed, please reply in news]

Matik (marzec@.sauron.xo.pl) writes:
> Configuration:
> Two SQL Srv 2000 SP3 (running on clusters).
> Booth servers configured with SQL_Latin1_General_CP1_CI_AS
> collation.
> On each server, I have one database, which collation is
> Latin1_General_CI_AS.
> I've created a view on Server1.Database1, which is reading complete
> table from Server2.Database2.
> Checking the collation, the view has, I was surpriced, the collation
> was the same as server collation.
> Is it always, that building views between two different servers, the
> object created will use default collation of server?

Since I don't really know which database that have which collation,
I don't really want to go into speculation. But without looking in
Books Online, my guess is that each column in the view retains the
collation the column has in its source table. And the repro below
appears to confirm this. It also demonstrates how you can modify your
view by using the COLLATE clause to resolve the problem.

create database collate_test collate Polish_CS_AS
go
use collate_test
go
create view nisse_view (PolishCustomerID, CustomerID) as
select CustomerID COLLATE database_default,
CustomerID
from Northwind..Customers
go
-- Succeeds, since CustomerID retains the collation from the
-- Northwind database.
select * from nisse_view n
where not exists (select * from
Northwind..Orders O
where O.CustomerID = n.CustomerID)
go
-- Fails, as we here use the column with a collation
-- of the database.
select * from nisse_view n
where not exists (select * from
Northwind..Orders O
where O.CustomerID = n.PolishCustomerID)

go
use master
go
drop database collate_test

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank You Erland,

As always helpfull answer!

Greatings

Mateusz|||Thanks for your answer!!

collation issues

hello,
i have a sql script/batch that i run against a db every day on my
workstation w/o a problem. recently i tried running it on my laptop
(replication of usual db)
i get an error on one select statement. the statement is a join b/t two
tables, on a field of type varchar(256) . i've tried both like and = as the
operator. one of the fields may actually be of type char(16) -- regardless,
this join always works on my desktop.
on my laptop, the text of the error message is:
Cannot resolve collation conflict for equal to operation
(or when i use the like operator)
Cannot resolve collation conflict for like operation
guessing this has to do w/ some collation setting for my sql server instance
on my laptop, but don't know. also, the sql server on my laptop is
development edition, while on my desktop it's enterprise edition -- don't
know if that matters
thanks for any help
matthewIt sounds as if the collation on the two columns is different. Use QA to scr
ipt
the Create Table statement to the clipboard and paste it into a message. The
re
is a way to coerce one collation into another if you know the collations on
the
columns.
Thomas
"matthew c. harad" <matthewcharad@.discussions.microsoft.com> wrote in messag
e
news:51C9A1FA-0DD3-44C8-AD49-309B5637CC24@.microsoft.com...
> hello,
> i have a sql script/batch that i run against a db every day on my
> workstation w/o a problem. recently i tried running it on my laptop
> (replication of usual db)
> i get an error on one select statement. the statement is a join b/t two
> tables, on a field of type varchar(256) . i've tried both like and = as t
he
> operator. one of the fields may actually be of type char(16) -- regardles
s,
> this join always works on my desktop.
> on my laptop, the text of the error message is:
> Cannot resolve collation conflict for equal to operation
> (or when i use the like operator)
> Cannot resolve collation conflict for like operation
> guessing this has to do w/ some collation setting for my sql server instan
ce
> on my laptop, but don't know. also, the sql server on my laptop is
> development edition, while on my desktop it's enterprise edition -- don't
> know if that matters
> thanks for any help
> matthew|||Check the collation_name of both columns from information_schema.columns and
use COLLATE to force the collations to be the same.
Example:
use northwind
go
create table t1 (
c1 char(10) collate SQL_Latin1_General_CP1_CI_AS
)
go
create table t2 (
c1 char(10) collate SQL_Latin1_General_CP1_CS_AS
)
go
insert into t1 values('microsoft')
insert into t2 values('Microsoft')
go
-- will give an error
select
*
from
t1 inner join t2
on t1.c1 = t2.c1
go
select
*
from
t1 inner join t2
on t1.c1 = t2.c1 collate SQL_Latin1_General_CP1_CI_AS
go
drop table t1, t2
go
AMB
"matthew c. harad" wrote:

> hello,
> i have a sql script/batch that i run against a db every day on my
> workstation w/o a problem. recently i tried running it on my laptop
> (replication of usual db)
> i get an error on one select statement. the statement is a join b/t two
> tables, on a field of type varchar(256) . i've tried both like and = as t
he
> operator. one of the fields may actually be of type char(16) -- regardles
s,
> this join always works on my desktop.
> on my laptop, the text of the error message is:
> Cannot resolve collation conflict for equal to operation
> (or when i use the like operator)
> Cannot resolve collation conflict for like operation
> guessing this has to do w/ some collation setting for my sql server instan
ce
> on my laptop, but don't know. also, the sql server on my laptop is
> development edition, while on my desktop it's enterprise edition -- don't
> know if that matters
> thanks for any help
> matthew

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

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

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

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
TimModify 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
>
>.
>|||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
>|||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...
> 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
> >
> >
> >.
> >|||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!TK2MSFTNGP08.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...
> 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
> >
> >
> >.
> >

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
TimYour 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].[d
bo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Produ
ct]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>

collation case sensitive

I have a vendor who is trying to apply an upgrade to our database, he is running into errors because the database collation is set to case sensitive. Is there a way to by pass the setting so he can procedd with the update scripts.
Robert,
A case sensitive TSQL script can often be made to function in case
insensitive manner by using UPPER or COLLATE. You could also change the
collation of the database if there are new tables which are set to inherit
the default collation. However I'd need to know more details to see if these
methods can help - please post back to explain further what the upgrade is
doing?.
Paul Ibison

collation case sensitive

I have a vendor who is trying to apply an upgrade to our database, he is run
ning into errors because the database collation is set to case sensitive. Is
there a way to by pass the setting so he can procedd with the update script
s.Robert,
A case sensitive TSQL script can often be made to function in case
insensitive manner by using UPPER or COLLATE. You could also change the
collation of the database if there are new tables which are set to inherit
the default collation. However I'd need to know more details to see if these
methods can help - please post back to explain further what the upgrade is
doing?.
Paul Ibisonsqlsql

Collation and Win2K3 Clustered Environment.

This past weekend I was challenged a little in resolving what I hoped was a
simple collation issue. We are running Win2K3 Ent. Clustering Services, SQL
Server 2000 Enterprise, b.8.00.760.
On Friday, 2.11, a user migrated an application to this new clustered setup
and immediately received the infamous 446 collation error.
I spent most of Friday evening and all day Saturday reviewing support topics
and news groups. I found that there was 1 issue that could be affecting us -
the build of SQL Server for Win2K3 Ent. Clustering is subtly different than
for Win2KAS Clustering - and all of my research came back to one thing: Run
rebuildm.exe and set the collation of this instance to be the same as the
existing development/production environment. The default setup on the cluster
was different than the previous production setup, as well as different than
the current development environment.
So, I backed everyone's database up, then the master objects, shut the
instance down, executed rebuild and set the collation to what I needed it to
be.
When I brought this user's database online - the same exact error occurs.
Prior to doing this, here is what we were faced with:
Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
The new clustered SQL=SQL Server Enterprise, w/Collate=Latin1_General_CI_AI.
The database/application in question, at the procedure where we receive the
collation error, basically calls a function that creates a temp table, then
another that pulls data for a report - however, the data is collected as a
JOIN on this tempdb temp object and a series of Views the programmer
previously setup. These views in turn hit various other static tables as well.
We were thinking that because of the collation difference on the new Win2K3
setup, as well as Collation/Locale settings differences with Win2K3
Clustering, we should at a minimum change the instance to match the
SQL_Latin1, etc. collation of development. This however, has not solved the
problem.
At this juncture, the Win2K3 Clustered server has the collation
SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
their current production environment, which is 2KAS nonclustered) is
SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now is
the accent sensitivity - but this should not be the issue.
If anyone else out there has any feedback, I'd be grateful for your time.
Thanks...
mhamilton"AT"nusoftsolutions"DOT"com
If the collation names are different you'll get the collation conflict
error -- a difference in accent sensitivity is sufficient to expose the
problem.
One option is rebuild master in dev or test (again) so that the two servers
have the same collation. You almost matched the collation last time, but
the different accent sensitivity setting is also critical.
Another option is to make sure that the T-SQL is written in a way that
makes it immune to the problem. For the scenario you describe you could do
this by making sure that your temp tables inherit the collation of the
current user database, not the collation of tempdb. A "COLLATE
database_default" clause will accomplish this. For example, when creating
the temp table in the stored proc:
CREATE TABLE #temp1 (
c1 int,
c2 varchar (30) COLLATE database_default,
c3 char(12) COLLATE database_default,
)
HTH,
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.
| Thread-Topic: Collation and Win2K3 Clustered Environment.
| thread-index: AcUUNUWE2BoB7xCnSeWo8pOsRpOhYg==
| X-WBNR-Posting-Host: 12.227.130.93
| From: "=?Utf-8?B?TWlrZUg=?=" <MikeH@.discussions.microsoft.com>
| Subject: Collation and Win2K3 Clustered Environment.
| Date: Wed, 16 Feb 2005 06:39:08 -0800
| Lines: 51
| Message-ID: <87A19939-8444-4CEC-BBB4-ED092DFFD4D7@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.clustering
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.clustering:3114
| X-Tomcat-NG: microsoft.public.sqlserver.clustering
|
| This past weekend I was challenged a little in resolving what I hoped was
a
| simple collation issue. We are running Win2K3 Ent. Clustering Services,
SQL
| Server 2000 Enterprise, b.8.00.760.
|
| On Friday, 2.11, a user migrated an application to this new clustered
setup
| and immediately received the infamous 446 collation error.
|
| I spent most of Friday evening and all day Saturday reviewing support
topics
| and news groups. I found that there was 1 issue that could be affecting
us -
| the build of SQL Server for Win2K3 Ent. Clustering is subtly different
than
| for Win2KAS Clustering - and all of my research came back to one thing:
Run
| rebuildm.exe and set the collation of this instance to be the same as the
| existing development/production environment. The default setup on the
cluster
| was different than the previous production setup, as well as different
than
| the current development environment.
|
| So, I backed everyone's database up, then the master objects, shut the
| instance down, executed rebuild and set the collation to what I needed it
to
| be.
|
| When I brought this user's database online - the same exact error occurs.
|
| Prior to doing this, here is what we were faced with:
| Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
| Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
|
| The new clustered SQL=SQL Server Enterprise,
w/Collate=Latin1_General_CI_AI.
|
| The database/application in question, at the procedure where we receive
the
| collation error, basically calls a function that creates a temp table,
then
| another that pulls data for a report - however, the data is collected as
a
| JOIN on this tempdb temp object and a series of Views the programmer
| previously setup. These views in turn hit various other static tables as
well.
|
| We were thinking that because of the collation difference on the new
Win2K3
| setup, as well as Collation/Locale settings differences with Win2K3
| Clustering, we should at a minimum change the instance to match the
| SQL_Latin1, etc. collation of development. This however, has not solved
the
| problem.
|
| At this juncture, the Win2K3 Clustered server has the collation
| SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
| their current production environment, which is 2KAS nonclustered) is
| SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now
is
| the accent sensitivity - but this should not be the issue.
|
| If anyone else out there has any feedback, I'd be grateful for your time.
|
| Thanks...
|
| mhamilton"AT"nusoftsolutions"DOT"com
|
|||Bart, thanks for getting back to me.
I must confess, moving from 2KAS Clustering to 2K3 Clustering is NOT fun.
Simply because of the nuances I am finding.
Foremost, I did get the application working, and thus far I have not
experienced the infamous 446 collation error.
However...
This particular cluster is only running 6 instances of SQL Server. Each
instance has the Full Text Search engine/component installed - yet only 2 of
the instances are actually using it - and the problem I ran into is on 1 of
these 2 instances.
Specifically, this instance has the full text search active.
Now...
IF - and I say this 'loudly' - IF the full text server was 'offline' when I
did the rebuild - then restarted the instance and tested the app - the app
failed. Most interesting...
It took me a couple days to see what was happening. So... I did the same
with the full text 'online' and voila!!! I have joy... The application worked
fine.
Now... I have a few questions, but I'm sure you're not going to be able to
answer them anymore than I can. This notwithstanding, I find it interesting
that rebuilding the instance - with the SQL Full Text 'offline' when I do it
-could actually keep it from working. Yes, software is software - but this is
quirky at best.
Anyway... Thank you for responding... I will be in contact with PSS and my
manager on this issue, and if you have any other questions regarding the
setup, please feel free to contact me.
"Bart Duncan [MSFT]" wrote:

> If the collation names are different you'll get the collation conflict
> error -- a difference in accent sensitivity is sufficient to expose the
> problem.
> One option is rebuild master in dev or test (again) so that the two servers
> have the same collation. You almost matched the collation last time, but
> the different accent sensitivity setting is also critical.
> Another option is to make sure that the T-SQL is written in a way that
> makes it immune to the problem. For the scenario you describe you could do
> this by making sure that your temp tables inherit the collation of the
> current user database, not the collation of tempdb. A "COLLATE
> database_default" clause will accomplish this. For example, when creating
> the temp table in the stored proc:
> CREATE TABLE #temp1 (
> c1 int,
> c2 varchar (30) COLLATE database_default,
> c3 char(12) COLLATE database_default,
> )
> HTH,
> 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.
>
> --
> | Thread-Topic: Collation and Win2K3 Clustered Environment.
> | thread-index: AcUUNUWE2BoB7xCnSeWo8pOsRpOhYg==
> | X-WBNR-Posting-Host: 12.227.130.93
> | From: "=?Utf-8?B?TWlrZUg=?=" <MikeH@.discussions.microsoft.com>
> | Subject: Collation and Win2K3 Clustered Environment.
> | Date: Wed, 16 Feb 2005 06:39:08 -0800
> | Lines: 51
> | Message-ID: <87A19939-8444-4CEC-BBB4-ED092DFFD4D7@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.clustering
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFT NGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.clustering:3114
> | X-Tomcat-NG: microsoft.public.sqlserver.clustering
> |
> | This past weekend I was challenged a little in resolving what I hoped was
> a
> | simple collation issue. We are running Win2K3 Ent. Clustering Services,
> SQL
> | Server 2000 Enterprise, b.8.00.760.
> |
> | On Friday, 2.11, a user migrated an application to this new clustered
> setup
> | and immediately received the infamous 446 collation error.
> |
> | I spent most of Friday evening and all day Saturday reviewing support
> topics
> | and news groups. I found that there was 1 issue that could be affecting
> us -
> | the build of SQL Server for Win2K3 Ent. Clustering is subtly different
> than
> | for Win2KAS Clustering - and all of my research came back to one thing:
> Run
> | rebuildm.exe and set the collation of this instance to be the same as the
> | existing development/production environment. The default setup on the
> cluster
> | was different than the previous production setup, as well as different
> than
> | the current development environment.
> |
> | So, I backed everyone's database up, then the master objects, shut the
> | instance down, executed rebuild and set the collation to what I needed it
> to
> | be.
> |
> | When I brought this user's database online - the same exact error occurs.
> |
> | Prior to doing this, here is what we were faced with:
> | Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
> | Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
> |
> | The new clustered SQL=SQL Server Enterprise,
> w/Collate=Latin1_General_CI_AI.
> |
> | The database/application in question, at the procedure where we receive
> the
> | collation error, basically calls a function that creates a temp table,
> then
> | another that pulls data for a report - however, the data is collected as
> a
> | JOIN on this tempdb temp object and a series of Views the programmer
> | previously setup. These views in turn hit various other static tables as
> well.
> |
> | We were thinking that because of the collation difference on the new
> Win2K3
> | setup, as well as Collation/Locale settings differences with Win2K3
> | Clustering, we should at a minimum change the instance to match the
> | SQL_Latin1, etc. collation of development. This however, has not solved
> the
> | problem.
> |
> | At this juncture, the Win2K3 Clustered server has the collation
> | SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
> | their current production environment, which is 2KAS nonclustered) is
> | SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now
> is
> | the accent sensitivity - but this should not be the issue.
> |
> | If anyone else out there has any feedback, I'd be grateful for your time.
> |
> | Thanks...
> |
> | mhamilton"AT"nusoftsolutions"DOT"com
> |
>

code getting progressively slower within a transaction

Hi guys,
I've got a stored proc running in a transaction, it does a lot of
complicated processing in terms of selecting from just about every
table in the db, and making a variety of updates. I can't post the DDL
or code, but hopefully it will suffice to say that it selects, updates,
deletes and inserts into lots of tables, and all runs wrapped up within
just 1 transaction (there are no nested transactions - at least not
explicit ones).
The code loops through with a cursor, running another stored procedure
to actually then process the row.
Each iteration takes longer and longer, but if we take it out of the
outer transaction then it's fine. What might be building up that could
cause this to happen? I assume it's not locking as this would cause
things to just deadlock rather than slow wouldn't it? My only guess is
that it's not able to clear something in the transaction log, and it is
having to grow the file which is taking time, but that would not get
progressively worse.
Cheers
WillI forgot to say, this job runs during maintenance, so there are no
other processes to lock with or compete with|||Will
u might be having a small transaction log file with an autogrowth by
a few percent. The process might get slowed if the transaction log grows
frequenlty. try to increase the size of the transaction log file and run the
proc. There might also be a problem with the tempdb overuse. Try to put the
tempdb and the transaction log file in seperate disks if u can. hope this
helps|||Autogrow will take longer each time if you have a % defined. The key is to
always ensure there is enough free space before you start the process. What
kind of cursors are you using? Try to declare them as Static and see if
that helps.
Andrew J. Kelly SQL MVP
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144830633.350622.218100@.z34g2000cwc.googlegroups.com...
> Hi guys,
> I've got a stored proc running in a transaction, it does a lot of
> complicated processing in terms of selecting from just about every
> table in the db, and making a variety of updates. I can't post the DDL
> or code, but hopefully it will suffice to say that it selects, updates,
> deletes and inserts into lots of tables, and all runs wrapped up within
> just 1 transaction (there are no nested transactions - at least not
> explicit ones).
> The code loops through with a cursor, running another stored procedure
> to actually then process the row.
> Each iteration takes longer and longer, but if we take it out of the
> outer transaction then it's fine. What might be building up that could
> cause this to happen? I assume it's not locking as this would cause
> things to just deadlock rather than slow wouldn't it? My only guess is
> that it's not able to clear something in the transaction log, and it is
> having to grow the file which is taking time, but that would not get
> progressively worse.
> Cheers
> Will
>

Code behaviour/performance on 2 machines

Hi,
I have UAT and production servers with same database schema. I am running a
stored procedure on both machines, Its taking much more time on UAT (31 hrs)
where as it is taking less time i.e. 8 hrs (which is expected because of
nature of query) Can someone please explain why is it taking more time on UA
T?
UAT SQL SERVER 2000 has SP4
Production SQL SERVER 2000 has SP3
Please help ASAP.
I can also be reached at sahil.arora@.cit.com
Thanks
SahilJust becuase the servers share the same database model, there is the issue
of differences in hardware configuration and even differences in the
physical implementation of the database such as: volume of data, placement
of files, index fragmentation, statistics, sever/database configurations,
etc. To start with, take a look at the Performance tab of Task Manager on
both servers and compare to what extent they are maxing out on memory and
CPU.
Compare the execution plan between the 2 servers:
http://msdn.microsoft.com/library/d... />
1_1pfd.asp
Also, the following article provides some good performance oriented check
lists and explains how to audit performance.
http://www.sql-server-performance.c...mance_audit.asp
"Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
> Hi,
> I have UAT and production servers with same database schema. I am running
> a
> stored procedure on both machines, Its taking much more time on UAT (31
> hrs)
> where as it is taking less time i.e. 8 hrs (which is expected because of
> nature of query) Can someone please explain why is it taking more time on
> UAT?
> UAT SQL SERVER 2000 has SP4
> Production SQL SERVER 2000 has SP3
> Please help ASAP.
> I can also be reached at sahil.arora@.cit.com
> Thanks
> Sahil|||I just checked everything on both servers, its same and the server on which
is taking more time is much more powerful than server with less time. any
comments?
"JT" wrote:

> Just becuase the servers share the same database model, there is the issue
> of differences in hardware configuration and even differences in the
> physical implementation of the database such as: volume of data, placement
> of files, index fragmentation, statistics, sever/database configurations,
> etc. To start with, take a look at the Performance tab of Task Manager on
> both servers and compare to what extent they are maxing out on memory and
> CPU.
> Compare the execution plan between the 2 servers:
> http://msdn.microsoft.com/library/d...>
n_1_1pfd.asp
> Also, the following article provides some good performance oriented check
> lists and explains how to audit performance.
> http://www.sql-server-performance.c...mance_audit.asp
>
> "Sahil Arora" <Sahil Arora@.discussions.microsoft.com> wrote in message
> news:40E34A48-D573-46EF-98BE-BA4A84A9A113@.microsoft.com...
>
>|||Do both servers have identical volumes of data, is the execution plan of the
query identical, is the OS level file fragmentation and database level index
fragmentation optimized or similar on both servers, etc.. It could be one or
all of a hundred things. Perhaps running a performance audit log on both
servers and comparing the results will reveal something.
INF: Job to Monitor SQL Server 2000 Performance and Activity
http://support.microsoft.com/defaul...kb;en-us;283696
"Sahil Arora" <SahilArora@.discussions.microsoft.com> wrote in message
news:2FE8B79F-9EC2-445A-9648-65F1C7CAABAD@.microsoft.com...
>I just checked everything on both servers, its same and the server on which
> is taking more time is much more powerful than server with less time. any
> comments?
> "JT" wrote:
>

Code and diff sql servers

I'm running out of ideas...
Given the following example of code:
set @.partialname = 'u'
SELECT col1, col2, col3
FROM tbl1
WHERE col1 LIKE @.partialName + '%'
ORDER BY col1
Why would two different sql servers, having exactly the same data, give
different results? One server returns an empty set, while the other returns
all rows where col1 starts with 'u'. A server setting I'm guessing, but
can't find what it is.
Can you help?crud - I messed up my explanation- it works with the 'u' but not when
@.partialname is blank ('').
Its this code that works on one server, but not the other:
set @.partialname = ''
SELECT col1, col2, col3
FROM tbl1
WHERE col1 LIKE @.partialName + '%'
ORDER BY col1
If @.partialname = 'u' -- both servers process correctly.
Sorry about that.
'
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:uMmFbzANGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I'm running out of ideas...
> Given the following example of code:
> set @.partialname = 'u'
> SELECT col1, col2, col3
> FROM tbl1
> WHERE col1 LIKE @.partialName + '%'
> ORDER BY col1
> Why would two different sql servers, having exactly the same data, give
> different results? One server returns an empty set, while the other
> returns all rows where col1 starts with 'u'. A server setting I'm
> guessing, but can't find what it is.
> Can you help?
>
>|||My guess is that the one thing you do not show - the data type of
partialname - is the problem. Is it, by any chance, CHAR(1)? If so
you are matching on ' %' when it is blank. Try making it varchar.
And you might have tried a little research of your own:
set @.partialname = ''
SELECT @.partialName + '%'
Roy
On Fri, 17 Feb 2006 14:03:14 -0800, "mikeb" <mike@.nohostanywhere.com>
wrote:

>crud - I messed up my explanation- it works with the 'u' but not when
>@.partialname is blank ('').
>Its this code that works on one server, but not the other:
>set @.partialname = ''
>SELECT col1, col2, col3
>FROM tbl1
>WHERE col1 LIKE @.partialName + '%'
>ORDER BY col1
>If @.partialname = 'u' -- both servers process correctly.
>Sorry about that.
>'
>"mikeb" <mike@.nohostanywhere.com> wrote in message
>news:uMmFbzANGHA.3908@.TK2MSFTNGP10.phx.gbl...
>|||TRIED RESEARCH OF MY OWN? I've done tons of searches Roy, spent the last
couple hours trying different options. ALL before posting.
You might want to get your crystal ball in for repair - it doesn't seem to
be working today...
@.partialname is VarChar(50)
It appears that the other database is SQL7, versus SQL2000 (which works)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:31mcv1lrouvu2dri9u7b0rbt19a91i4gcv@.
4ax.com...
> My guess is that the one thing you do not show - the data type of
> partialname - is the problem. Is it, by any chance, CHAR(1)? If so
> you are matching on ' %' when it is blank. Try making it varchar.
> And you might have tried a little research of your own:
> set @.partialname = ''
> SELECT @.partialName + '%'
> Roy
>
> On Fri, 17 Feb 2006 14:03:14 -0800, "mikeb" <mike@.nohostanywhere.com>
> wrote:
>|||It seems that the difference was that even though @.partialName, a
varchar(50), was passed an empty string ('') to the s.proc, SQL7 somehow
converted it to a single blank char (' '). Where SQL2000 left it empty. I
could very well be doing something wrong here - I'm just trying to fix an
error in what code we were left with. Open to suggestions if its bad form.
Wow. I even kept researching after my hand was slapped for not (sic)...
pomposity gets really tiring sometimes.
"mikeb" <mike@.nohostanywhere.com> wrote in message
news:udUKw1BNGHA.2752@.TK2MSFTNGP14.phx.gbl...
> TRIED RESEARCH OF MY OWN? I've done tons of searches Roy, spent the last
> couple hours trying different options. ALL before posting.
> You might want to get your crystal ball in for repair - it doesn't seem to
> be working today...
> @.partialname is VarChar(50)
> It appears that the other database is SQL7, versus SQL2000 (which works)
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:31mcv1lrouvu2dri9u7b0rbt19a91i4gcv@.
4ax.com...
>|||Sorry it came across that way. My apologies.
You should be able to get around the problem with:
RTRIM(@.partialName) + '%'
Roy|||Yep, thats exactly what I did to get it working - I meant to mention that
too in the previous post. thx.
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:27scv1l4fqtv3hrvuf0msr6q6elv7fl5ej@.
4ax.com...
> Sorry it came across that way. My apologies.
> You should be able to get around the problem with:
> RTRIM(@.partialName) + '%'
> Roy

cmdexec subsystem failure

have a scheduled job that has been running fine for 2 years, but this week I keep getting the following error:

0x469A75B7998F8142A910FA7E9983CCDF

has caused an exception in the CmdExec subsystem and has been terminated.

the enterprise manager shows that the job failed, but the app still shows up in task manager.

the .cmd file has a single step in it that calls the .exe file and does an "if errorlevel = 1" option that displays an error message.

if I run the .exe file outside of enterprise manager, it runs just fine.

I searched around on microsoft.com and the only reference to this error I could find was a but that was fixed in Sql Server 6.5. I am running SQL Server 2000, the server that is running the apps has Windows 2000 Advanced Server installed.

does anyone have any suggestions?

Thanks

I too have been receiving this same error and have not been able to find a soloution. Any help out thier in microsoft land?

Gary

|||

Can one of you pass on the job details and that can help us to narrow down the issue ?

Also, please do mention the OS environment and SP of SQL Server so we can try on the same settings in our lab ?

Thanks,

Gops Dwarak

|||

The job that is running is a series of COBOL programs, you would not be able to duplicate the exact process.

the OS is Win 2000 Advanced Server SP 4 build 295

SQL Server 2000 SP 3

Enterprise Mgr is build 2195 SP 4

it is running a .cmd file that has 4 steps in it. the problem usually happens in step 2 which is a Large Cobol Program.

even on reruns of the same exact input file, it happens in different places(so it is not bad data or a size issue)

cmdexec subsystem failure

have a scheduled job that has been running fine for 2 years, but this week I keep getting the following error:

0x469A75B7998F8142A910FA7E9983CCDF

has caused an exception in the CmdExec subsystem and has been terminated.

the enterprise manager shows that the job failed, but the app still shows up in task manager.

the .cmd file has a single step in it that calls the .exe file and does an "if errorlevel = 1" option that displays an error message.

if I run the .exe file outside of enterprise manager, it runs just fine.

I searched around on microsoft.com and the only reference to this error I could find was a but that was fixed in Sql Server 6.5. I am running SQL Server 2000, the server that is running the apps has Windows 2000 Advanced Server installed.

does anyone have any suggestions?

Thanks

I too have been receiving this same error and have not been able to find a soloution. Any help out thier in microsoft land?

Gary

|||

Can one of you pass on the job details and that can help us to narrow down the issue ?

Also, please do mention the OS environment and SP of SQL Server so we can try on the same settings in our lab ?

Thanks,

Gops Dwarak

|||

The job that is running is a series of COBOL programs, you would not be able to duplicate the exact process.

the OS is Win 2000 Advanced Server SP 4 build 295

SQL Server 2000 SP 3

Enterprise Mgr is build 2195 SP 4

it is running a .cmd file that has 4 steps in it. the problem usually happens in step 2 which is a Large Cobol Program.

even on reruns of the same exact input file, it happens in different places(so it is not bad data or a size issue)

cmdexec subsystem failure

I have a scheduled job that has been running fine for 2 years, but this week I keep getting the following error:

0x469A75B7998F8142A910FA7E9983CCDF

has caused an exception in the CmdExec subsystem and has been terminated.

the enterprise manager shows that the job failed, but the app still shows up in task manager.

any suggestions?

thanks

What does the job do?

What technology is it calling?

If its been running 2 years then I doubt you're calling SSIS hence this is the wrong forum for you.

-Jamie

Cmdexec on SQL 2000

On a SQL 2000 I am running a Job with the following single step:
xcopy \\nas01\backup\*.* \\nas02\backup /s /e /i /c /d /y
xcopy \\nas01\kunder\*.* \\nas02\Kunder /s /e /i /c /d /y
xcopy \\nas01\projekter\*.* \\nas02\projekter /s /e /i /c /d /y
xcopy \\nas01\konvertering\*.* \\nas02\konvertering /s /e /i /c /d /y
The job executes and report success, however only the first line has
been carried out. Is it not possible to run several lines in a single
step?Hello,
Put all this commands in a single batch (.BAT) file and use the BAT file
name inside Agent Job and try?
Thanks
Hari
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1175843398.821347.252100@.n76g2000hsh.googlegroups.com...
> On a SQL 2000 I am running a Job with the following single step:
> xcopy \\nas01\backup\*.* \\nas02\backup /s /e /i /c /d /y
> xcopy \\nas01\kunder\*.* \\nas02\Kunder /s /e /i /c /d /y
> xcopy \\nas01\projekter\*.* \\nas02\projekter /s /e /i /c /d /y
> xcopy \\nas01\konvertering\*.* \\nas02\konvertering /s /e /i /c /d /y
> The job executes and report success, however only the first line has
> been carried out. Is it not possible to run several lines in a single
> step?
>

Cmdexec on SQL 2000

On a SQL 2000 I am running a Job with the following single step:
xcopy \\nas01\backup\*.* \\nas02\backup /s /e /i /c /d /y
xcopy \\nas01\kunder\*.* \\nas02\Kunder /s /e /i /c /d /y
xcopy \\nas01\projekter\*.* \\nas02\projekter /s /e /i /c /d /y
xcopy \\nas01\konvertering\*.* \\nas02\konvertering /s /e /i /c /d /y
The job executes and report success, however only the first line has
been carried out. Is it not possible to run several lines in a single
step?Hello,
Put all this commands in a single batch (.BAT) file and use the BAT file
name inside Agent Job and try?
Thanks
Hari
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1175843398.821347.252100@.n76g2000hsh.googlegroups.com...
> On a SQL 2000 I am running a Job with the following single step:
> xcopy \\nas01\backup\*.* \\nas02\backup /s /e /i /c /d /y
> xcopy \\nas01\kunder\*.* \\nas02\Kunder /s /e /i /c /d /y
> xcopy \\nas01\projekter\*.* \\nas02\projekter /s /e /i /c /d /y
> xcopy \\nas01\konvertering\*.* \\nas02\konvertering /s /e /i /c /d /y
> The job executes and report success, however only the first line has
> been carried out. Is it not possible to run several lines in a single
> step?
>