Collation problem

[Matik]

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_Gene ral_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.Databas e1, which is reading complete
table from Server2.Databas e2.

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_SERV ER1.Col1 from
TABLE_FROM_SERV ER1,
VIEW_ON_SERVER1 _BUT_ACCESSING_ COMPLETE_SERVER 2
where TABLE_FROM_SERV ER1.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

[Erland Sommarskog]

Re: Collation problem

[posted and mailed, please reply in news]

Matik (marzec@sauron. xo.pl) writes:[color=blue]
> Configuration:
> Two SQL Srv 2000 SP3 (running on clusters).
> Booth servers configured with SQL_Latin1_Gene ral_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.Databas e1, which is reading complete
> table from Server2.Databas e2.
>
> 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?[/color]

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 (PolishCustomer ID, CustomerID) as
select CustomerID COLLATE database_defaul t,
CustomerID
from Northwind..Cust omers
go
-- Succeeds, since CustomerID retains the collation from the
-- Northwind database.
select * from nisse_view n
where not exists (select * from
Northwind..Orde rs 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..Orde rs O
where O.CustomerID = n.PolishCustome rID)

go
use master
go
drop database collate_test

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Matik]

Re: Collation problem

Thank You Erland,

As always helpfull answer!

Greatings

Mateusz

[Renwei]

Re: Collation problem

Thanks for your answer!!