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

[Erland Sommarskog]

[posted and mailed, please reply in news]

Matik (ma****@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, es****@sommarskog.se

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

[Matik]

Thank You Erland,

As always helpfull answer!

Greatings

Mateusz

[Renwei]

Thanks for your answer!!