-- Stored Procedure: dbo.DBConfigSetDefault
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBConfigSetDefault]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DBConfigSetDefault]
GO

CREATE Procedure dbo.DBConfigSetDefault
AS

/******************************************************************************
** File: dbo.DBConfigSetDefault.PRC
** Name: dbo.DBConfigSetDefault
** Desc: defines how the scripting actions will sync with the current  
**            server state
**
** Auth: Bill Wunder
** Date: 7-25-2003
*******************************************************************************
** Change History
*******************************************************************************
** Date:  Author:  Description:
** -------- -------- ---------------------------------------
**    
*******************************************************************************/
declare @er int

SET NOCOUNT ON
set xact_abort on
     
-- add configuration row for any new databases on server
begin tran
insert admin.dbo.ArchUtilChanges
 (SQLServerName
 , VSSItem
 , VSSParent
 , VSSItemType
 , DatabaseName
 , ChangeAction)
select @@servername
 , ''
 , ''
 , ''     
 , d.name
 , 'Add Database Archive Configuration'
from master.dbo.sysdatabases d 
where not exists (select 1
  from admin.dbo.ArchUtilDBConfig
  where DatabaseName = d.name
  and SQLServerName = @@servername)
and d.name  not in ('tempdb', 'pubs', 'NorthWind')

if @@error <> 0
 goto ErrorHandler

insert admin.dbo.ArchUtilDBConfig (DatabaseName)
select d.name 
from master.dbo.sysdatabases d 
where not exists (select 1
  from admin.dbo.ArchUtilDBConfig
  where DatabaseName = d.name
  and SQLServerName = @@servername)
and d.name  not in ('tempdb', 'pubs', 'NorthWind')

if @@error <> 0
 goto ErrorHandler

-- remove configuration row id database is not on server
insert admin.dbo.ArchUtilChanges
 (SQLServerName
 , VSSItem
 , VSSParent
 , VSSItemType
 , DatabaseName
 , ChangeAction)
select @@servername
 , ''
 , ''
 , ''     
 , DatabaseName
 , 'Delete Database Archive Configuration'
from admin.dbo.ArchUtilDBConfig 
where db_id(DatabaseName) is null

if @@error <> 0
 goto ErrorHandler

delete admin.dbo.ArchUtilDBConfig 
where db_id(DatabaseName) is null

if @@error <> 0
 goto ErrorHandler

commit tran

return

ErrorHandler:
 rollback tran
 return -1
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO