Change SDE database's collation

[HenWagner]

Hi all,
I am trying to change my DB (sde single, 9.3.1) collation, but some dependencies exist:

Msg 5075, Level 16, State 1, Line 1
The object 'spatial_ref_zunits' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5075, Level 16, State 1, Line 1
The object 'spatial_ref_xyunits' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'static' cannot be set to SQL_Latin1_General_CP1_CI_AS.

Has anyone handled this before?
TIA
Hen

[AlexMerkulov]

We have the same problem

[KonstantinLapine]

You will need to drop the constraints dependent on your database collation and recreate them after collation is changed:

--drop constraints
USE [database_name]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[spatial_ref_xyunits]') AND parent_object_id = OBJECT_ID(N'[dbo].[SDE_spatial_references]'))
ALTER TABLE [dbo].[SDE_spatial_references] DROP CONSTRAINT [spatial_ref_xyunits]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[spatial_ref_zunits]') AND parent_object_id = OBJECT_ID(N'[dbo].[SDE_spatial_references]'))
ALTER TABLE [dbo].[SDE_spatial_references] DROP CONSTRAINT [spatial_ref_zunits]
GO


--change collation
ALTER DATABASE [database_name] collate SQL_Latin1_General_CP1_CI_AS


--recreate constraints
ALTER TABLE [dbo].[SDE_spatial_references]  WITH CHECK ADD  CONSTRAINT [spatial_ref_zunits] CHECK  (([zunits]>=(1)))
GO

ALTER TABLE [dbo].[SDE_spatial_references] CHECK CONSTRAINT [spatial_ref_zunits]
GO

ALTER TABLE [dbo].[SDE_spatial_references]  WITH CHECK ADD  CONSTRAINT [spatial_ref_xyunits] CHECK  (([xyunits]>=(1)))
GO

ALTER TABLE [dbo].[SDE_spatial_references] CHECK CONSTRAINT [spatial_ref_xyunits]
GO