Errors Changing SQL Collation

just as a matter of interest, which database is it reporting the collation errors on?

The script i've used before was this:
http://blogs.msdn.com/b/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx

They say to do a find and replace on $destdb, but thats a little stupid, as the definition is right at the top,
just change the following

From:
:SETVAR destdb IOFFICE2000
:SETVAR desired_collation Cyrillic_General_CI_AS
:SETVAR script_only 0


To:
:SETVAR destdb EnterpriseVaultDirectory
:SETVAR desired_collation Cyrillic_General_CI_AS
:SETVAR script_only 0
 

Make sure the collation matches the Master DB collation and most importantly run it in Cmd mode and make sure that all EV Servers are off and not trying to access the db

Hi JesusWept 2,

Thanks for taking the time to comment.  The deployment scanner was reporting collation errors on all the EV databases, we migrated the databases to a new server to improve performance a year or so ago and the Server collation was different to the original server used when EV was first installed.

The script you've linked to is the same one that is referenced in the technote I linked in my post.  I've seen a number of Forum posts about various problems with the collation script and tried to follow the technote and other peoples advise as closely as possible.  All EV services are set to manual and have been stopped and I've checked SQL Activity Monitor to ensure that no other process are active agains the EV DBs.  The script has completed fine against 5 other EV DBs so far.

As I mentioned, the script ran in report mode (:SETVAR script_only 1) and returned success so in theory there shouldn't have been an issue.  The script had completed the dropping of the constraints, primary keys, foreign keys etc, updated the collation to match the server and done some of the rebuilds before it flagged up with the constraint error which I thought was a bit unusual given that it didn't flag that issue up in report mode.  As the indexes and other bits and bobs hadn't finished rebuilding and the DB was still in Single User Mode I opted to do a full restore from the backup taken earlier rather than try and perform and troublehooting on a partially built database.

The restores have taken up most of today so I'll try and run the script again on the remaining 3 databases tomorrow to see if they complete this time. *fingers crossed*

Rebuilding the master database wasn't an option as it is a central shared SQL Server, not dedicated to EV otherwise that would have been the ideal solution.  Ironically, the SQL server was built to use SQL_Latin1_General_CP1_CI_AS, which is the preferred collation, but the original SQL Server was built with the default windows Latin1_General_CI_AS so the EV databases had inheritted that.  I'd rather have them on the SQL collation anyway. 

The Audit DB restore is at 90% so I should be able to kick off the scripts again when I get in tomorrow, I'm hoping this glitch was just a temporary issue as I've successfully converted the collation on 2 other Archive Vault Store DBs and the Journal Vault Store DB since then. 

Progress Report

Well, I've tried the scripts on a couple of the databases I was having problems with again. 

On the Vault Store Database:

- Ran VaultStoreDB_8_Programmability.sql up to the first CREATE statement
- ran change_collation.sql with :SETVAR script_only 1 - successful operation
- Ran script to drop TempDB tables
- ran change_collation.sql with :SETVAR script_only 0 - operation completed with errors, in the exact same place as before:

-- CREATE FK CONSTRAINTS
...
ALTER TABLE [dbo].[SavesetProperty]  ADD CONSTRAINT [FK_SavesetIdentity_SavesetProperty] FOREIGN KEY ([SavesetIdentity]) REFERENCES [dbo].[Saveset] ([SavesetIdentity])
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SavesetIdentity_SavesetProperty". The conflict occurred in database "EVExchangeArchiveStore1", table "dbo.Saveset", column 'SavesetIdentity'.

On the Directory Database:

- Ran DirectoryDB_8_Programmability.sql up to the first CREATE statement
- ran change_collation.sql with :SETVAR script_only 1 - operation completed with errors:

-- TRY TO CHANGE COLLATION....
ALTER DATABASE EnterpriseVaultDirectory COLLATE SQL_Latin1_General_CP1_CI_AS
Msg 5075, Level 16, State 1, Line 1689
The object 'CK_RegisteredClientMailboxes_ClientOSType' 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 1689
The object 'CK_RegisteredClientMailboxes_MailboxServerType' 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 1689

I'm still confused why the Vault Store Database reports operational successful when running the collation script in report mode but then throws up an error when running in normal mode.

I'm not sure how to procede with the Directory DB as othen than running the programability script and ensuring no EV services are running and no other locks on the Database.

Has anyone got any other thoughts?

I've attached the full output of messages for the 2 processes