Stored procedure to Reset the OD. In SQL Manager, "Programmability>Stored Procedures" right click "new stored procedure"
GO
/****** Object: StoredProcedure [dbo].[ResetOD] Script Date: 08/22/2007 10:06:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: David Valentine
-- Create date: April 2007
-- Description: Clean out the database
-- =============================================
CREATE PROCEDURE [dbo].[ResetOD]
-- Add the parameters for the stored procedure here
@resetThisDB int = 0
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--SELECT resetThisDB
IF ( @resetThisDB != 0 )
BEGIN
--Clear out the Tables
DELETE FROM [Groups]
DELETE FROM [GroupDescriptions]
DELETE FROM [DataValues]
DELETE FROM [sites] where siteID
DELETE FROM [variables] where variableID
DELETE FROM [qualifiers] where qualifierID
DELETE FROM [sources] where sourceID
DELETE FROM [derivedfrom]
DELETE FROM [seriescatalog]
-- clean up vocabs
DELETE FROM [methods] where MethodID >0
DELETE FROM [Samples] where SampleID >0
DELETE FROM [offsettypes] where OffsetTypeID >0
DELETE FROM [qualifiers] where qualifierID >0
--Reset the primary key field
DBCC CHECKIDENT (SeriesCatalog, RESEED, 0)
-- DBCC CHECKIDENT (DataValues, RESEED, 0)
-- DBCC CHECKIDENT (Sites, RESEED, 0)
-- DBCC CHECKIDENT (Qualifiers, RESEED, 0)
-- DBCC CHECKIDENT (Variables, RESEED, 0)
END
Called by executing a query like:
USE [OD]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[ResetOD]
@resetThisDB = 1
SELECT 'Return Value' = @return_value
GO
Custom Procedure used in developement where the database has sample data in it:
GO
/****** Object: StoredProcedure [dbo].[ResetOD] Script Date: 08/22/2007 10:06:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: David Valentine
-- Create date: April 2007
-- Description: Clean out the database
-- =============================================
CREATE PROCEDURE [dbo].[ResetOD]
-- Add the parameters for the stored procedure here
@resetThisDB int = 0
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--SELECT resetThisDB
IF ( @resetThisDB != 0 )
BEGIN
--Clear out the Tables
--DELETE FROM [SeriesCatlog]
DELETE FROM [Groups]
DELETE FROM [GroupDescriptions]
DELETE FROM [DataValues]
DELETE FROM [sites] where siteID > -1
DELETE FROM [variables] where variableID >-1
DELETE FROM [qualifiers] where qualifierID > -1
DELETE FROM [sources] where sourceID >-1
DELETE FROM [derivedfrom]
DELETE FROM [seriescatalog]
-- clean up vocabs
DELETE FROM [methods] where MethodID >0
DELETE FROM [Samples] where SampleID >0
DELETE FROM [offsettypes] where OffsetTypeID >0
DELETE FROM [qualifiers] where qualifierID >0
--Reset the primary key field
DBCC CHECKIDENT (SeriesCatalog, RESEED, 0)
-- DBCC CHECKIDENT (DataValues, RESEED, 0)
-- DBCC CHECKIDENT (Sites, RESEED, 0)
-- DBCC CHECKIDENT (Qualifiers, RESEED, 0)
-- DBCC CHECKIDENT (Variables, RESEED, 0)
END