ALTER DATABASE and schema bound views.

Hello, everyone!
The question is: Can I alter the collation of a database
inside which there is a schema bound view, under SQL
Server 2000?
If you could help me with it, I'd ve extremely thankful.
We've got a product which is commercialized
internationally.
In order to do so, we have a basic database, and we alter
its collation to fit the target market.
Lately, we added a materialized view, and the ALTER
DATABASE COLLATE... command stopped working.
This is part of the script:
SELECT DATABASEPROPERTYEX('db', 'Collation') --
'SQL_Latin1_General_CP1255_CI_AS'
GO
DROP VIEW [DBO].[MATERIAL_VIEW_001]
GO
CREATE VIEW [DBO].[MATERIAL_VIEW_001]
WITH SCHEMABINDING
AS
SELECT DV.[DEPARTMENT], -- char(10)
DV.[FILENUM], -- tinyint
DV.[FIELDNUM], -- smallint
DV.[FROM_DATE], -- datetime
DV.[VALUE], -- varchar(1024), usually <
20.
EI.[DEP_INF] -- tinyint
FROM [DBO].[DEPVAL] DV WITH (NOLOCK)
JOIN [DBO].[EMPINF] EI WITH (NOLOCK)
ON EI.FILENUM = DV.FILENUM -- tinyint
AND EI.FIELDNUM = DV.FIELDNUM -- smallint
WHERE EI.DEP_INF > 0 -- tinyint
GO
CREATE UNIQUE CLUSTERED INDEX PK_MATERIAL_VIEW_001
ON DBO.[MATERIAL_VIEW_001]
( [DEPARTMENT], [FILENUM], [FIELDNUM], [FROM_DATE],
[DEP_INF], [VALUE] )
GO
-- drop index lv_depval_inf.pk_MATERIAL_VIEW_001 -- This
doesn't help.
ALTER DATABASE DB COLLATE French_CI_AS
/*
Server: Msg 5075, Level 16, State 1, Line 1
The object 'MATERIAL_VIEW_001' is dependent on database
collation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of
database 'db' cannot be set to French_CI_AS.
*/
Can you help me with this?
Thanks in advance,
PabloHi
You don't mention if you change the column collations? e.g
http://tinyurl.com/91sg
I would also expect you to check what collation the system is set to:
Select SERVERPROPERTY(N'Collation')
I would expect the way around this is to drop and re-create the view after
you have changed the collation.
John
"Pablo Aliskevicius" wrote:

> Hello, everyone!
> The question is: Can I alter the collation of a database
> inside which there is a schema bound view, under SQL
> Server 2000?
> If you could help me with it, I'd ve extremely thankful.
> We've got a product which is commercialized
> internationally.
> In order to do so, we have a basic database, and we alter
> its collation to fit the target market.
> Lately, we added a materialized view, and the ALTER
> DATABASE COLLATE... command stopped working.
> This is part of the script:
> SELECT DATABASEPROPERTYEX('db', 'Collation') --
> 'SQL_Latin1_General_CP1255_CI_AS'
> GO
> DROP VIEW [DBO].[MATERIAL_VIEW_001]
> GO
> CREATE VIEW [DBO].[MATERIAL_VIEW_001]
> WITH SCHEMABINDING
> AS
> SELECT DV.[DEPARTMENT], -- char(10)
> DV.[FILENUM], -- tinyint
> DV.[FIELDNUM], -- smallint
> DV.[FROM_DATE], -- datetime
> DV.[VALUE], -- varchar(1024), usually <
> 20.
> EI.[DEP_INF] -- tinyint
> FROM [DBO].[DEPVAL] DV WITH (NOLOCK)
> JOIN [DBO].[EMPINF] EI WITH (NOLOCK)
> ON EI.FILENUM = DV.FILENUM -- tinyint
> AND EI.FIELDNUM = DV.FIELDNUM -- smallint
> WHERE EI.DEP_INF > 0 -- tinyint
> GO
> CREATE UNIQUE CLUSTERED INDEX PK_MATERIAL_VIEW_001
> ON DBO.[MATERIAL_VIEW_001]
> ( [DEPARTMENT], [FILENUM], [FIELDNUM], [FROM_DATE],
> [DEP_INF], [VALUE] )
> GO
> -- drop index lv_depval_inf.pk_MATERIAL_VIEW_001 -- This
> doesn't help.
> ALTER DATABASE DB COLLATE French_CI_AS
> /*
> Server: Msg 5075, Level 16, State 1, Line 1
> The object 'MATERIAL_VIEW_001' is dependent on database
> collation.
> Server: Msg 5072, Level 16, State 1, Line 1
> ALTER DATABASE failed. The default collation of
> database 'db' cannot be set to French_CI_AS.
> */
> Can you help me with this?
>
> Thanks in advance,
> Pablo
>|||Thank you, John, for your answer.
Maybe I wasn't clear enough: the application I'm
supporting has dozens of installations in at least ten
countries (that I know of) spanning three continents (or
four, if you count South America and North America as
two). Two more countries will be added in the next few
months. As a result, the server collation can be just
about any.
The software works on top of SQL Server. Since the
software is alive, the database keeps changing: fields
are added to existing tables, new tables and procedures
are added from time to time, new views appear from time
to time. We have already dozens of tables, and over 300
procedures.
As a result, an automatic upgrade program was written,
which compares the production database at the client's
site, with a 'last model' database. This 'last model'
exists in one place only, with one collation only.
In order to compare the databases, the 'model' database
must assume the 'target' database's collation.
Since there are a LOT of objects, dropping and recreating
objects can be done only as a last resource. A way to
execute ALTER DATABASE even when a schema-bound view
exists would be the best possible solution. After that, I
run a script quite like the one described in the URL you
mention - but the DATABASE_DEFAULT is UNKNOWN until run
time.
Thanks again,
Pablo.

>--Original Message--
>Hi
>You don't mention if you change the column collations?
e.g
>http://tinyurl.com/91sg
>I would also expect you to check what collation the
system is set to:
>Select SERVERPROPERTY(N'Collation')
>I would expect the way around this is to drop and re-
create the view after
>you have changed the collation.
>John
>