topic Re: Change SDE database's collation in Data Management Questions

Change SDE database's collation

HenWagner — Thu, 25 Nov 2010 14:27:51 GMT

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

Re: Change SDE database's collation

AlexMerkulov — Mon, 18 Apr 2011 09:05:47 GMT

We have the same problem

Re: Change SDE database's collation

KonstantinLapine — Mon, 21 Nov 2011 19:39:07 GMT

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