SQL Server 2000 SP4 doesn't support repository deletion

[lbauer]

It is not possible to delete a Vault repository if the Vault database is on SQL Server 2000 SP4. This is due to a bug in SQL Server.

You may see the following errors --

In the Admin Tool:

Code: Select all

VaultServiceAPILib.VaultSoapException: 1104 : FailDBReader 
   at VaultService.VaultAdminService.DeleteRepository(Int32 nRepID) 

In the sgvault.log file:

Code: Select all

 Internal Query Processor Error: The query processor could not produce a query plan.  Contact your primary support provider for more information. 

The workaround is to revert back to SQL Server SP3.

SQL Server 2005 also supports repository deletion.

[ian_sg]

There is a work-around that doesn't involve removing SP4. Removing and re-creating some of Vault's foreign keys seems to alleviate SQL Server's confusion:

Code: Select all

USE sgvault
GO

ALTER TABLE [dbo].[tblfsobjectshares] DROP CONSTRAINT [fk_tblfsobjectshares_objid_tblfsobjects]
ALTER TABLE [dbo].[tblfsobjectshares] ADD CONSTRAINT [fk_tblfsobjectshares_objid_tblfsobjects] FOREIGN KEY ( [objid] ) REFERENCES [dbo].[tblfsobjects] ( [objid] ) ON DELETE CASCADE

ALTER TABLE [dbo].[tblfsobjectversions] DROP CONSTRAINT [fk_tblfsobjectversions_tblfsobjects]
ALTER TABLE [dbo].[tblfsobjectversions] ADD CONSTRAINT [fk_tblfsobjectversions_tblfsobjects] FOREIGN KEY ( [objid] ) REFERENCES [dbo].[tblfsobjects] ( [objid] ) ON DELETE CASCADE

ALTER TABLE [dbo].[tbltransactions] DROP CONSTRAINT [fk_tbltransactions_tblrepositories]
ALTER TABLE [dbo].[tbltransactions] ADD CONSTRAINT [fk_tbltransactions_tblrepositories] FOREIGN KEY ( [repid] ) REFERENCES [dbo].[tblrepositories] ( [repid] ) ON DELETE CASCADE

ALTER TABLE [dbo].[tbldeletions] DROP CONSTRAINT [fk_tbldeletions_objid_tblfsobjects]
ALTER TABLE [dbo].[tbldeletions] ADD CONSTRAINT [fk_tbldeletions_objid_tblfsobjects] FOREIGN KEY ( [objid] ) REFERENCES [dbo].[tblfsobjects] ( [objid] ) ON DELETE CASCADE
GO

After running this (which does take a while on some repositories), you should be able to drop a repository.