Posted by Erland Sommarskog on 09/30/60 11:16

[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/techinfo/productdoc/2000/books.asp

[Back to original message]