If you actually look at the script first, GOOD JOB !!!
Scan for the string "--script edit required"
to find some spots that need a little more info
Take the time to understand the code in these spots
so you can provide the correct information.

/* Microsoft SQL Server - Scripting			*/
/* Server: ?????					*/
/* Database: admin					*/
/* Creation Date 11/4/98 1:05:34 PM 			*/

/****** Object:  Stored Procedure dbo.dbSync    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.dbSync') and sysstat & 0xf = 4)
	drop procedure dbo.dbSync
GO

/****** Object:  Stored Procedure dbo.logSync    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.logSync') and sysstat & 0xf = 4)
	drop procedure dbo.logSync
GO

/****** Object:  Stored Procedure dbo.maintenance    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.maintenance') and sysstat & 0xf = 4)
	drop procedure dbo.maintenance
GO

/****** Object:  Stored Procedure dbo.check_ident    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.check_ident') and sysstat & 0xf = 4)
	drop procedure dbo.check_ident
GO

/****** Object:  Stored Procedure dbo.check_outfiles    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.check_outfiles') and sysstat & 0xf = 4)
	drop procedure dbo.check_outfiles
GO

/****** Object:  Stored Procedure dbo.CheckPULIST    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.CheckPULIST') and sysstat & 0xf = 4)
	drop procedure dbo.CheckPULIST
GO

/****** Object:  Stored Procedure dbo.convert_share_to_drive    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.convert_share_to_drive') and sysstat & 0xf = 4)
	drop procedure dbo.convert_share_to_drive
GO

/****** Object:  Stored Procedure dbo.expunge_users    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.expunge_users') and sysstat & 0xf = 4)
	drop procedure dbo.expunge_users
GO

/****** Object:  Stored Procedure dbo.run_script_remote    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.run_script_remote') and sysstat & 0xf = 4)
	drop procedure dbo.run_script_remote
GO

/****** Object:  Stored Procedure dbo.verify_sysusages    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.verify_sysusages') and sysstat & 0xf = 4)
	drop procedure dbo.verify_sysusages
GO

/****** Object:  Stored Procedure dbo.dbSync_driver    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.dbSync_driver') and sysstat & 0xf = 4)
	drop procedure dbo.dbSync_driver
GO

/****** Object:  Stored Procedure dbo.delete_archive    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.delete_archive') and sysstat & 0xf = 4)
	drop procedure dbo.delete_archive
GO

/****** Object:  Stored Procedure dbo.dumpDatabase    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.dumpDatabase') and sysstat & 0xf = 4)
	drop procedure dbo.dumpDatabase
GO

/****** Object:  Stored Procedure dbo.free_semaphore    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.free_semaphore') and sysstat & 0xf = 4)
	drop procedure dbo.free_semaphore
GO

/****** Object:  Stored Procedure dbo.help_users_all    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.help_users_all') and sysstat & 0xf = 4)
	drop procedure dbo.help_users_all
GO

/****** Object:  Stored Procedure dbo.hold_semaphore    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.hold_semaphore') and sysstat & 0xf = 4)
	drop procedure dbo.hold_semaphore
GO

/****** Object:  Stored Procedure dbo.logSync_driver    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.logSync_driver') and sysstat & 0xf = 4)
	drop procedure dbo.logSync_driver
GO

/****** Object:  Stored Procedure dbo.maintenance_driver    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.maintenance_driver') and sysstat & 0xf = 4)
	drop procedure dbo.maintenance_driver
GO

/****** Object:  Stored Procedure dbo.release_semaphore    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.release_semaphore') and sysstat & 0xf = 4)
	drop procedure dbo.release_semaphore
GO

/****** Object:  Stored Procedure dbo.reset_subsystem    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.reset_subsystem') and sysstat & 0xf = 4)
	drop procedure dbo.reset_subsystem
GO

/****** Object:  Stored Procedure dbo.setLogDump    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.setLogDump') and sysstat & 0xf = 4)
	drop procedure dbo.setLogDump
GO

/****** Object:  Stored Procedure dbo.verify_sync_folders    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.verify_sync_folders') and sysstat & 0xf = 4)
	drop procedure dbo.verify_sync_folders
GO

/****** Object:  Stored Procedure dbo.zip_n_move    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.zip_n_move') and sysstat & 0xf = 4)
	drop procedure dbo.zip_n_move
GO

/****** Object:  Stored Procedure dbo.check_for_blocks    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.check_for_blocks') and sysstat & 0xf = 4)
	drop procedure dbo.check_for_blocks
GO

/****** Object:  Stored Procedure dbo.dispatch    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.dispatch') and sysstat & 0xf = 4)
	drop procedure dbo.dispatch
GO

/****** Object:  Stored Procedure dbo.find_blocker    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.find_blocker') and sysstat & 0xf = 4)
	drop procedure dbo.find_blocker
GO

/****** Object:  Stored Procedure dbo.isAdminUsingDB    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.isAdminUsingDB') and sysstat & 0xf = 4)
	drop procedure dbo.isAdminUsingDB
GO

/****** Object:  Stored Procedure dbo.logSyncThenSetDbToHotSite    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.logSyncThenSetDbToHotSite') and sysstat & 0xf = 4)
	drop procedure dbo.logSyncThenSetDbToHotSite
GO

/****** Object:  Stored Procedure dbo.purgeMSDB    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.purgeMSDB') and sysstat & 0xf = 4)
	drop procedure dbo.purgeMSDB
GO

/****** Object:  Stored Procedure dbo.rQry    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.rQry') and sysstat & 0xf = 4)
	drop procedure dbo.rQry
GO

/****** Object:  Stored Procedure dbo.set_to_hot_site    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.set_to_hot_site') and sysstat & 0xf = 4)
	drop procedure dbo.set_to_hot_site
GO

/****** Object:  Stored Procedure dbo.set_to_load    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.set_to_load') and sysstat & 0xf = 4)
	drop procedure dbo.set_to_load
GO

/****** Object:  Stored Procedure dbo.set_to_primary    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.set_to_primary') and sysstat & 0xf = 4)
	drop procedure dbo.set_to_primary
GO

/****** Object:  Stored Procedure dbo.setAllSearchDBsToTruncLog    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.setAllSearchDBsToTruncLog') and sysstat & 0xf = 4)
	drop procedure dbo.setAllSearchDBsToTruncLog
GO

/****** Object:  Stored Procedure dbo.setSearchDBsToTruncLog    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.setSearchDBsToTruncLog') and sysstat & 0xf = 4)
	drop procedure dbo.setSearchDBsToTruncLog
GO

/****** Object:  Stored Procedure dbo.setDbToHotSite    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.setDbToHotSite') and sysstat & 0xf = 4)
	drop procedure dbo.setDbToHotSite
GO

/****** Object:  Stored Procedure dbo.showInputBuffers    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.showInputBuffers') and sysstat & 0xf = 4)
	drop procedure dbo.showInputBuffers
GO

/****** Object:  Stored Procedure dbo.sp_allspace    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_allspace') and sysstat & 0xf = 4)
	drop procedure dbo.sp_allspace
GO

/****** Object:  Stored Procedure dbo.verify_dump    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.verify_dump') and sysstat & 0xf = 4)
	drop procedure dbo.verify_dump
GO

/****** Object:  Stored Procedure dbo.verify_load    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.verify_load') and sysstat & 0xf = 4)
	drop procedure dbo.verify_load
GO

/****** Object:  Table dbo.databases    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.databases') and sysstat & 0xf = 3)
	drop table dbo.databases
GO

/****** Object:  Table dbo.drivers    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.drivers') and sysstat & 0xf = 3)
	drop table dbo.drivers
GO

/****** Object:  Table dbo.in_use    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.in_use') and sysstat & 0xf = 3)
	drop table dbo.in_use
GO

/****** Object:  Table dbo.logspace    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.logspace') and sysstat & 0xf = 3)
	drop table dbo.logspace
GO

/****** Object:  Table dbo.messenger    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.messenger') and sysstat & 0xf = 3)
	drop table dbo.messenger
GO

/****** Object:  Table dbo.persons    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.persons') and sysstat & 0xf = 3)
	drop table dbo.persons
GO

/****** Object:  Table dbo.PULISTtext    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.PULISTtext') and sysstat & 0xf = 3)
	drop table dbo.PULISTtext
GO

/****** Object:  Table dbo.scripts    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.scripts') and sysstat & 0xf = 3)
	drop table dbo.scripts
GO

/****** Object:  Table dbo.semaphore    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.semaphore') and sysstat & 0xf = 3)
	drop table dbo.semaphore
GO

/****** Object:  Table dbo.servers    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.servers') and sysstat & 0xf = 3)
	drop table dbo.servers
GO

/****** Object:  Table dbo.test    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.test') and sysstat & 0xf = 3)
	drop table dbo.test
GO

/****** Object:  Table dbo.textreader    Script Date: 11/4/98 1:05:37 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.textreader') and sysstat & 0xf = 3)
	drop table dbo.textreader
GO

/****** Object:  Table dbo.databases    Script Date: 11/4/98 1:05:37 PM ******/
CREATE TABLE dbo.databases (
	name varchar (30) NOT NULL ,
	description varchar (255) NULL ,
	compareTableName varchar (30) NULL ,
	CONSTRAINT PK_databases PRIMARY KEY  CLUSTERED 
	(
		name
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.drivers    Script Date: 11/4/98 1:05:37 PM ******/
CREATE TABLE dbo.drivers (
	name varchar (30) NOT NULL ,
	description varchar (255) NULL ,
	distributedFlag varchar (5) NULL ,
	CONSTRAINT PK_drivers PRIMARY KEY  CLUSTERED 
	(
		name
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.in_use    Script Date: 11/4/98 1:05:38 PM ******/
CREATE TABLE dbo.in_use (
	dbName varchar (30) NOT NULL ,
	server varchar (30) NOT NULL ,
	process varchar (30) NULL ,
	start_time datetime NOT NULL CONSTRAINT DF__in_use__start_ti__22DF33C0 DEFAULT (getdate()),
	CONSTRAINT PK_in_use PRIMARY KEY  CLUSTERED 
	(
		dbName,
		server
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.logspace    Script Date: 11/4/98 1:05:38 PM ******/
CREATE TABLE dbo.logspace (
	dbname char (30) NOT NULL ,
	logsize numeric(5, 2) NOT NULL ,
	spaceused numeric(10, 8) NOT NULL ,
	status bit NOT NULL ,
	date datetime NOT NULL CONSTRAINT DF_logspace_date DEFAULT (getdate()),
	CONSTRAINT PK_logspace PRIMARY KEY  CLUSTERED 
	(
		dbname,
		date
	) WITH  FILLFACTOR = 90
)
GO

/****** Object:  Table dbo.messenger    Script Date: 11/4/98 1:05:38 PM ******/
CREATE TABLE dbo.messenger (
	id int IDENTITY (1, 1) NOT NULL ,
	spid smallint NULL ,
	info varchar (100) NULL ,
	CONSTRAINT PK_messenger PRIMARY KEY  CLUSTERED 
	(
		id
	) WITH  FILLFACTOR = 90
)
GO

/****** Object:  Table dbo.persons    Script Date: 11/4/98 1:05:38 PM ******/
CREATE TABLE dbo.persons (
	firstName varchar (20) NOT NULL ,
	lastName varchar (20) NOT NULL ,
	role char (10) NOT NULL ,
	homePhone varchar (15) NULL ,
	pager varchar (15) NULL ,
	emailAddress varchar (50) NULL ,
	CONSTRAINT PK_persons PRIMARY KEY  CLUSTERED 
	(
		lastName,
		firstName,
                role
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.PULISTtext    Script Date: 11/4/98 1:05:38 PM ******/
CREATE TABLE dbo.PULISTtext (
	text varchar (100) NOT NULL 
)
GO

/****** Object:  Table dbo.scripts    Script Date: 11/4/98 1:05:39 PM ******/
CREATE TABLE dbo.scripts (
	ID int IDENTITY (1, 1) NOT NULL ,
	name varchar (30) NOT NULL ,
	line varchar (100) NOT NULL ,
	CONSTRAINT PK_scripts PRIMARY KEY  CLUSTERED 
	(
		name,
		ID
	) WITH  FILLFACTOR = 90
)
GO

/****** Object:  Table dbo.semaphore    Script Date: 11/4/98 1:05:39 PM ******/
CREATE TABLE dbo.semaphore (
	name varchar (30) NOT NULL ,
	status tinyint NOT NULL ,
	CONSTRAINT PK_semaphore PRIMARY KEY  CLUSTERED 
	(
		name
	) WITH  FILLFACTOR = 90
)
GO

/****** Object:  Table dbo.servers    Script Date: 11/4/98 1:05:39 PM ******/
CREATE TABLE dbo.servers (
	name varchar (30) NOT NULL ,
	description varchar (100) NULL ,
	IPaddr char (15) NULL ,
	dumpShare varchar (10) NULL ,
	dumpDrive char (1) NULL ,
	dumpFolder varchar (30) NULL ,
	loadShare varchar (10) NULL ,
	loadDrive char (1) NULL ,
	loadFolder varchar (30) NULL ,
	CONSTRAINT PK_servers PRIMARY KEY  CLUSTERED 
	(
		name
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.test    Script Date: 11/4/98 1:05:39 PM ******/
CREATE TABLE dbo.test (
	text varchar (100) NOT NULL 
)
GO

/****** Object:  Table dbo.textreader    Script Date: 11/4/98 1:05:39 PM ******/
CREATE TABLE dbo.textreader (
	text varchar (100) NOT NULL 
)
GO

/****** Object:  Stored Procedure dbo.check_for_blocks    Script Date: 11/4/98 1:05:40 PM ******/
CREATE PROCEDURE check_for_blocks
    @taskName VARCHAR(30) = "Block Monitor",
    @userName VARCHAR(30) = " ",
    @mailTo VARCHAR(30) = " "
AS
/* see if any searchers are blocked */
DECLARE @CMD VARCHAR(255),
        @CMDstub VARCHAR(255), 
        @CMDstatus INT,
        @localCMDstatus INT,
        @spid SMALLINT,
        @dbid SMALLINT,
        @blocker SMALLINT,
        @blockersuid SMALLINT,
        @myName VARCHAR(50)      

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

If (@username = ' ') 
  SELECT @username = 'sa'

If (@mailto = ' ')
  SELECT @mailto = (SELECT emailAddress FROM persons WHERE role='DBA')

/* only allow one instance of the block monitor to run */
IF (SELECT COUNT(*) 
    FROM master..sysprocesses 
    WHERE spid <> @@SPID
    AND program_name LIKE ("SQLEXEC - TSQL Task #%")
    AND CONVERT(INT,SUBSTRING(RTRIM(program_name), 
                CHARINDEX("#", program_name) + 1, 
                DATALENGTH(program_name) - CHARINDEX("#", program_name))) 
                IN (SELECT id FROM msdb..systasks
                    WHERE name = (@taskName))) = 0
  BEGIN
    /* get the process id and user id for the first block encountered */
    SELECT @spid = spid, @dbid = dbid, @blocker = blocked
    FROM master..sysprocesses
    WHERE blocked <> 0 
    AND suid = SUSER_ID(@userName) 
    /* if no blocks, we're done */
    IF @spid IS NOT NULL
      BEGIN      
        /* wait five seconds, if still blocked take action */
        WAITFOR DELAY "00:00:10"        
        SELECT @spid = spid, @dbid = dbid, @blocker = blocked
        FROM master..sysprocesses
        WHERE blocked <> 0 
        AND suid = SUSER_ID(@userName) 
        IF @spid IS NOT NULL
          BEGIN
            SELECT @blockersuid = suid FROM master..sysprocesses WHERE spid = @blocker
            SELECT @CMDstatus = 0  
            SELECT @CMDstub = "Login " + UPPER(@UserName) + " (spid " + CONVERT(VARCHAR(3),@spid) + 
                              ") blocked in database " + UPPER(DB_NAME(@dbid)) + " by login " + 
                              UPPER(SUSER_NAME(@blockersuid)) + " (spid " + CONVERT(VARCHAR(3),@blocker) 
                              + "). [admin.check_for_blocks] " + CONVERT(VARCHAR(20), getdate()) 
             
            SELECT @CMD = "net send " + @@SERVERNAME + @CMDstub
            EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output
            IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 
            SELECT @CMD = "net send /USERS " + @CMDstub
            EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output
            /* send to individual machines or users too ! 
            IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 
            SELECT @CMD = "net send mike " + @CMDstub
            EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output
            IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 
            SELECT @CMD = "net send roy " + @CMDstub
            EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output
            IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 
            */
            IF (@CMDstatus <> 0) -- failed
              BEGIN
                SELECT @CMD = CONVERT(CHAR(1),@CMDStatus) + 
                              " block alert network broadcast messages FAILED!" + 
                              @myName
                RAISERROR(@CMD,1,2) WITH LOG
              END
            /* send a report of the blocking hierarchy */
            EXEC master..xp_sendmail @recipients = @mailTo, 
                                     @subject = 'Blocking Report',  
                                     @message = @CMDstub,
                                     @query = 'admin..find_blocker'         
            /* wait five minutes so the block doesn't flood the network with messages */ 
            WAITFOR DELAY "00:05:00"        
          END -- send messages
      END  -- blocks found
  END  -- block monitor already running
EXEC msdb..sp_purgehistory @taskName
GO

/****** Object:  Stored Procedure dbo.dispatch    Script Date: 11/4/98 1:05:40 PM ******/
CREATE PROCEDURE dispatch
  	@CMD VARCHAR(255)   = NULL,
        @caller VARCHAR(50) = NULL, 
        @debug VARCHAR(5)   = 'false',
	@mailto VARCHAR(50) = ' '
AS
  /* Invoke the command line. If the command fails, notify any
     designate(s) by email and network message. The default behavior 
     of the function is to place invalid command line strings into an 
     email message. Use this behavoir to email admin info message strings.*/
  DECLARE @CMDstatus int,
          @localCMDstatus int 	
  If (@mailto = ' ')
    SELECT @mailto = (SELECT emailAddress FROM persons WHERE role='DBA')
  IF @debug = 'false'
    EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output
  ELSE
    BEGIN
      SELECT "**** dispatch command from " + @caller + " ****" 
      SELECT getdate()
      SELECT @CMD
      EXEC @CMDstatus = master..xp_cmdshell @CMD
      SELECT "Command return status: ",@CMDstatus
      IF (@CMDstatus <> 0) -- failed
        BEGIN
            BEGIN 
              SELECT "**** dispatch failed ****"
              SELECT @CMD
              SELECT @CMD = 'The ' + @caller + ' process failed at :' + @CMD +
                            '. Status:'+ CONVERT(VARCHAR(2),@CMDstatus) + ' (' + 
                            CONVERT(VARCHAR(20),getdate())+ ' admin..dispatch)'
              EXEC master..xp_sendmail @mailto , @subject="admin..dispatch ALERT!", @message=@CMD
              RAISERROR(@CMD,1,2) WITH LOG
            END
        END
    END
  RETURN @CMDstatus
GO

/****** Object:  Stored Procedure dbo.find_blocker    Script Date: 11/4/98 1:05:40 PM ******/
CREATE PROCEDURE find_blocker
AS
/* Query to find spids at head of a blocking chain, their input buffers, 
   and the type of blocking locks they hold */
DECLARE @blocker_spid smallint, 
        @spid SMALLINT, 
        @i_buff_string char(30),
        @CMD VARCHAR(255) 
SET NOCOUNT ON
/* Get all blocked spids */
SELECT DISTINCT spid,
                suid,
                dbid,  
                blocked 
INTO #blk 
FROM master..sysprocesses (NOLOCK) 
WHERE blocked <> 0
OR (spid IN (SELECT blocked FROM master..sysprocesses where blocked <> 0)
    AND blocked = 0) 
/* list all blocked spids */
SELECT "Blocked Processes"
SELECT spid as "BLOCKED SPID", 
       SUSER_NAME(suid) as "LOGIN NAME", 
       DB_NAME(dbid) as "DB NAME", 
       blocked as "BLOCKING SPID"
       FROM #blk
       WHERE blocked <> 0 
/* dump all input buffers for spids that are blocked */
SELECT "Last Command from Blocked Processes"
SELECT @spid = (SELECT MIN(spid) FROM #blk WHERE blocked <> 0)
WHILE @spid IS NOT NULL
  BEGIN
    SELECT @CMD = 'SELECT "BLOCKED SPID ' + CONVERT(VARCHAR(3),@spid) + '"' +
              " DBCC INPUTBUFFER(" + CONVERT(VARCHAR(3),@spid) + ") " 
    EXEC (@CMD)
    SELECT @spid = (SELECT MIN(spid) FROM #blk
                    WHERE spid > @spid
                    AND blocked <> 0)
  END
/* For each spid at the head of a blocking chain 
   print the type of blocking locks it holds */
/* dump all input buffers for spids that are blocked */
SELECT "Locks held by involved processes"
SELECT spid, 
       l.type, 
       locktype = name, 
       table_id = id, 
       page, 
       dbid 
FROM master..syslocks l, master.dbo.spt_values v 
WHERE l.type = v.number 
AND v.type='L' 
AND (l.type & 256)=256 
AND spid IN (SELECT blocked FROM #blk) 
ORDER BY spid 
/* delete all blocking spids except blockers */
DELETE FROM #blk 
WHERE blocked <> 0 
/* get each spid from sysprocesses which is referenced in 
   the "blocked" column of #blk. This should be the head 
   of each blocking chain */
SELECT "Blocking Process(es)"
SELECT "BLOCKED SPID"=spid, 
       "LOGIN NAME"=SUSER_NAME(suid), 
       "DB NAME"=DB_NAME(dbid), 
       "BLOCKING SPID"=blocked FROM #blk
       WHERE blocked = 0 
/* For each spid at the head of a blocking chain 
   print its input buffer to show what query it's running */
SELECT "  Last Command from Blocking Processes"
SELECT @spid = (SELECT MIN(spid) FROM #blk)
WHILE @spid IS NOT NULL
  BEGIN
    SELECT @CMD = 'SELECT "BLOCKED SPID ' + CONVERT(VARCHAR(3),@spid) + '"' +
              " DBCC INPUTBUFFER(" + CONVERT(VARCHAR(3),@spid) + ") " 
    EXEC (@CMD)
    SELECT @spid = (SELECT MIN(spid) FROM #blk
                    WHERE spid > @spid)
  END
DROP table #blk

GO

/****** Object:  Stored Procedure dbo.isAdminUsingDB    Script Date: 11/4/98 1:05:40 PM ******/
CREATE PROCEDURE isAdminUsingDB
     @dbName VARCHAR(30) = ' ',
     @source VARCHAR(30) = ' ',
     @target VARCHAR(30) = ' ',
     @debug VARCHAR(5) = 'false'
AS
DECLARE @dbSyncTask VARCHAR(50),
        @logSyncTask VARCHAR(50),
        @maintTask VARCHAR(50),
        @val INT,
        @spid SMALLINT,
        @CMD VARCHAR(255),
        @myName VARCHAR(50)         
/* 
  Procedure isAdminUSingDB will return zero if there are no 
  competing admin processes currently active that use the 
  specified database on the source or target server. Can't use
  systasks, because a hard failure can render this info corrupt.
*/
SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

SELECT @dbSyncTask = "dbSync " + @dbName + "%"
SELECT @logSyncTask = "logSync " + @dbName + "%"
SELECT @maintTask = "Maintain " + @source + '.' + @dbName + "%"
/* check for a t-SQL competitor */
SELECT @val = (SELECT count(*) FROM master..sysprocesses 
               WHERE spid <> @@SPID
               AND program_name LIKE ("SQLEXEC - TSQL Task #%")
               AND CONVERT(INT,SUBSTRING(RTRIM(program_name), 
                   CHARINDEX("#", program_name) + 1, 
                   DATALENGTH(program_name) - CHARINDEX("#", program_name))) 
                 IN (SELECT id FROM msdb..systasks
                     WHERE (    name LIKE (@dbSyncTask)
                            AND (   CHARINDEX(@source, name) > 0
                                 OR CHARINDEX(@target, name) > 0))
                     OR    (    name LIKE (@logSyncTask)
                            AND (   CHARINDEX(@source, name) > 0
                                 OR CHARINDEX(@target, name) > 0))
                     OR name LIKE (@maintTask)))
IF (@val <> 0) 
  BEGIN
    /* wait 2 seconds and recheck - could be that another job 
       submitted this task and it hasn't cleaned up yet */
    WAITFOR DELAY "00:00:02"
    SELECT @val = (SELECT count(*) FROM master..sysprocesses 
                   WHERE spid <> @@SPID
                   AND program_name LIKE ("SQLEXEC - TSQL Task #%")
                   AND CONVERT(INT,SUBSTRING(RTRIM(program_name), 
                       CHARINDEX("#", program_name) + 1, 
                       DATALENGTH(program_name) - CHARINDEX("#", program_name))) 
                     IN (SELECT id FROM msdb..systasks
                         WHERE (    name LIKE (@dbSyncTask)
                                AND (   CHARINDEX(@source, name) > 0
                                     OR CHARINDEX(@target, name) > 0))
                         OR    (    name LIKE (@logSyncTask)
                                AND (   CHARINDEX(@source, name) > 0
                                     OR CHARINDEX(@target, name) > 0))
                         OR name LIKE (@maintTask)))
  END
IF (@val = 0) 
  BEGIN
    /* still could be a CMDEXEC competitor */
    IF (SELECT count(*) FROM master..sysprocesses
        WHERE program_name = ("ISQL-32")
        AND spid <> @@SPID) <> 0
      BEGIN
        /* wait 2 seconds and recheck - could be that another job 
           submitted this task and it hasn't cleaned up yet */
        WAITFOR DELAY "00:00:02"
        IF (SELECT count(*) FROM master..sysprocesses
            WHERE program_name = ("ISQL-32")
            AND spid <> @@SPID) <> 0
          BEGIN
            /* temp table needs to allow nulls in case a spid that
            qualified for the cursor ends before the count*/ 
            CREATE TABLE #string(name VARCHAR(255) NULL)
            DECLARE CmdExec_cursor CURSOR
            FOR
            SELECT spid FROM master..sysprocesses
            WHERE program_name = "ISQL-32"
            AND spid <> @@SPID  
            OPEN CmdExec_cursor
            FETCH NEXT FROM CmdExec_cursor INTO @spid
            WHILE (@@fetch_status <> -1)
              BEGIN
                SELECT @CMD = "DBCC INPUTBUFFER(" + CONVERT(VARCHAR(6), @spid) + ")"              
                INSERT #string EXEC(@CMD)
  	        FETCH NEXT FROM CmdExec_cursor INTO @spid
              END
            CLOSE CmdExec_cursor
            DEALLOCATE CmdExec_cursor 
            SELECT @val = (SELECT count(*) FROM #string  
                           WHERE (    name LIKE (@dbSyncTask)
                                  AND (   CHARINDEX(@source, name) > 0
                                       OR CHARINDEX(@target, name) > 0))
                           OR    (    name LIKE (@logSyncTask)
                                  AND (   CHARINDEX(@source, name) > 0
                                       OR CHARINDEX(@target, name) > 0))
                           OR name LIKE (@maintTask))    
            DROP TABLE #string
        END 
     END
  END
IF @debug = 'true'
  BEGIN
   SELECT "stored procedure ",@myName
   SELECT "number of competing processes = ", @val
  END
RETURN @val
GO

/****** Object:  Stored Procedure dbo.logSyncThenSetDbToHotSite    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE logSyncThenSetDbToHotSite
  @dbName VARCHAR(30) = ' '
AS
EXEC logSync @dbName
EXEC setDbToHotSite

GO

/****** Object:  Stored Procedure dbo.purgeMSDB    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROC purgeMSDB 
   @source VARCHAR(30) = ' ',
   @sourcePassword VARCHAR(30) = ' ',
   @DeleteBeforeDate VARCHAR(30) = '1/1/1970',
   @debug VARCHAR(5) = 'false' 
   WITH RECOMPILE
   AS
  DECLARE @CMD VARCHAR(255),     -- reusable command holder 
          @CMDstatus INT,         -- return status of command prompt  
          @myName VARCHAR(50) 

  SET NOCOUNT ON

  SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

  SELECT @CMDstatus = 0

  IF (@source = ' ') 
    BEGIN
      SELECT @CMD = "Invalid parameter list provided for procedure " + @myName
      RAISERROR(@CMD,1,1) WITH SETERROR
      RETURN 1
    END

  /* default to keep 10 days of history */
  IF (@DeleteBeforeDate = '1/1/1970')
     SELECT @DeleteBeforeDate = DATEADD(dd,-10,getdate())

  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"' + "dump tran msdb with truncate_only" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus

  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"' + "sp_configure 'allow updates',1" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus

    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                  ' -dmaster -Q"reconfigure with override"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus

      SELECT @CMD = 'DELETE msdb.dbo.sysbackupdetail WHERE backup_id IN ' +
                    '(SELECT backup_id FROM msdb.dbo.sysbackuphistory ' + 
                    "WHERE backup_start <= '" + @DeleteBeforeDate + "')"
      SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                    ' -dmaster -Q"' + @CMD + '"'
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus

      SELECT @CMD = "DELETE msdb.dbo.sysbackuphistory WHERE backup_start <= '" +
                    @DeleteBeforeDate + "'"
      SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                    ' -dmaster -Q"' + @CMD + '"'
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus

      SELECT @CMD = 'DELETE msdb.dbo.sysrestoredetail WHERE restore_id IN ' +
                    '(SELECT restore_id FROM msdb.dbo.sysrestorehistory ' + 
                    "WHERE backup_start <=  '" + @DeleteBeforeDate + "')"
      SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                    ' -dmaster -Q"' + @CMD + '"'
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus

      SELECT @CMD = "DELETE msdb.dbo.sysRestorehistory WHERE backup_start <= '" +
                    @DeleteBeforeDate + "'"
      SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                    ' -dmaster -Q"' + @CMD + '"'
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus

    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                  ' -dmaster -Q"' + "sp_configure 'allow updates',0" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus

  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"reconfigure with override"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus


GO

/****** Object:  Stored Procedure dbo.rQry    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE rQry
  @CMD VARCHAR(255)
AS
  EXEC (@CMD)

GO

/****** Object:  Stored Procedure dbo.set_to_hot_site    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE set_to_hot_site
     @dbName VARCHAR(30) = ' ',              -- the database name on both systems
     @target VARCHAR(30) = ' ',              -- the source server name
     @targetPassword VARCHAR(30) = ' ',      -- sa password on source
     @debug VARCHAR(5) = 'false'             -- debug this procedure 
AS
DECLARE 
     @CMD VARCHAR(255),                      -- reusable command holder 
     @CMDstatus INT,                         -- return status of command prompt  
     @myName VARCHAR(50)                     -- name of this function 

IF (@dbName <> ' ') AND (@target <> ' ') 
  BEGIN    

    SET NOCOUNT ON

    SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

    IF DB_ID(@dbName) < 6
      BEGIN
        SELECT @CMD = @myName + ' cannot be used on ' + @dbName + 
                      '. Only user databases can be safely moved via dump/load.'
        RAISERROR(@CMD,1,1) WITH SETERROR
        RETURN 1
      END
    /*
    make sure the target database is read only 
    (or not a permanent data storage location),
    has checkpoint on recovery disabled,
    has select into/bulkcopy disabled,
    has truncate log on checkpoint disabled,  
    */
    IF @dbName IN ('Put',                     --script edit requited
                   'your','volitile',         --script edit required
                   'data','databases',        --script edit required
                   'here', 'to' ,'allow',     --script edit required
                   'bcp')                     --script edit required 
      BEGIN
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'read only',false" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'select into/bulkcopy',true" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'trunc. log on chkpt.',true" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END
    ELSE  -- all other dbs
      BEGIN
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'read only',true" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'select into/bulkcopy',false" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'trunc. log on chkpt.',false" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END
  END
ELSE
  BEGIN
    PRINT "  usage: set_to_hot_site"
    PRINT "                [@dbname         = <name of db to be replicated >         ],"
    PRINT "                [@target         = <target SQL Server's name>             ]," 
    PRINT "                [@targetPassword = <sa password on the target SQL Server> ],"
    PRINT "                [@debug          = <debug mode flag - default is false>   ],"
    RETURN 1
  END
RETURN
GO

/****** Object:  Stored Procedure dbo.set_to_load    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE set_to_load
     @dbName VARCHAR(30) = ' ',              -- the database name on both systems
     @target VARCHAR(30) = ' ',              -- the source server name
     @targetPassword VARCHAR(30) = ' ',      -- sa password on source
     @debug VARCHAR(5) = 'false'             -- debug this procedure 
AS
DECLARE 
     @CMD VARCHAR(255),                      -- reusable command holder 
     @CMDstatus INT,                         -- return status of command prompt  
     @myName VARCHAR(50)                     -- name of this function 

IF     (@dbName <> ' ') AND (@target <> ' ')
  BEGIN    
    SET NOCOUNT ON

    SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

    IF DB_ID(@dbName) < 6
      BEGIN
        SELECT @CMD = @myName + ' cannot be used on ' + @dbName + 
                      '. Only user databases can be safely moved via dump/load.'
        RAISERROR(@CMD,1,1) WITH SETERROR
        RETURN 1
      END
  /*
    make sure the target database is not read only 
    has checkpoint on recovery disabled
    has select into/bulkcopy disabled
    has truncate log on checkpoint disabled  
    and has minimum log space
  */
  SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                ",'read only',false" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
/*
  SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                ' -dmaster -Q"' + 'DUMP TRANSACTION ' + @dbName + 
                ' with TRUNCATE_ONLY' + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
*/
  SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                ",'select into/bulkcopy',false" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                ",'trunc. log on chkpt.',false" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                ",'no chkpt on recovery',true" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  END
ELSE
  BEGIN
    PRINT "  usage: set_to_load"
    PRINT "                [@dbname         = <name of the database to be replicated >    ],"
    PRINT "                [@target         = <target SQL Server's name>                  ]," 
    PRINT "                [@targetPassword = <sa password on the target SQL Server>      ],"
    PRINT "                [@debug          = <debug mode flag - default is false>        ],"
    RETURN 1
  END
RETURN
GO

/****** Object:  Stored Procedure dbo.set_to_primary    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE set_to_primary
     @dbName VARCHAR(30) = ' ',              -- the database name on both systems
     @source VARCHAR(30) = ' ',              -- the source server name
     @sourcePassword VARCHAR(30) = ' ',      -- sa password on source
     @debug VARCHAR(5) = 'false'             -- debug this procedure 
AS
DECLARE 
     @CMD VARCHAR(255),                      -- reusable command holder 
     @CMDstatus INT,                         -- return status of command prompt  
     @myName VARCHAR(50)                     -- name of this function 

IF     (@dbName <> ' ') AND (@source <> ' ')
  BEGIN   
    SET NOCOUNT ON

    SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

    IF DB_ID(@dbName) < 6
      BEGIN
        SELECT @CMD = @myName + ' cannot be used on ' + @dbName + 
                      '. Only user databases can be safely moved via dump/load.'
        RAISERROR(@CMD,1,1) WITH SETERROR
        RETURN 1
      END
 
    /*
    make sure the source database has select into/bulkcopy disabled
    truncate log on checkpoint disabled (except this admin db)  
    and no checkpoint on recovery enabled
    */
    IF @dbName = 'admin'
      BEGIN
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'select into/bulkcopy',true" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'trunc. log on chkpt.',true" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END
    ELSE
      BEGIN
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'select into/bulkcopy',false" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                      ",'trunc. log on chkpt.',false" + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                  ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                  ",'no chkpt on recovery',true" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
  END
ELSE
  BEGIN
    PRINT "  usage: set_to_primary"
    PRINT "                [@dbname         = <name of the database to be replicated >    ],"
    PRINT "                [@source         = <source SQL Server's name>                  ]," 
    PRINT "                [@sourcePassword = <sa password on the source SQL Server>      ],"
    PRINT "                [@debug          = <debug mode flag - default is false>        ],"
    RETURN 1
  END
RETURN
GO

/****** Object:  Stored Procedure dbo.setAllSearchDBsToTruncLog    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE setAllSearchDBsToTruncLog
AS
DECLARE @CMD VARCHAR(255),
        @CMDstatus int,
        @target VARCHAR(30),
        @targetId INT,
        @myName VARCHAR(50)

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

/* set the local transient data stores to trunc. log on chkpt */
EXEC admin.dbo.setSearchDBsToTruncLog
/* set all remote transient data stores to trunc. log on chkpt */
SELECT @targetID = (SELECT MIN(srvid) 
                    FROM master..sysservers
                    WHERE srvid > 0
                    AND srvstatus = 1)
SELECT @target = (SELECT srvname 
                  FROM master..sysservers
                  WHERE srvid = @targetId)
WHILE @targetID IS NOT NULL
  BEGIN
    SELECT @CMD = @target + ".admin.dbo.setSearchDBsToTruncLog"
    EXEC (@CMD)                
    SELECT @targetID = (SELECT MIN(srvid) 
                        FROM master..sysservers
                        WHERE srvid > @targetId
                        AND srvstatus = 1)
    IF @targetId IS NOT NULL
      SELECT @target = (SELECT srvname 
                        FROM master..sysservers
                        WHERE srvid = @targetId)
  END
GO

/****** Object:  Stored Procedure dbo.setSearchDBsToTruncLog    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE setSearchDBsToTruncLog
AS
DECLARE @dbid INT,
        @CMD VARCHAR(255),
        @CMDstatus int

SET NOCOUNT ON

SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases
              WHERE name LIKE "%SP" 
              OR name LIKE "%RES")
WHILE @dbid IS NOT NULL
  BEGIN
    SELECT @CMD = 'sp_dboption ' + DB_NAME(@dbid) + ',"trunc. log on chkpt.",true'    
    EXEC (@CMD)
    SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases
                    WHERE dbid > @dbid
                    AND (   name LIKE "%SP" 
                         OR name LIKE "%RES"))
  END
GO

/****** Object:  Stored Procedure dbo.setDbToHotSite    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE setDbToHotSite
     @dbName VARCHAR(30) = NULL,          -- the database name on both systems
     @target VARCHAR(30) = 'HS_LEIN',     -- the target server name
     @targetPassword VARCHAR(30) = ' ',   -- sa password on target
     @debug VARCHAR(5) = 'false'          -- debug flag 
AS
DECLARE 
    @CMD VARCHAR(255),                    -- working var to build and pass strings
    @CMDstatus INT,                       -- working var for return status of an @CMD 
    @myName VARCHAR(50)                   -- name of this function

IF @dbName IS NOT NULL
  BEGIN
    /*
    Procedure will dump a database and restore the dump to a 
    hot backup destination. Both the source and target databases
    will be configured to support an async log dump and restore
    process. All dumps will be archived for the source and target
    systems.
    */
    SET NOCOUNT ON

    SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)
 
    /*
    make sure the target database is read/write 
    has checkpoint on recovery enabled
    has select into/bulkcopy disabled
    has truncate log on checkpoint disabled  
    and has log space
    */
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                  ",'read only',false" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'DUMP TRANSACTION ' + @dbName + 
                  ' with TRUNCATE_ONLY' + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                  ",'select into/bulkcopy',false" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                  ",'trunc. log on chkpt.',false" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                  ",'no chkpt on recovery',false" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    /* 
    Turn off all tasks that init logSync or dbSync to this system 
    */
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + "sp_configure 'allow updates',1" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"reconfigure with override"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = "UPDATE msdb.dbo.systasks SET enabled = 0 " +
                  "WHERE name LIKE 'logSync%' " +
                  "OR name LIKE 'dbSync%'" 
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                   ' -dmaster -Q"' + @CMD + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + "sp_configure 'allow updates',0" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"reconfigure with override"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
  END
GO

/****** Object:  Stored Procedure dbo.showInputBuffers    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE showInputBuffers
AS
DECLARE @spid SMALLINT,
        @CMD VARCHAR(50)

SET NOCOUNT ON

SELECT @spid = (SELECT MIN(spid) from master..sysprocesses
                WHERE spid > 4)
WHILE @spid IS NOT NULL
  BEGIN
    SELECT 'spid: ',@spid
    SELECT @CMD = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(5), @spid) + ')'
    EXEC (@CMD) 
    SELECT @spid = (SELECT MIN(spid) from master..sysprocesses
                    WHERE spid > @spid)
  END
GO

/****** Object:  Stored Procedure dbo.sp_allspace    Script Date: 11/4/98 1:05:41 PM ******/
create procedure sp_allspace
-- IMPORTANT! usage info. should be updated before running me.
-- Empty tables are ommitted from the results. 
as

declare @objname varchar(92)		-- The object we want size on.
declare @id	int			-- The object id of @objname.
declare @type	smallint		-- The object type.
declare	@pages	int			-- Working variable for size calc.
declare @dbname varchar(30)
declare @dbsize dec(15,0)

declare @trows dec(15,0)
declare @tall dec(15,0)
declare @tdata dec(15,0)
declare @tidx dec(15,0)
declare @tuu dec(15,0)

declare @srows dec(15,0)
declare @sall dec(15,0)
declare @sdata dec(15,0)
declare @sidx dec(15,0)
declare @suu dec(15,0)

declare @irows dec(15,0)
declare @iall dec(15,0)
declare @idata dec(15,0)
declare @iidx dec(15,0)
declare @iuu dec(15,0)
declare @iname varchar(20)

declare @msg varchar(80)

declare AllTables insensitive cursor
for
select name from sysobjects order by name

open AllTables

/*
**  We need to create a temp table to do the calculation.
**  reserved: sum(reserved) where indid in (0, 1, 255)
**  data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
**  indexp: sum(used) where indid in (0, 1, 255) - data
**  unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
	name 		varchar(92),
	rows		int null,
	reserved	dec(15) null,
	data		dec(15) null,
	indexp		dec(15) null,
	unused		dec(15) null
)
set nocount on
select getdate()

fetch next from AllTables into @objname

/*
**  Insert the row for the table
*/

insert into #spt_space (name,rows,reserved,data,indexp,unused)
		values ("zzUser Table Totals",0,0,0,0,0)
insert into #spt_space (name,rows,reserved,data,indexp,unused)
		values ("zzSystem Table Totals",0,0,0,0,0)

while @@fetch_status = 0
begin

	/*
	**  Find the object.
	*/
	select @id = null
	select @id = id, @type = sysstat & 0xf
		from sysobjects
		where id = object_id(@objname)

	/*
	**  See if it's a space object.
	**  types are:
	**	1 - system table
	**	2 - view
	**	3 - user table
	**	4 - sproc
	**	6 - default
	**	7 - rule
	**	8 - trigger
	**	9 - primary key
	**	10 - check constraint
	**	11 - foreign key
	**	12 - replication filter stored proc
	*/

	if @type in (1,3)
	begin
		/*
		**  Insert the row for the table
		*/
		insert into #spt_space (name)
			values (@objname)
		/*
		**  Now calculate the summary data.
		**  reserved: sum(reserved) where indid in (0, 1, 255)
		*/
		/* insert into #spt_space (reserved) */
		select @iall = sum(reserved)
			from sysindexes
			where indid in (0, 1, 255)
				and id = @id
		update #spt_space 
			set reserved =  @iall
			where name = @objname
		/* Update Totals */
		if @type = 3 
			update #spt_space
				set reserved = reserved + @iall
				where name = "zzUser Table Totals"
		else
			update #spt_space
				set reserved = reserved + @iall
				where name = "zzSystem Table Totals"

		/*
		** data: sum(dpages) where indid < 2
		**	+ sum(used) where indid = 255 (text)
		*/
		select @pages = sum(dpages)
			from sysindexes
			where indid < 2 and id = @id

		select @pages = @pages + isnull(sum(used), 0)
			from sysindexes
			where indid = 255 and id = @id
		update #spt_space
			set data = @pages
			where name = @objname
		/* Update Totals */
		if @type = 3 
			update #spt_space
				set data = data + @pages
				where name = "zzUser Table Totals"
		else
			update #spt_space
				set data = data + @pages
				where name = "zzSystem Table Totals"
	
	
		/* index: sum(used) where indid in (0, 1, 255) - data */
		select @iidx = sum(used) 
			from sysindexes
			where indid in (0, 1, 255) and id = @id		
		update #spt_space
			set indexp = @iidx - data
			where name = @objname
		/* Update Totals */
		if @type = 3 
			update #spt_space
				set indexp = indexp + (@iidx - data)
				where name = "zzUser Table Totals"
		else
			update #spt_space
				set indexp = indexp + (@iidx - data)
				where name = "zzSystem Table Totals"
	

		/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
		update #spt_space
			set unused = reserved - @iidx
			where name = @objname
		/* Update Totals */
		if @type = 3 
			update #spt_space
				set unused = unused + (reserved - @iidx)
				where name = "zzUser Table Totals"
		else
			update #spt_space
				set unused = unused + (reserved	- @iidx)
				where name = "zzSystem Table Totals"

		select @irows = i.rows
			from sysindexes i
			where i.indid < 2 and i.id = @id
		update #spt_space
			set rows = @irows
			where name = @objname
		/* Update totals */
		if @type = 3 
			update #spt_space
				set rows = rows + @irows
				where name = "zzUser Table Totals"
		else
			update #spt_space
				set rows = rows + @irows
				where name = "zzSystem Table Totals"	
	end
	fetch next from AllTables into @objname
end
/* end while loop here and print out totals */
select "Table" = substring(#spt_space.name, 1, 20),
	"Rows" =  str(rows,12),
	"Reserved KB" = (str(reserved * d.low / 1024.,15)),
	"Data Space KB" = (str(data * d.low / 1024.,15)),
	"Index Space KB" = (str(indexp * d.low / 1024.,15)),
	"Unused Space KB" = (str(unused * d.low / 1024.,15))
from #spt_space, master.dbo.spt_values d
	where (d.number = 1 and d.type = 'E')
		and rows <> 0  -- do not show empty tables
order by #spt_space.name

close AllTables
deallocate AllTables

return (0)



GO

/****** Object:  Stored Procedure dbo.verify_dump    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE verify_dump
        @source VARCHAR(30) = ' ',
        @device VARCHAR(80) = ' ',
        @found INT OUTPUT, 
	@debug VARCHAR(5) = 'false'
AS
DECLARE @CMDstatus INT,
        @timeWindowStart VARCHAR(20), 
 	@myName VARCHAR(50)

IF (@source = ' ') OR (@device = ' ')  RETURN 1

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

IF @debug = 'true'
  BEGIN
    SELECT '@myName: ', @myName 
    SELECT '@source: ', @source
    SELECT '@device: ', @device
  END  	 	
IF @source = @@SERVERNAME
  BEGIN
    /* already dumped in last hour or so minutes? */
    SELECT @timeWindowStart = DATEADD(minute, -59, GETDATE())
    SELECT @found = (SELECT COUNT(d.backup_id)
                     FROM msdb..sysbackupdetail d, msdb..sysbackuphistory h 
                     WHERE d.device_name = @device 
                     AND d.backup_id = h.backup_id 
                     AND h.backup_finish > @timeWindowStart)
    RETURN
  END 
--script edit required
IF (@source = 'servername1') EXEC servername1.admin.dbo.verify_dump @source,
                                                            @device,
                                                            @found OUTPUT, 
	                                                    @debug
--script edit required
IF (@source = 'servername2') EXEC servername2.admin.dbo.verify_dump @source,
                                                      @device,
                                                      @found OUTPUT, 
	                                              @debug
--script edit required
IF (@source = 'servername3') EXEC servername3.admin.dbo.verify_dump @source,
                                                      @device,
                                                      @found OUTPUT, 
                                                      @debug
--script edit required
--and so on (no way to get the remote exec to work without hard coding :( )
--I need to rewrite to use run_script_remote and select from servers
IF (@source = 'servername4') EXEC servername4.admin.dbo.verify_dump @source,
                                                    @device,
                                                    @found OUTPUT, 
	                                            @debug
    IF (@debug='true') SELECT "@found = ",@found
GO

/****** Object:  Stored Procedure dbo.verify_load    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE verify_load
        @target VARCHAR(30) = ' ',
        @device VARCHAR(80) = ' ',
        @found INT OUTPUT, 
	@debug VARCHAR(5) = 'false'
AS
DECLARE @CMDstatus INT,
        @timeWindowStart VARCHAR(20), 
 	@myName VARCHAR(50)

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

IF (@debug='true')
  BEGIN
    SELECT "INPUT PARMS FOR ", @myName
    SELECT "Current server: ", @@SERVERNAME  
    SELECT "target: ",@target
    SELECT "device: ",@device
  END

IF (@target = ' ') OR (@device = ' ') RETURN 1

SELECT @device = "%" + SUBSTRING(@device, DATALENGTH(@device) - 20 , 21)   

IF @target = @@SERVERNAME
  BEGIN
    SELECT @myName = 'admin.verify_load'
    /* in last 30 minutes? */
    SELECT @timeWindowStart = DATEADD(minute, -40, GETDATE())
    SELECT @found = (SELECT COUNT(d.restore_id)
                     FROM msdb..sysrestoredetail d, msdb..sysrestorehistory h 
                     WHERE d.device_name LIKE @device 
                     AND d.restore_id = h.restore_id 
                     AND h.restore_date > @timeWindowStart)
    RETURN
  END 
--script edit required
IF (@target = 'servername1') EXEC servername1.admin.dbo.verify_dump @target,
                                                            @device,
                                                            @found OUTPUT, 
	                                                    @debug
--script edit required
IF (@target = 'servername2') EXEC servername2.admin.dbo.verify_dump @target,
                                                      @device,
                                                      @found OUTPUT, 
	                                              @debug
--script edit required
IF (@target = 'servername3') EXEC servername3.admin.dbo.verify_dump @target,
                                                      @device,
                                                      @found OUTPUT, 
                                                      @debug
--script edit required
--and so on (no way to get the remote exec to work without hard coding :( )
--I need to rewrite to use run_script_remote and select from servers
IF (@target = 'servername4') EXEC servername4.admin.dbo.verify_dump @target,
                                                    @device,
                                                    @found OUTPUT, 
	                                            @debug
IF (@debug='true')
  BEGIN
    SELECT "INTERNAL VARS FOR ", @myName
    SELECT "Current server: ", @@SERVERNAME
    SELECT "parsed device: ",@device
    SELECT "found: ",@found
  END
GO

/****** Object:  Stored Procedure dbo.dbSync_driver    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE dbSync_driver
    @dbName VARCHAR(30) = ' ',
    @source VARCHAR(30) = ' ',
    @target VARCHAR(30) = ' ',
    @debug VARCHAR(50) = 'false'
as
DECLARE @procedure VARCHAR(30),
        @CMD VARCHAR(255),
        @CMDstatus INT,
        @myName VARCHAR(30)
 
SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID)
SELECT @procedure = 'dbSync'

INSERT in_use VALUES (@dbName, @source, @procedure, getdate())

SELECT @CMDstatus = @@ERROR

IF (@CMDstatus=0)
  BEGIN
    INSERT in_use VALUES (@dbName, @target, @procedure , getdate())

    SELECT @CMDstatus = @@ERROR
  END

IF (@CMDstatus = 0) 
  BEGIN
    SELECT @CMD = @procedure + ' ' + @dbName + ', ' + @source + ', ' + @target + ', ' + '@debug=' + @debug

    EXEC (@CMD)

    SELECT @CMDstatus = @@ERROR
    IF @CMDstatus <> 0
      SELECT "return code: ",@CMDstatus

    DELETE in_use 
    WHERE dbName = @dbName
    AND (server = @source OR server = @target)
    AND process = @procedure         

  END
ELSE
  BEGIN
    SELECT @CMD = "An unexpected error occurred while marking the database as 'in use'! (CMDstatus = " +
                   CONVERT(VARCHAR(5), @CMDstatus) + ", "  + @myName + ")"  
    IF (@CMDstatus = 2627)
      SELECT @CMD = 'An adminstrative subsystem process is already '
                    + 'active in database ' + @dbName + ' on server '
                    + @source  
                    + '. Only one admin process may be active '
                    + 'in any database at any time.('
                    + @myName + ')'  
    RAISERROR(@CMD,1,1) WITH SETERROR
    RETURN 1
  END
GO

/****** Object:  Stored Procedure dbo.delete_archive    Script Date: 11/4/98 1:05:41 PM ******/
CREATE PROCEDURE delete_archive
     @archive VARCHAR(80) = ' ',
     @debug VARCHAR(5) = 'false'      
AS
DECLARE @myName VARCHAR(50),
        @CMD VARCHAR(255),
        @CMDstatus INT
         
/*
  maintain the archive 
  remove files with extension of .zzz 
  if .zip exist. 
  rename files with extension of .zip to .zzz
*/   
SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

SELECT @CMD = 'dir ' + @archive + '\*.zip'
EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output
IF (@CMDstatus = 0) -- found
  BEGIN
    SELECT @CMD = 'dir ' + @archive + '\*.zzz'
    EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output
    IF (@CMDstatus = 0) -- found
      BEGIN
        SELECT @CMD = 'del ' + @archive + '\*.zzz'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      END
    SELECT @CMD = 'ren ' + @archive + '\*.zip '+ '*.zzz'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  END  
RETURN
GO

/****** Object:  Stored Procedure dbo.dumpDatabase    Script Date: 11/4/98 1:05:42 PM ******/
CREATE PROCEDURE dumpDatabase
     @db VARCHAR(30)= ' ',
     @server VARCHAR(30) = ' ',
     @Password VARCHAR(30) = ' ',
     @Folder VARCHAR(30) = ' ',
     @drive VARCHAR(5) = ' ',
     @debug VARCHAR(5) = 'false'
AS
DECLARE @CMD VARCHAR(255),
        @CMDstatus INT,
        @dumpName VARCHAR(80),
        @myName VARCHAR(50)

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)
 
IF (@db = ' ') OR (@server = ' ') OR (@Folder = ' ') OR (@drive = ' ')  
  RETURN 1

/* dump the database's log on the source system */
SELECT @CMD = 'isql -Usa -P' + @Password + ' -S' + @server + 
              ' -dmaster -Q"' + 'DUMP TRANSACTION ' + @db + 
              ' with TRUNCATE_ONLY' + '"'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) RETURN @CMDstatus

/* don't need to dump transient dbs */
--script edit required
IF (@db NOT LIKE "%Res") OR (@db = "exceptThisRes") 
  BEGIN
    SELECT @dumpName =  @drive +  ':\' + @Folder + '\' + @db 
                        + '\' + SUBSTRING(@db,1,4) +"dump.DAT" 
    SELECT @CMD = 'isql -Usa -P' + @Password + ' -S' + @server + 
                  ' -dmaster -Q"DUMP DATABASE ' + @db + 
                  " TO DISK = '" + @dumpName +
                  "' WITH INIT, RETAINDAYS = 0" + '"' 
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* make sure the dump was logged to msdb */
    EXEC verify_dump @server, 
                     @dumpName,
                     @CMDstatus OUTPUT,
                     @debug
    IF (@debug = 'true') SELECT "No of dumps found = ", @CMDstatus
    IF (@CMDstatus = 0) 
      BEGIN
        SELECT @CMD = 'Dump of database ' + @db  
                      + ' not found in msdb..sysbackuphistory on ' 
                      + @server + '. The ' + @myName 
                      + ' process has been aborted.'
        RAISERROR(@CMD,1,1) WITH SETERROR
        RETURN 1
      END
    IF (@CMDstatus > 1) 
      BEGIN
        SELECT @CMD = 'Unintended dumps of database ' + @db  
                      + ' on server ' + @server 
                      + ' may have occurred in the last 30 minutes. (' 
                      + @myName + ')'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      END
  END -- do not dump transient db
ELSE
    IF (@debug='true') SELECT "This transient db will not be dumped to disk"    

RETURN
GO

/****** Object:  Stored Procedure dbo.free_semaphore    Script Date: 11/4/98 1:05:42 PM ******/
CREATE PROCEDURE free_semaphore
AS
/* restart recovery task to clean up falsly held semaphores */
UPDATE semaphore
SET status = 0
GO

/****** Object:  Stored Procedure dbo.help_users_all    Script Date: 11/4/98 1:05:42 PM ******/
CREATE PROCEDURE help_users_all 
     @saPassword VARCHAR(20) = ' '       
AS
DECLARE @dbid INT,
        @drive CHAR(1),
        @folder VARCHAR(50), 
 	@lastdbid INT, 
        @CMD VARCHAR(255),
        @CMDstatus INT,
        @myName VARCHAR(50)

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID)

SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases) 

/* build an outfile path for this server */
SELECT @drive = dumpDrive, @folder = dumpFolder
FROM admin..servers
WHERE name = @@SERVERNAME

SELECT @CMD = "dir " + @drive + ":\" + @folder +
              "\master\help_users_all.out"
EXEC @CMDstatus = dispatch @CMD, @myName
IF (@CMDstatus = 0) 
  BEGIN
    SELECT @CMD = "del " + @drive + ":\" + @folder +
                  "\master\help_users_all.out"
    EXEC @CMDstatus = dispatch @CMD, @myName
    IF (@CMDstatus <> 0) return @CMDstatus
  END

WHILE @dbid IS NOT NULL
  BEGIN
    SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME + 
                  ' -Q"SELECT dbName=' + "'" + DB_NAME(@dbid) + "' USE " + DB_NAME(@dbid) + 
                  +' EXEC sp_helpuser">>' + @drive + ':\' + @folder +
                  '\master\help_users_all.out'
    EXEC @CMDstatus = dispatch @CMD, @myName
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @lastdbid = @dbid
    SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases
                    WHERE dbid > @lastdbid) 
  END

GO

/****** Object:  Stored Procedure dbo.hold_semaphore    Script Date: 11/4/98 1:05:42 PM ******/
CREATE PROCEDURE hold_semaphore
     @name VARCHAR(30) = 'textreader'
AS
  /* wait until the requested semaphore 
     is available then grab it */
SET NOCOUNT ON
WHILE (SELECT status FROM semaphore
       WHERE name = @name) = 1
  BEGIN
    WAITFOR DELAY "00:00:10"    
  END
  UPDATE semaphore
  SET status = 1
  WHERE name = @name
  AND status = 0

GO

/****** Object:  Stored Procedure dbo.logSync_driver    Script Date: 11/4/98 1:05:42 PM ******/
CREATE PROCEDURE logSync_driver
    @dbName VARCHAR(30) = ' ',
    @source VARCHAR(30) = ' ',
    @target VARCHAR(30) = ' ',
    @debug VARCHAR(50) = 'false'
AS
DECLARE @procedure VARCHAR(30),
        @CMD VARCHAR(255),
        @CMDstatus INT,
        @myName VARCHAR(30)
 
SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID)
SELECT @procedure = 'logSync'

INSERT in_use VALUES (@dbName, @source, @procedure, getdate())

SELECT @CMDstatus = @@ERROR

IF (@CMDstatus=0)
  BEGIN
    INSERT in_use VALUES (@dbName, @target, @procedure , getdate())

    SELECT @CMDstatus = @@ERROR
  END

IF (@CMDstatus = 0) 
  BEGIN
    SELECT @CMD = @procedure + ' ' + @dbName + ', ' + @source + ', ' + @target + ', ' + '@debug=' + @debug

    EXEC (@CMD)

    SELECT @CMDstatus = @@ERROR
    IF @CMDstatus <> 0
      SELECT "return code: ",@CMDstatus

    DELETE in_use 
    WHERE dbName = @dbName
    AND (server = @source OR server = @target)
    AND process = @procedure         

  END
ELSE
  BEGIN

    SELECT @CMD = "An unexpected error occurred while marking the database as 'in use'! (CMDstatus = " +
                   CONVERT(VARCHAR(5), @CMDstatus) + ", "  + @myName + ")"  

    IF (@CMDstatus = 2627)
      SELECT @CMD = 'An adminstrative subsystem process is already '
                    + 'active in database ' + @dbName + ' on server '
                    + @source  
                    + '. Only one admin process may be active '
                    + 'in any database at any time.('
                    + @myName + ')'  
    RAISERROR(@CMD,1,1) WITH SETERROR
    RETURN 1

  END
GO

/****** Object:  Stored Procedure dbo.maintenance_driver    Script Date: 11/4/98 1:05:42 PM ******/
CREATE PROCEDURE maintenance_driver
    @dbName VARCHAR(30) = ' ',
    @source VARCHAR(30) = ' ',
    @debug VARCHAR(50) = 'false'
as
DECLARE @procedure VARCHAR(30),
        @CMD VARCHAR(255),
        @CMDstatus INT,
        @myName VARCHAR(30)
 
SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID)

SELECT @procedure='maintenance'

INSERT in_use VALUES (@dbName, @source, @procedure, getdate())

SELECT @CMDstatus = @@ERROR

IF (@CMDstatus = 0) 
  BEGIN
    SELECT @CMD = @procedure + ' ' + @dbName + ', ' + @source + ', ' + '@debug=' + @debug

    EXEC (@CMD)

    SELECT @CMDstatus = @@ERROR
    IF @CMDstatus <> 0
      SELECT "return code: ",@CMDstatus

    DELETE in_use 
    WHERE dbName = @dbName
    AND server = @source
    AND process = @procedure         

  END
ELSE
  BEGIN

    SELECT @CMD = "An unexpected error occurred while marking the database as 'in use'! (CMDstatus = " +
                   CONVERT(VARCHAR(5), @CMDstatus) + ", "  + @myName + ")"  

    IF (@CMDstatus = 2627)
      SELECT @CMD = 'An adminstrative subsystem process is already '
                    + 'active in database ' + @dbName + ' on server '
                    + @source  
                    + '. Only one admin process may be active '
                    + 'in any database at any time.('
                    + @myName + ')'  
    RAISERROR(@CMD,1,1) WITH SETERROR
    RETURN 1

  END
GO

/****** Object:  Stored Procedure dbo.release_semaphore    Script Date: 11/4/98 1:05:43 PM ******/
CREATE PROCEDURE release_semaphore
     @name VARCHAR(30) = 'textreader'
AS
  /* release the specified semaphore */
SET NOCOUNT ON
  UPDATE semaphore
  SET status = 0
  WHERE name = @name
  AND status = 1

GO

/****** Object:  Stored Procedure dbo.reset_subsystem    Script Date: 11/4/98 1:05:43 PM ******/
CREATE PROCEDURE reset_subsystem AS
UPDATE admin.dbo.semaphore
SET status=0
DELETE admin.dbo.in_use
GO

/****** Object:  Stored Procedure dbo.setLogDump    Script Date: 11/4/98 1:05:43 PM ******/
CREATE PROCEDURE setLogDump
    @dbName VARCHAR(30) = ' ',
    @dumpPath VARCHAR(50) = ' ',            -- fully qualified dump path
    @loadPath VARCHAR(50) = ' ',            -- fully qualified load path 
    @name VARCHAR(30) = ' ' OUTPUT,            
    @debug VARCHAR(5) = 'false'
AS
DECLARE 
    @CMD VARCHAR(255),                      -- reusable command holder 
    @CMDstatus INT,                         -- return status of command prompt  
    @localCMDstatus INT,                         -- return status of command prompt  
    @dumpDevice VARCHAR(80),                -- fully qualified dump device
    @dbDumpName VARCHAR(20),                -- database dump file name
    @dbDumpTemp VARCHAR(20),                -- database dump file name
    @loadDevice VARCHAR(80),                -- fully qualified load device
    @currentDate DATETIME,                  -- used to make dump file name
    @dumpSeq TINYINT,                       -- used to make dump file name 
    @myName VARCHAR(50)                     -- this procedures name  

/* find a dump file name that is not in use. 
   Look in the dump and load folders.
   This will support up to 253 log dump files
   only the first 99 will be 8.3 file name */

IF (@dbName = ' ') OR (@dumpPath = ' ') OR (@loadPath = ' ') RETURN 1

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

SELECT @dbDumpName =  SUBSTRING(@dbName,1,4) +"dump.DAT" 
SELECT @dbDumpTemp =  SUBSTRING(@dbName,1,4) +"dump.TMP" 
SELECT @currentDate = (SELECT getdate())
SELECT @CMDstatus = 0
SELECT @dumpSeq = 1
WHILE (@CMDstatus = 0) AND (@dumpSeq < 254) -- success
  BEGIN -- look in the dump directory 
    SELECT @name = CONVERT(varchar(2), datepart(month,@currentDate)) + 
                   CONVERT(varchar(2), datepart(day,@currentDate)) + 
                   SUBSTRING(CONVERT(char(4), datepart(year,@currentDate)),3,2) + 
                   CONVERT(varchar(3), @dumpSeq) + ".DAT" 
    SELECT @dumpDevice = @dumpPath + '\' + @name
    SELECT @CMD = 'dir ' + @dumpdevice
    EXEC @CMDstatus=master..xp_cmdshell @CMD, no_output
    IF (@CMDstatus <> 0) -- did not find it
      BEGIN -- look in load folder
        SELECT @loadDevice = @loadPath + '\' + @name
        SELECT @CMD = 'dir ' + @loaddevice
        EXEC @CMDstatus=master..xp_cmdshell @CMD, no_output
        IF (@CMDstatus = 0) -- found it
          SELECT @dumpSeq = @dumpSeq + 1 -- try next name
        ELSE
          BEGIN
            IF (@dumpseq = 1) -- remove previous log dumps
              BEGIN 
                SELECT @CMD = 'dir ' + @dumpPath + '\' + @dbDumpName
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'ren ' + @dumpPath + '\' + @dbDumpName + ' ' + @dbDumpTemp
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 
                SELECT @CMD = 'dir ' + @dumpPath + '\*.DAT'
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'del ' + @dumpPath + '\*.DAT' 
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 
                SELECT @CMD = 'dir ' + @dumpPath + '\*.OUT'
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'del ' + @dumpPath + '\*.OUT' 
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 
                SELECT @CMD = 'dir ' + @dumpPath + '\' + @dbDumpTemp
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'ren ' + @dumpPath + '\' + @dbDumpTemp + ' ' + @dbDumpName
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 
                SELECT @CMD = 'dir ' + @loadPath + '\' + @dbDumpName
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'ren ' + @loadPath + '\' + @dbDumpName + ' ' + @dbDumpTemp
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 
                SELECT @CMD = 'dir ' + @loadPath + '\*.DAT'
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'del ' + @loadPath + '\*.DAT' 
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 
                SELECT @CMD = 'dir ' + @loadPath + '\' + @dbDumpTemp
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'ren ' + @loadPath + '\' + @dbDumpTemp + ' ' + @dbDumpName
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 
              END                              
          END
      END  
    ELSE -- found this file in the dump archive folder
        SELECT @dumpSeq = @dumpSeq + 1 -- try next name      
    IF @dumpSeq > 254
      BEGIN
        SELECT @CMD = "Unable to create a unique log dump name. Cannot continue. (" 
                      + @myName + ")"
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus = 0) RETURN @CMDstatus
        RETURN 99
      END
  END -- get the next dump file name
RETURN
GO

/****** Object:  Stored Procedure dbo.verify_sync_folders    Script Date: 11/4/98 1:05:43 PM ******/
CREATE PROCEDURE verify_sync_folders
     @dbName VARCHAR(30) = ' ',
     @devicePath VARCHAR(50) = ' ',
     @deviceShare VARCHAR(80) = ' ',
     @deviceFolder VARCHAR(30) = ' ',
     @archive VARCHAR(80) = ' ',
     @serverName VARCHAR(30) = ' ',
     @debug VARCHAR(5) = 'false'      
AS
DECLARE @myName VARCHAR(50),
        @CMD VARCHAR(255),
        @CMDstatus INT

IF (@debug='true')
  BEGIN
    SELECT "verify_sync_folders INPUT PARMS:" 
    SELECT "dbName: ",@dbName
    SELECT "devicePath: ",@devicePath
    SELECT "deviceShare: ",@deviceShare
    SELECT "deviceFolder: ",@deviceFolder
    SELECT "archive: ",@archive
    SELECT "serverName: ",@serverName
  END
         
IF (@dbName = ' ') OR (@devicePath = ' ') OR (@deviceShare = ' ') 
OR (@deviceFolder = ' ') OR (@archive = ' ') OR (@serverName = ' ')
  RETURN 1

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

SELECT @CMD = 'dir ' + @devicePath
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) -- failed
  BEGIN
    SELECT @CMD = 'mkdir ' + @devicePath + '\'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus
    /* move a pkzip and pkunzip into the dump dump path */
    SELECT @CMD = 'copy \\' + @serverName + '\' + @deviceShare + '\' + 
                  @deviceFolder + '\pkzip.exe \\' + @serverName + '\' + 
                  @deviceShare + '\' + @deviceFolder + '\' + @dbName 
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus
    SELECT @CMD = 'copy \\' + @serverName + '\' + @deviceShare + '\' + 
                  @deviceFolder + '\pkunzip.exe \\' + @serverName + '\' + 
                  @deviceShare + '\' + @deviceFolder + '\'+ @dbName 
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus
  END -- dump path does not exist
/* make sure an archive path exists for this database */
SELECT @CMD = 'dir ' + @archive
EXEC @CMDstatus = dispatch @CMD, @myName, @debug
IF (@CMDstatus <> 0) -- not found
  BEGIN   
    SELECT @CMD = 'mkdir ' + @archive + '\'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus
  END
RETURN
GO

/****** Object:  Stored Procedure dbo.zip_n_move    Script Date: 11/4/98 1:05:43 PM ******/
CREATE PROCEDURE zip_n_move
     @dbName VARCHAR(30),           -- the database name on both systems
     @fileName VARCHAR(30),         -- the file to be moved (less extension)
     @source VARCHAR(30),           -- the source server name
     @target VARCHAR(30),           -- the target server name
     @sourcePassword VARCHAR(30),   -- sa password on source
     @targetPassword VARCHAR(30),   -- sa password on target
     @saPassword VARCHAR(30),       -- admin database sa password
     @debug VARCHAR(5)              -- debug flag
 AS
DECLARE 
     @sourceIP CHAR(15),                  
     @dumpShare VARCHAR(10),             
     @dumpDrive CHAR(1),                  
     @dumpFolder VARCHAR(30), 
     @dumpArchive VARCHAR(80),
     @targetIP CHAR(15),      
     @loadShare VARCHAR(10),
     @loadDrive CHAR(1),
     @loadFolder VARCHAR(30),
     @loadArchive VARCHAR(80),
     @CMD VARCHAR(255),
     @CMDstatus INT,
     @drive varchar(5),
     @myName VARCHAR(30)

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

/* load server info into work variables */
SELECT @sourceIP=IPaddr, 
       @dumpShare=dumpShare, 
       @dumpDrive=dumpDrive,
       @dumpFolder=dumpFolder
FROM servers WHERE name = @source

SELECT @targetIP = IPaddr,
       @loadShare=loadShare,
       @loadDrive=loadDrive,
       @loadFolder=loadFolder 
FROM servers where name = @target

/* build some IP based UNS paths for network copy calls */
SELECT @dumpArchive = '\\' + RTRIM(@sourceIP) + '\' + @dumpShare + '\' + 
                   @dumpFolder + '\' + @dbName + '\archive' 
SELECT @loadArchive = '\\' + RTRIM(@targetIP) + '\' + @loadShare + '\' + 
                   @loadFolder + '\' + @dbName + '\archive'

IF @debug = 'true'
  BEGIN
    SELECT "dbName: ",@dbName 
    SELECT "fileName: ", @fileName
    SELECT "source: ", @source
    SELECT "sourcePassword: ", @sourcePassword
    SELECT "dumpShare: ", @dumpShare
    SELECT "dumpDrive: ", @dumpDrive
    SELECT "dumpFolder: ", @dumpFolder
    SELECT "target: ", @target
    SELECT "targetPassword: ", @targetPassword
    SELECT "loadShare: ", @loadShare
    SELECT "loadDrive: ", @loadDrive
    SELECT "loadFolder: ", @loadFolder
    SELECT "saPassword: ", @saPassword
  END

/* zip the dump file into the source system archive folder */
IF @fileName = @dbName
  SELECT @CMD = @dumpDrive + ':&cd ' + @dumpfolder + '\' + @dbName + '&pkzip ' +
                'archive\' + SUBSTRING(@fileName,1,4) + 'dump.zip ' + 
                SUBSTRING(@fileName,1,4) + 'dump.dat -b' + @dumpDrive
ELSE
  SELECT @CMD = @dumpDrive + ':&cd ' + @dumpfolder + '\' + @dbName + '&pkzip ' +
                'archive\' + @fileName + '.zip ' + 
                 @fileName + '.dat -b' + @drive
SELECT @CMD = "master..xp_cmdshell '" + @CMD + "'" 
SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source +
              ' -dmaster -Q"' + @CMD + '"'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug 
IF (@CMDstatus <> 0) return @CMDstatus

/* copy the zip file to the target system */
IF @fileName = @dbName
  SELECT @CMD = 'copy ' + 
                @dumpArchive + '\' + SUBSTRING(@fileName,1,4) + 'dump.zip ' +
                @loadArchive + '\' + SUBSTRING(@fileName,1,4) + 'dump.zip '
ELSE
  SELECT @CMD = 'copy ' + 
                @dumpArchive + '\' + @fileName + '.zip ' +
                @loadArchive + '\' + @fileName + '.zip'
If @debug = 'true' select @CMD
EXEC @CMDstatus = dispatch @CMD, @myName, @debug 
IF (@CMDstatus <> 0) return @CMDstatus

/* unzip the dump file into the target system restore folder */
IF @fileName = @dbName
  SELECT @CMD = @loaddrive + ':&cd ' + @loadfolder + '\' + @dbName + '&pkunzip -o ' +
                'archive\' + SUBSTRING(@fileName,1,4) + 'dump.zip '
ELSE
  SELECT @CMD = @loadDrive + ':&cd ' + @loadfolder + '\' + @dbName + '&pkunzip -o ' +
                'archive\' + @fileName + '.zip'
SELECT @CMD = "master..xp_cmdshell '" + @CMD + "'" 
SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target +
              ' -dmaster -Q"' + @CMD + '"'
EXEC @CMDstatus = dispatch @CMD, @myName, @debug 
IF (@CMDstatus <> 0) return @CMDstatus

RETURN
GO

/****** Object:  Stored Procedure dbo.check_ident    Script Date: 11/4/98 1:05:43 PM ******/
CREATE PROCEDURE check_ident 
     @dbName VARCHAR(30) = NULL,
     @saPassword VARCHAR(20) = ' '       
AS
DECLARE @dbid INT,
 	@lastdbid INT, 
        @CMD VARCHAR(255),
        @CMDstatus INT,
        @myName VARCHAR(50)

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

SELECT @saPassword = ' '
IF @dbName IS NULL -- do em all
  BEGIN
    SELECT @dbid = 1
    SELECT @lastdbid = 5 -- skip the system dbs
  END
ELSE
  SELECT @dbid = DB_ID(@dbName)
WHILE @dbid IS NOT NULL
  BEGIN
    IF @dbName IS NULL -- do em all
      SELECT @dbid = (SELECT MIN(dbid) FROM master..sysdatabases
                      WHERE dbid > @lastdbid) 
    SELECT @CMD = "del C:\check_ident_" + DB_NAME(@dbid) + ".*"
    EXEC @CMDstatus = dispatch @CMD, @myName
    IF (@CMDstatus <> 0) return @CMDstatus
    EXEC hold_semaphore 'textreader'
      TRUNCATE TABLE textreader
      SELECT count(*) FROM textreader WHERE 1=2
      INSERT textreader
      VALUES("USE " + DB_NAME(@dbid))
      INSERT textreader
      SELECT line 
      FROM scripts 
      WHERE name = 'fix_all_ident'
      ORDER BY ID
      SELECT @CMD = 'bcp admin..textreader out ' +
                    'C:\check_ident_' + DB_NAME(@dbid) + '.sql -c -Usa -P' + 
                    @saPassword + ' -S' + @@SERVERNAME
      EXEC @CMDstatus = dispatch @CMD, @myName
      TRUNCATE TABLE textreader
      IF (@CMDstatus <> 0) return @CMDstatus
     EXEC release_semaphore 'textreader' 
    SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME + 
                  ' -dmaster -i"C:\check_ident_' + DB_NAME(@dbid) + '.sql"' +
                  ' >> C:\\check_ident_' + DB_NAME(@dbid) + '.out -n'
    EXEC @CMDstatus = dispatch @CMD, @myName
    IF (@CMDstatus <> 0) return @CMDstatus
    IF @dbName IS NULL
      SELECT @lastdbid = @dbid
    ELSE
      SELECT @dbid = NULL 
  END
GO

/****** Object:  Stored Procedure dbo.check_outfiles    Script Date: 11/4/98 1:05:44 PM ******/
CREATE PROCEDURE check_outfiles
    @dbName VARCHAR(30) = ' ',       -- the database name
    @source VARCHAR(30) = ' ',       -- SQL Server name
    @path VARCHAR(50) = ' ',         -- fully qualified output path    
    @mailto VARCHAR(30),             -- valid account in same Exchange Server
    @saPassword VARCHAR(30),         -- sa password for admin server
    @debug VARCHAR(5)                -- debug codependant
AS
DECLARE @CMD VARCHAR(255),     -- reusable command holder 
        @CMDstatus INT,        -- return status of command prompt
        @mailSubject VARCHAR(255), 
	@myName VARCHAR(50),   -- this stored procedure
        @outFile VARCHAR(80)   -- working var for qualified file name
/*
   Procedure check_outfiles looks for errors in the out files by 
   parsing the maintenance output files for specific words associated
   with problems in the database. Designated user(s) are notified by 
   email only if problems are found
*/
IF (@dbName = ' ') OR (@source = ' ') OR (@path = ' ')
  RETURN 1

  SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

  DELETE messenger where spid = @@SPID
  SELECT @outfile = @path + '\ck_outfiles.results'
  SELECT @CMD = 'del ' + @outfile
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  SELECT @CMD = 'echo The maintenance output files have been reviewed for problems.>' + @outfile
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  SELECT @CMD = 'echo Review all files listed below and resolve all exceptions>>' + @outfile
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  SELECT @CMD = 'findstr "dbprocess Level error:" ' + @path + '\*.out>>' + @outfile
  EXEC @CMDstatus = master..xp_cmdshell @CMD
  /* put an explaination header in the messenger file */
  INSERT messenger 
  SELECT @@SPID, '-   DATABASE: ' + @dbName 
  INSERT messenger 
  SELECT @@SPID, '-   SOURCE SYSTEM: ' + @source
  INSERT messenger 
  SELECT @@SPID, '-   OUTFILE PATH: ' + @path
  EXEC hold_semaphore 'textreader'
    TRUNCATE TABLE textreader
      SELECT @CMD = 'bcp admin..textreader in ' + @path +
                    '\ck_outfiles.results -c -Usa -P' + 
                    @saPassword + ' -S' + @source 
      EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output
      IF (select count(*) from textreader) > 2
        BEGIN
          INSERT messenger SELECT @@SPID, text FROM textreader
          SELECT @mailSubject = 'Maintenance problems identified! database: ' 
                                + @dbName + ' server: ' + @source 
          TRUNCATE TABLE textreader
          DELETE messenger where spid = @@SPID
          SELECT @CMD = 'SELECT info FROM admin.dbo.messenger WHERE info ' + 
                        'IS NOT NULL AND spid = ' + 
                        CONVERT(VARCHAR(10), @@SPID)
          EXEC master.dbo.xp_sendmail @recipients = @mailto,
                                      @subject = @mailSubject,
                                      @query = @CMD
        END
    TRUNCATE TABLE textreader
  EXEC release_semaphore 'textreader' 
  DELETE messenger where spid = @@SPID
RETURN
GO

/****** Object:  Stored Procedure dbo.CheckPULIST    Script Date: 11/4/98 1:05:44 PM ******/
CREATE PROCEDURE CheckPULIST
     @server VARCHAR(30) = ' ',
     @sendTo VARCHAR(255) = '/USERS',         -- domain user(s) or computer name(s) 
     @saPassword VARCHAR(30) = ' ',
     @debug VARCHAR(5) = 'false'
AS
DECLARE 
     @CMD VARCHAR(255),                      -- reusable command holder 
     @CMDstatus INT,                         -- return status of command prompt  
     @found INT,                             -- number of Dr Watsons found
     @singleSendTo VARCHAR(15),              -- working var if multiple send tos 
     @myName VARCHAR(50)                     -- name of this function 

/* 
Check processes on web server. If Dr Watson is running, tell somebody. 
*/

SET NOCOUNT ON

IF (@server = ' ') SELECT @server = @@SERVERNAME

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

/* load the pulist into a table and scan for Dr Watson */
EXEC hold_semaphore 'PULISTtext' 
  TRUNCATE TABLE PULISTtext
    /* run a pulist to get active processes on server */ 
    SELECT @CMD = "cd c:\ntreskit&pulist " + @server
    INSERT PULISTtext EXEC master..xp_cmdshell @CMD
    SELECT @found= (SELECT COUNT(*) FROM PULISTtext WHERE text like '%DRWTSN32%')      
  TRUNCATE TABLE PULISTtext
EXEC release_semaphore 'PULISTtext' 

/* send message if Dr Watson found */
IF (@found <> 0) 
  BEGIN
    IF (CHARINDEX(" ", @sendto)>0)
      BEGIN    
        WHILE (CHARINDEX(" ",@sendto) > 0)
          BEGIN
            SELECT @singleSendTo = SUBSTRING(@sendto, 1, CHARINDEX(" ", @sendto) - 1)
            SELECT @sendto = SUBSTRING(@sendto, CHARINDEX(" ", @sendto) + 1, DATALENGTH(@sendto) - (CHARINDEX(" ", @sendto))) 
            SELECT @CMD = "net send " + @singleSendto + " Dr Watson is running on " + @server + " !"
            EXEC @CMDstatus = dispatch @CMD, @myName, @debug
            IF (@CMDstatus <> 0) return @CMDstatus
          END
      END
    /* get the last one or only one */
    SELECT @CMD = "net send " + @sendto + " Dr Watson is running on " + @server + " !"
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
  END

GO

/****** Object:  Stored Procedure dbo.convert_share_to_drive    Script Date: 11/4/98 1:05:44 PM ******/
CREATE PROCEDURE convert_share_to_drive
     @share VARCHAR(30) = ' ', 
     @drive VARCHAR(5) = ' ' OUTPUT, 
     @path VARCHAR(50) = ' ',
     @source VARCHAR(30) = ' ',
     @sourcePassword VARCHAR(30) = ' ',
     @saPassword VARCHAR(30) = ' ',
     @debug VARCHAR(5) = 'false'
AS
DECLARE @CMD VARCHAR(255),
        @CMDstatus INT,
        @myName VARCHAR(50)
/*
  Procedure convert_share_to_drive will return a logical 
  drive name for a valid network share name.
*/
IF    (@share = ' ') 
   OR (@path = ' ') 
   OR (@source = ' ')
  RETURN 1

  SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

  SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                ' -dmaster -Q"master..xp_cmdshell ' + "'net share " + 
                @share + ">" + @path + "\convert.share'" + '"'
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  EXEC hold_semaphore 'textreader'
    TRUNCATE TABLE textreader
      SELECT @CMD = 'bcp admin..textreader in ' + @path + 
                    '\convert.share -c -Usa -P' + @saPassword + 
                    ' -S' + @@SERVERNAME
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus = 0)
        BEGIN
      IF (@debug='true')
        BEGIN
          SELECT "net use info"
          SELECT * from textreader
        END

          SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                        ' -dmaster -Q"master..xp_cmdshell ' + "'del " + 
                        @path + "\convert.share'" + '"'
          EXEC @CMDstatus = dispatch @CMD, @myName, @debug
          SELECT @drive = SUBSTRING((SELECT DISTINCT RTRIM(LTRIM(SUBSTRING(text,
                                     CHARINDEX('path',text) + DATALENGTH('path'), 
                                     DATALENGTH(text)))) 
                                     FROM textreader WHERE text LIKE 'PATH%'),1,2)
        END
      IF (@debug='true')
        BEGIN
          SELECT RTRIM(@myName) + " DEBUG INFO! -->" + 
                 " share = " + @share + " -->" +
                 " drive = " + @drive + " -->" +
                 " path = " + @path + " -->" +
                 " source = " + @source + " -->" +
                 " sourcePassword = " + @sourcePassword + " -->" +
                 " saPassword = " + @saPassword + " --> END" 
          SELECT * from textreader
        END
    TRUNCATE TABLE textreader
  EXEC release_semaphore 'textreader' 
  RETURN @CMDstatus
GO

/****** Object:  Stored Procedure dbo.expunge_users    Script Date: 11/4/98 1:05:45 PM ******/
CREATE PROCEDURE expunge_users
     @dbName VARCHAR(30) = ' ',
     @target VARCHAR(30) = ' ',
     @targetPassword VARCHAR(30) = ' ',
     @saPassword VARCHAR(30) = ' ',
     @path VARCHAR(50) = ' ',
     @debug VARCHAR(5) = 'false'
AS
DECLARE @bail SMALLINT,
        @CMD VARCHAR(255), 
        @CMDstatus INT,
        @myName VARCHAR(50), 
        @spidToKill VARCHAR(5)    
IF (@dbName <> ' ') AND (@target <> ' ') AND (@path <> ' ')
  BEGIN

    SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

    SELECT @bail = 10 
    WHILE (@bail > 0)
      BEGIN
        DELETE messenger where spid = @@SPID
        SELECT @CMD = "SELECT max(spid) FROM master..sysprocesses " +
                      "WHERE dbid = (SELECT dbid FROM master..sysdatabases " +
                      "WHERE name = '" + @dbName + "')"
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + @CMD + '" -o' + @path +
                      '\userCount.txt'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
 
        EXEC hold_semaphore 'textreader'
          TRUNCATE TABLE textreader
          SELECT @CMD = 'bcp admin..textreader in ' + @path + 
                        '\userCount.txt -c -Usa -P' + @saPassword +
                        ' -S' + @@SERVERNAME 
          EXEC @CMDstatus = dispatch @CMD, @myName, @debug
          IF (@CMDstatus <> 0) return @CMDstatus
          INSERT messenger SELECT @@SPID, text from textreader
          TRUNCATE TABLE textreader 
          SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                        ' -dmaster -Q"master..xp_cmdshell ' + "'del " + 
                        @path + "\userCount.txt'" + '"'
          EXEC @CMDstatus = dispatch @CMD, @myName, @debug
          IF (@CMDstatus <> 0) return @CMDstatus
          INSERT messenger 
          SELECT @@SPID, RTRIM(LTRIM(text)) 
          FROM textreader
          TRUNCATE TABLE textreader     
        EXEC release_semaphore 'textreader' 
        SELECT @spidToKill = (SELECT min(info) FROM messenger 
                                               WHERE spid = @@SPID
                                               AND info <> " " 	
                                               AND info NOT LIKE "---%"
                                               AND info NOT LIKE "%affected)%")
        IF (SELECT ISNUMERIC(@spidToKill))= 0
          SELECT @bail = -1
        ELSE
          BEGIN  
            IF (@target = @@SERVERNAME) 
              AND
               (CONVERT(VARCHAR(5), @@SPID) = @spidToKill)
                  BEGIN
                    SELECT @CMD = 'Cannot load database ' + @dbName + ' on ' + @target
                                   + '. This process is running in the database.'
                    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
                    RETURN @CMDStatus
                  END       
            SELECT @CMD = "kill " + @spidToKill
            SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                          ' -dmaster -Q"' + @CMD + '"'
            EXEC @CMDstatus = dispatch @CMD, @myName, @debug
            IF (@CMDstatus <> 0) return @CMDstatus
            IF @bail = 0
              BEGIN
                SELECT @CMD = 'There are active users in ' + 
                              @dbName + ' on ' + @target + 
                              ' that cannot be disconnected at this time.' 
                EXEC @CMDstatus = dispatch @CMD, @myName, @debug
                IF (@CMDstatus <> 0) return @CMDstatus
              END
            SELECT @bail = @bail - 1  
          END
      END 
    DELETE messenger where spid = @@SPID
    RETURN
  END
ELSE
  BEGIN
    PRINT "      usage: expunge_users [@dbName = ] <the db from which to end all user jobs>,"
    PRINT "                           [@target = ] <the server name where @dbName lives>,"
    PRINT "                           [@targetPassword = ] <sa password on target server>,"
    PRINT "                           [@saPassword = ] <sa password on local server>,"
    PRINT "                           [@path = ] <location to place all output files>,"
    PRINT "                           [@debug = ] <debug this procedure - default false>,"
    RETURN 1
  END
GO

/****** Object:  Stored Procedure dbo.run_script_remote    Script Date: 11/4/98 1:05:45 PM ******/
CREATE PROCEDURE run_script_remote
     @name VARCHAR(30) = ' ',
     @dbNAme VARCHAR(30) = ' ',
     @source VARCHAR(30) = ' ',
     @sourcePassword VARCHAR(30) = ' ',
     @path VARCHAR(50) = ' ',
     @saPassword VARCHAR(30) = ' ',
     @ext VARCHAR(3) = 'out',
     @debug VARCHAR(5) = 'false'
AS
DECLARE @scriptName VARCHAR(30),
        @myName VARCHAR(50),    
        @scriptFile VARCHAR(35),
        @outFile VARCHAR(35),
        @CMD VARCHAR(255),
	@CMDstatus INT 
/*
   Procedure run_remote_script will run any script
   in the admin subsystem's scripts table that is
   prefixed with 'script_' on any server with an 
   adequate trust relationship to the admin SQL
   Server. The scripts table nameing convention is 
   assumed.
*/
        
IF     (@name = ' ') 
    OR (@dbName = ' ')
    OR (@source = ' ') 
    OR (@path NOT LIKE '\\%')
  RETURN 1

  SET NOCOUNT ON

  SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

  SELECT @scriptName = 'script_' + @name
  SELECT @scriptFile = '\' + @name + '.sql'
  SELECT @outFile = '\' + @name + '.' + @ext 
  /*
     move the script to flat file in a folder
     with read/write permissions granted to
     the login account of the admin SQL Server
     and the source SQL Server 
  */
  EXEC hold_semaphore 'textreader'
    TRUNCATE TABLE textreader
      INSERT textreader
      SELECT line 
      FROM scripts 
      WHERE name = @scriptName
      ORDER BY ID
      IF @debug = 'true' SELECT * FROM textreader
      SELECT @CMD = 'bcp admin..textreader out ' + @path + 
                    @scriptFile + ' -c -Usa -P' + @saPassword + 
                    ' -S' + @@SERVERNAME
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    TRUNCATE TABLE textreader
      IF (@CMDstatus <> 0)  return @CMDstatus
  EXEC release_semaphore 'textreader'  
  /*
    run the script on the source SQL Server
  */
  IF @ext = ' '
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                  ' -d' + @dbName + ' -i"' + @path + @scriptFile + '" -n'
  ELSE
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                  ' -d' + @dbName + ' -i"' + @path + @scriptFile + '" -o' +
                  @path + @outFile + " -n"
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
  /*
    delete the script
  */
  SELECT @CMD = 'del ' + @path + @scriptFile
  EXEC @CMDstatus = dispatch @CMD, @myName, @debug
  IF (@CMDstatus <> 0) return @CMDstatus
RETURN
GO

/****** Object:  Stored Procedure dbo.verify_sysusages    Script Date: 11/4/98 1:05:45 PM ******/
CREATE PROCEDURE verify_sysusages
     @dbName VARCHAR(30) = ' ',         -- the database name on both systems
     @source VARCHAR(30) = ' ',         -- the source server name
     @sourcePassword VARCHAR(30) = ' ', -- sa password on source
     @target VARCHAR(30) = ' ',         -- the target server name
     @targetPassword VARCHAR(30) = ' ', -- sa password on target
     @saPassword VARCHAR(30) = ' ',     -- sa password on admin server
     @loadPath VARCHAR(50) = ' ',       -- fully qualified load path 
     @mailTo VARCHAR(30) = ' ',         -- address to mail debug info
     @debug VARCHAR(5) = 'false'        -- debug this procedure 
AS
/* 
verify_sysusages compares the size allocated in master..sysusages for two
databases and reports a problem if they are not the same size
*/
DECLARE 
     @CMD VARCHAR(255),                      -- reusable command holder 
     @CMDstatus INT,                         -- return status of command prompt  
     @myName VARCHAR(50)                     -- name of this function 

SET NOCOUNT ON

SELECT @mailto = emailAddress FROM persons WHERE role='DBA'

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

IF     (@dbName <> ' ') 
   AND (@source <> ' ') 
   AND (@target <> ' ') 
   AND (@loadPath <> ' ')
  BEGIN
    SELECT @CMD = "SELECT SUM(size) FROM master..sysusages WHERE dbid = DB_ID('" +
                  @dbName + "')"
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + ' -Q"' + 
                  @CMD + '" -o' + @loadpath + '\' + @source + @dbName + '.siz'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = "SELECT SUM(size) FROM master..sysusages WHERE dbid = DB_ID('" + 
                  @dbName + "')"
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + ' -Q"' + 
                  @CMD + '" -o' + @loadpath + '\' + @target + @dbName + '.siz'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    DELETE messenger WHERE spid = @@SPID
    EXEC hold_semaphore 'textreader' 
      TRUNCATE TABLE textreader
      SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + 
                    '\' + @source + @dbName + '.siz -c -Usa -P' + 
                    @saPassword + ' -S' + @@SERVERNAME 
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus
      SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + 
                    '\' + @target + @dbName + '.siz -c -Usa -P' + 
                    @saPassword + ' -S' + @@SERVERNAME
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) return @CMDstatus
      INSERT messenger
      SELECT @@SPID, 
             text 
      FROM textreader 
      WHERE text NOT LIKE " -%" 
      AND text NOT LIKE "(1 %"
      AND text NOT LIKE " "
      AND text IS NOT NULL
      TRUNCATE TABLE textreader
    EXEC release_semaphore 'textreader' 
    /* do some cleanup */
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"master..xp_cmdshell ' + "'del " + 
                  @loadPath + "\*.siz'" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    IF EXISTS (SELECT info FROM messenger
               WHERE spid = @@SPID
               AND ISNUMERIC(info) = 0)
      BEGIN
        SELECT @CMD = 'An error occurred while checking the size of database ' 
                      + @dbName + ' on ' + @source + ' and ' + @target + '. ('
                      + @myName + ')' 
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) 
          BEGIN
            SELECT @CMD = "SELECT * FROM admin.dbo.messenger WHERE spid = " 
                          + CONVERT(VARCHAR(5),@@SPID)
            EXEC master..xp_sendmail @mailTo, 
                                     'admin.verify_sysusages error info', 
                                     @query = @CMD            
            RETURN @CMDstatus
          END
        DELETE messenger where spid = @@SPID
        RETURN 99
      END 
    IF NOT EXISTS (SELECT * FROM messenger
                   WHERE spid = @@SPID
                   GROUP BY info
                   HAVING COUNT(*) > 1)
      BEGIN
        IF @debug = 'true'
          BEGIN
            SELECT @CMD = "SELECT * FROM admin.dbo.messenger WHERE spid = " 
                          + CONVERT(VARCHAR(5),@@SPID)
            EXEC master..xp_sendmail @mailTo, 
                                     'admin.verify_sysusages debug info', 
                                     @query = @CMD            
          END
        SELECT @CMD = 'The ' + @source + ' and ' + @target + ' ' + @dbName + 
                       ' databases are not the same size. (' + @myName + ')'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        DELETE messenger where spid = @@SPID
        RETURN 99     
      END
    DELETE messenger WHERE spid = @@SPID
    RETURN
  END
ELSE
  BEGIN
    PRINT "  usage: verify_sysusages"
    PRINT "                [@dbname         = <name of the database to be replicated > ],"
    PRINT "                [@source         = <source SQL Server's name>               ]," 
    PRINT "                [@sourcePassword = <sa password on the source SQL Server>   ],"
    PRINT "                [@target         = <target SQL Server's name>               ],"
    PRINT "                [@targetPassword = <sa password on target SQL Server>       ],"
    PRINT "                [@saPassword     = <sa password on adminstration SQL Server>],"
    PRINT "                [@loadPath       = <target system restore path>             ]," 
    PRINT "                [@debug          = <debug mode flag - default is false>     ],"
    RETURN 1
  END
GO

/****** Object:  Stored Procedure dbo.logSync    Script Date: 11/4/98 1:05:45 PM ******/
CREATE PROCEDURE logSync
    @dbName VARCHAR(30) = ' ',            -- the database name on both systems
    @source VARCHAR(30) = ' ',            -- the source server name
    @target VARCHAR(30) = ' ',            -- the target server name
    @sourcePassword VARCHAR(30) = ' ',    -- sa password on source
    @targetPassword VARCHAR(30) = ' ',    -- sa password on target
    @saPassword VARCHAR(30) = ' ',        -- sa password on admin server
    @debug VARCHAR(5) = 'false'
AS
DECLARE 
    @candidateTable VARCHAR(30),    -- for verification of restore (rowcount)     
    @dumpShare VARCHAR(10),         -- drive share name where dump folder resides
    @dumpDrive VARCHAR(5),          -- drive logical name where dump folder resides
    @dumpFolder VARCHAR(30),        -- source sys top level (full path less drive)
    @dumpPath VARCHAR(50),          -- fully qualified dump path
    @dumpDevice VARCHAR(80),        -- fully qualified dump device
    @dumpArchive VARCHAR(80),       -- source archive PATH
    @loadShare VARCHAR(10),         -- drive share name where load folder resides
    @loadDrive VARCHAR(5),          -- drive logical name where load folder resides
    @loadFolder VARCHAR(30),        -- target sys top level (full path less drive) 
    @loadPath VARCHAR(50),          -- fully qualified load path 
    @loadDevice VARCHAR(80),        -- fully qualified load device
    @loadArchive VARCHAR(80),       -- target archive PATH 
    @CMD VARCHAR(255),              -- reusable command holder 
    @CMDstatus INT,                 -- return status of command prompt  
    @mailto VARCHAR(30),            -- valid account in same Exchange Server
    @mailSubject VARCHAR(80),       -- mail subject string
    @mailAttachment VARCHAR(255),   -- mail subject string
    @fileName VARCHAR(30),          -- name of this log dump
    @myName VARCHAR(30),            -- this procedures name  
    @task VARCHAR(100)              -- working var to  invoke dbSync on fail   
/*
   logSync will dump a database and restore the dump to a hot backup 
   destination. Both the source and target databases will be configured 
   to support an async log dump and restore process. All dumps will be 
   archived for the source and target systems.
*/
SET NOCOUNT ON
    
SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

/* verify some parms - exit if inconsistent */
IF (@dbName = ' ') OR (@candidateTable = ' ') OR (@source = ' ') OR (@target = ' ')
    RETURN 1

/* if system db display error message and exit */
IF DB_ID(@dbName) < 6
  BEGIN
    SELECT @CMD = @myName + ' cannot be used on system database ' + @dbName + 
                  '. Only user databases can be safely moved via dump/load.'
    RAISERROR(@CMD,1,1) WITH SETERROR
    RETURN 1
  END

/* if debug is on, submit job to batch and produce debug output info */
IF   (@debug="true") 
 AND NOT EXISTS (SELECT program_name 
                  FROM master..sysprocesses
                  WHERE spid = @@SPID
                  AND program_name LIKE "ISQL%")
  BEGIN
    /* load server info into work variables */
    SELECT @dumpShare=dumpShare, 
           @dumpFolder=dumpFolder
    FROM servers WHERE name = @source

    SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME 
                  + ' -d' + DB_NAME() 
                  + ' -Q"' + OBJECT_NAME(@@PROCID) + " '" + 
                  + @dbName + "','" +
                  + @source + "','" + 
                  + @target + "','" +
                  + @sourcePassword + "','" +
                  + @targetPassword + "','" +
                  + @saPassword + "','" +
                  + @debug + "'" +
                  + '" -o' + '\\' + @source + '\' + @dumpShare + '\' 
                  + @dumpFolder + '\' + @dbName + '\' + OBJECT_NAME(@@PROCID) + 'debug.txt'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF @CMDstatus <> 0 
      BEGIN
        SELECT @CMD = 'Attempt to submit the ' + @myName +
                      ' process for database ' + @dbName +
                      + ' from server ' + @source +   
                      + ' to server ' + @target +
                      + ' in debug mode has failed!'
        RAISERROR(@CMD,1,1) WITH SETERROR
     END
  END
ELSE
  BEGIN

    /* make sure the db is available on source and target else exit*//*
    SELECT @CMDstatus = 0 
    INSERT in_use VALUES (@dbName, @source, OBJECT_NAME(@@PROCID), getdate())
    IF @@ERROR <> 0 SELECT @CMDstatus = 1
    INSERT in_use VALUES (@dbName, @target, OBJECT_NAME(@@PROCID), getdate())
    IF @@ERROR <> 0 SELECT @CMDstatus = @CMDstatus + 2
    IF @CMDstatus <> 0
      BEGIN 
        SELECT @CMD = "An unexpected error occurred while marking the database as 'in use'! (CMDstatus = " +
                      CONVERT(VARCHAR(5), @CMDstatus) + ")"  
        IF (@CMDstatus = 1)
            SELECT @CMD = 'An adminstrative subsystem process is already '
                          + 'active in database ' + @dbName + ' on server '
                          + @source  
                          + '. Only one admin process may be active '
                          + 'in any database at any time.('
                          + @myName + ')'  

        IF (@CMDstatus = 2)
            SELECT @CMD = 'An adminstrative subsystem process is already '
                          + 'active in database ' + @dbName + ' on server '
                          + @target  
                          + '. Only one admin process may be active '
                          + 'in any database at any time.('
                          + @myName + ')'  

        IF (@CMDstatus = 3)
            SELECT @CMD = 'An adminstrative subsystem process is already '
                          + 'active in database ' + @dbName + ' on server '
                          + @source + ' and server ' + @target  
                          + '. Only one admin process may be active '
                          + 'in any database at any time.('
                          + @myName + ')'  
        RAISERROR(@CMD,1,1) WITH SETERROR
        RETURN 1
      END -- in use
*/

    /* load server info into work variables */
    SELECT @mailto=emailAddress 
    FROM persons 
    WHERE role="DBA"

    SELECT @dumpShare=dumpShare, 
           @dumpDrive=dumpDrive,
           @dumpFolder=dumpFolder
    FROM servers 
    WHERE name = @source

    SELECT @loadShare=loadShare,
           @loadDrive=loadDrive,
           @loadFolder=loadFolder 
    FROM servers 
    WHERE name = @target

    SELECT @candidateTable=compareTableName
    FROM databases where name=@dbName

    SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' + 
                       @dumpFolder + '\' + @dbName 
    SELECT @dumpArchive = @dumpPath + '\archive'
    SELECT @loadPath = '\\' + @target + '\' + @loadShare + '\' + 
                       @loadFolder + '\' + @dbName
    SELECT @loadArchive = @loadPath + '\archive'

    /* make sure no one else is in the target database else load will fail */
    EXEC @CMDstatus = admin.dbo.expunge_users @dbName, 
                                              @target,
                                              @targetPassword,
                                              @saPassword,
                                              @loadPath,
                                              @debug      
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* make sure the source system dump device path is valid */
    SELECT @CMD = 'dir ' + @dumpPath
    EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output 
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* make sure a source system archive path exists for this database */
    SELECT @CMD = 'dir ' + @dumpArchive
    EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output 
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* make sure a target system archive path exists for this database */
    SELECT @CMD = 'dir ' + @loadArchive
    EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output 
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* find a name for the logdump. If this is the first log dump 
       for the db today, cleanup all previous log dumps */
    EXEC @CMDstatus = setLogDump @dbName,              -- database name   
                                 @dumpPath,            -- fully qualified dump path
                                 @loadPath,            -- fully qualified load path 
                                 @fileName OUTPUT,     -- name to use       
                                 @debug
    SELECT @dumpDevice = @dumpPath + '\' + @fileName
    SELECT @loadDevice = @loadPath + '\' + @fileName
    IF (@debug = 'true') 
      BEGIN
        SELECT '@filename: ',@fileName
        SELECT '@dumpDevice: ',@dumpDevice
        SELECT '@loadDevice: ',@loadDevice
      END
    /* count the number of rows in the candidate table in the source database */
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                  ' -d' + @dbName + ' -Q"select count(*) as ' + @source + 
                  ' from ' + @candidateTable + '" -o' + @loadPath +
                  '\sourceRows.txt'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
/*
    IF @debug = 'true'
      BEGIN
        SELECT @mailSubject = 'DEBUG INFO! ' + @dbName + ' logSync source rowcount results'
        SELECT @mailAttachment = @loadPath + '\sourceRows.txt' 
        EXEC master.dbo.xp_sendmail @recipients = @mailto,
                                    @subject = @mailSubject,
                                    @message = @cmd,
                                    @attachments = @mailAttachment 
      END
*/
    /* strip the ext off of the log dump file name */
    SELECT @filename = SUBSTRING(@fileName,1,CHARINDEX(".",@fileName) - 1)

    /* dump the database on the source system */
    SELECT @CMD = 'isql -Usa -P -S' + @source + ' -dmaster -Q"DUMP TRANSACTION ' + 
                  @dbName + " TO DISK = '" + RTRIM(LTRIM(@dumpdevice)) + "'" + 
                  '" -o' + @dumpPath + '\' + @fileName + '.out'

    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus
    EXEC verify_dump @source, 
                     @dumpDevice,
                     @CMDstatus OUTPUT,
                     @debug
    If (@CMDstatus = 0) 
      BEGIN
        SELECT @CMD =  @dumpPath + '\' + @fileName  + '.out' 
        SELECT @mailSubject = 'Unable to verify ' + @dbName + ' log dump!'  
        EXEC master.dbo.xp_sendmail @recipients = @mailto,
                                    @subject = @mailSubject,
                                    @attachments = @CMD
        SELECT @CMD = 'Unable to verify log dump to ' + @dumpDevice + 
                      ' of database ' + @dbName + ' on server ' 
                      + @source + '. The ' + @myName + ' process has been aborted.'     
        RAISERROR(@CMD,1,1) WITH SETERROR

        /* make the db available on source and target */
        DELETE in_use 
        WHERE dbName = @dbName
        AND server in (@source, @target)

        RETURN 1
      END
    If (@CMDstatus > 1) 
      BEGIN
        SELECT @CMD = 'Multiple log dumps of database ' + @dbName + ' on server ' 
                      + @source + ' to device ' + @dumpDevice + 
                      ' have occurred in the last 30 minutes. The ' 
                      + @myName + ' process will continue. Investigate this incident ASAP.'
        RAISERROR(@CMD,1,1) WITH SETERROR
      END

    /* zip the dump into the archive and move it to the hot site */
    EXEC @CMDstatus = zip_n_move @dbName,
                                 @filename, 
                                 @source, 
                                 @target, 
                                 @sourcePassword, 
                                 @targetPassword, 
                                 @saPassword,
                                 @debug

     IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* configure the target database for load of source dump */
    EXEC @CMDstatus = set_to_load @dbName,
                                  @target,
                                  @targetPassword,
                                  @debug 
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* restore the dump to the target system */
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"LOAD TRANSACTION ' + @dbName + 
                  " FROM DISK = '" + RTRIM(LTRIM(@loadDevice)) + "'" + 
                  '" -o' +  @loadPath + '\' + @fileName + '.out'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus
    EXEC verify_load @target, 
                     @loadDevice,
                     @CMDstatus OUTPUT,
                     @debug
    If (@CMDstatus = 0) 
      BEGIN

        /* make the db available on source and target */
        DELETE in_use 
        WHERE dbName = @dbName
        AND server in (@source, @target)

        /* notify DBA of failure */
        SELECT @CMD =  @loadPath + '\' + @fileName + '.out' 
        SELECT @mailSubject = @dbName + ' logSync Load failed. dbSync has been initiated'
        EXEC master.dbo.xp_sendmail @recipients = @mailto,
                                    @subject = @mailSubject,
                                    @attachments = @CMD

        /* submit a dbSync task */
        SELECT @task = 'dbSync ' + @dbName + ' ' + @source + ' to ' + @target
        EXEC msdb..sp_runtask @task
        RETURN 1
      END
    ELSE 
      BEGIN

        /* configure the target database as the hot site */
        EXEC @CMDstatus = set_to_hot_site @dbName,
                                          @target,
                                          @targetPassword,
                                          @debug 
        IF (@CMDstatus <> 0) return @CMDstatus

        /* check candidate table rowcount from source and target systems
           send email report if the rowcount is different */
        SELECT @CMD = 'isql -Usa -P -S' + @target + ' -d' + @dbName + 
                      ' -Q"select count(*) as ' + @target + ' from ' + 
                      @candidateTable + '" -o' + @loadPath + '\targetRows.txt'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) RETURN @CMDstatus
/*
         IF @debug = 'true'
          BEGIN
            SELECT @mailSubject = 'DEBUG INFO! ' + @dbName + ' logSync target db rowcount results'
            SELECT @mailAttachment = @loadPath + '\targetRows.txt' 
            EXEC master.dbo.xp_sendmail @recipients = @mailto,
                                        @subject = @mailSubject,
                                        @message = @cmd,
                                        @attachments = @mailAttachment 
          END
*/
        /* put an explaination header in the messenger file */
        DELETE messenger where spid = @@SPID 
        INSERT messenger 
        SELECT @@SPID, 'Count of the rows in a candidate table on the source'
        INSERT messenger 
        SELECT @@SPID, 'system just before the log is dumped and the same table'
        INSERT messenger 
        SELECT @@SPID, 'on the target system just after the log is loaded.'
        INSERT messenger 
        SELECT @@SPID, '-   CANDIDATE TABLE: ' + @candidateTable
        INSERT messenger 
        SELECT @@SPID, '-   DATABASE: ' + @dbName 
        INSERT messenger 
        SELECT @@SPID, '-   SOURCE SYSTEM: ' + @source
        INSERT messenger 
        SELECT @@SPID, '-   TARGET SYSTEM: ' + @target
        INSERT messenger 
        SELECT @@SPID, '***** source and target row count should be equal *****'
        INSERT messenger 
        SELECT @@SPID, '.'
        EXEC hold_semaphore 'textreader'
          TRUNCATE TABLE textreader
          SELECT @CMD = 'bcp admin..textreader in ' + @loadPath +
                        '\sourceRows.txt -c -Usa -P' + @saPassword + 
                        ' -S' + @@SERVERNAME 
          EXEC @CMDstatus = dispatch @CMD, @myName, @debug
          INSERT messenger SELECT @@SPID, text FROM textreader
          TRUNCATE TABLE textreader
          SELECT @CMD = 'bcp admin..textreader in ' + @loadPath + 
                        '\targetRows.txt -c -Usa -P' + @saPassword + 
                        ' -S' + @@SERVERNAME 
          EXEC @CMDstatus = dispatch @CMD, @myName, @debug
          INSERT messenger SELECT @@SPID, text FROM textreader
          SELECT @CMD = 'SELECT info FROM admin.dbo.messenger WHERE info ' + 
                        'IS NOT NULL AND spid = ' + 
                         CONVERT(VARCHAR(10), @@SPID)
          UPDATE messenger 
          SET info = " " 
          WHERE info LIKE "(1 row %" AND spid = @@SPID
          UPDATE messenger 
          SET info = rtrim(ltrim(info)) 
          WHERE spid = @@SPID
          IF (SELECT info FROM messenger WHERE ID = 13) <> (SELECT info FROM messenger WHERE ID = 16) 
            BEGIN
              SELECT @mailSubject = @dbName + ' ' + @myName + ' consistency check discrepency report'
              EXEC master.dbo.xp_sendmail @recipients = @mailto,
                                          @subject = @mailSubject,
                                          @query = @CMD
            END  
          DELETE messenger where spid = @@SPID
          TRUNCATE TABLE textreader
        EXEC release_semaphore 'textreader'  
        /* clean up consistency check files */
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target  
                      + ' -dmaster -Q"master..xp_cmdshell ' + "'del "  
                      + @loadPath + "\*Rows.txt'" + '"'  
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) RETURN @CMDstatus
      END

  END -- debug in batch 

RETURN
GO

/****** Object:  Stored Procedure dbo.maintenance    Script Date: 11/4/98 1:05:45 PM ******/
CREATE PROCEDURE maintenance
    @dbName VARCHAR(30) = ' ',          -- the database name
    @source VARCHAR(30) = ' ',          -- the server name
    @dumpMe VARCHAR(5) = 'true',        -- flag to enable dump processing
    @sourcePassword VARCHAR(30) = ' ',  -- sa password on source
    @saPassword VARCHAR(30) = ' ',      -- sa password for admin server
    @debug VARCHAR(5) = 'false'         -- debug codependant
AS
DECLARE 
    @dumpPath VARCHAR(50),     -- dump path 
    @dumpDevice VARCHAR(80),   -- fully qualified dump device
    @dumpArchive VARCHAR(80),  -- source archive PATH
    @dumpShare VARCHAR(30),    -- source system drive share name
    @drive VARCHAR(5),         -- source system drive logical name
    @dumpFolder VARCHAR(30),   -- source system top level directory
    @CMD VARCHAR(255),         -- reusable command holder 
    @CMDstatus INT,            -- return status of command prompt  
    @mailto VARCHAR(30),       -- DBAs email address
    @mailSubject VARCHAR(80),  -- mail subject string
    @mailMessage VARCHAR(80),  -- mail message string
    @myName VARCHAR(50)        -- this stored procedure

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' +OBJECT_NAME(@@PROCID)

SELECT @mailto=emailAddress FROM persons WHERE role="DBA"

/*
  Procedure will perform maintenance and dump of a
  database. The previous dump will be kept in a 
  zipped archive along with the current dump. 
*/
IF (@dbName = ' ') OR (@source = ' ')
  BEGIN
    EXEC sp_help maintenance
    RETURN 1
  END

/* if debug is on and the job is not already in batch, 
   submit job to batch and produce debug output info */
IF   (@debug="true") 
 AND NOT EXISTS (SELECT program_name 
                  FROM master..sysprocesses
                  WHERE spid = @@SPID
                  AND program_name LIKE "ISQL%")
  BEGIN
    /* load server info into work variables */
    SELECT @dumpShare=dumpShare, 
           @dumpFolder=dumpFolder
    FROM servers WHERE name = @source

    SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' + 
                       @dumpFolder + '\' + @dbName 
    SELECT @dumpArchive = @dumpPath + '\archive'

    /* verify necessary file systems for target server */
    EXEC @CMDstatus = verify_sync_folders @dbName,
                                          @dumpPath,
                                          @dumpShare,
                                          @dumpFolder,
                                          @dumpArchive,
                                          @source,                     
                                          @debug
    IF (@CMDstatus <> 0) return @CMDstatus

    SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME 
                  + ' -d' + DB_NAME() 
                  + ' -Q"' + OBJECT_NAME(@@PROCID) + " '" + 
                  + @dbName + "','" +
                  + @source + "','" + 
                  + @dumpMe + "','" +
                  + @sourcePassword + "','" +
                  + @saPassword + "','" +
                  + @debug + "'" +
                  + '" -o' + '\\' + @source + '\' + @dumpShare + '\' 
                  + @dumpFolder + '\' + @dbName + '\' + OBJECT_NAME(@@PROCID) + 'debug.txt'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF @CMDstatus <> 0 
      BEGIN
        SELECT @CMD = 'Attempt to submit the ' + @myName +
                      ' process for database ' + @dbName +
                      + ' from server ' + @source +   
                      + ' in debug mode has failed!'
        RAISERROR(@CMD,1,1) WITH SETERROR
     END
  END
ELSE
  BEGIN

    /* load server info into work variables */
    IF (@dumpMe='true')
        SELECT @dumpShare=dumpShare, 
               @drive=dumpDrive,
               @dumpFolder=dumpFolder
        FROM servers WHERE name = @source
    ELSE  
        SELECT @dumpShare=loadShare, 
               @drive=loadDrive,
               @dumpFolder=loadFolder
        FROM servers WHERE name = @source

    /* build file system paths used by this the maintenance procedure */
    SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' + 
                       @dumpFolder + '\' + @dbName 
    SELECT @dumpDevice = @dumpPath + '\' + SUBSTRING(@dbName,1,4) +"dump.DAT" 
    SELECT @dumpArchive = @dumpPath + '\archive'
    IF @debug = 'true'
      SELECT "maintenance DEBUG info! ==> @myName=", @myName,
             " ==> dumpPath=", @dumpPath,
             " ==> dumpDevice=", @dumpDevice
             " ==> dumpArchive=", @dumpArchive
 
    /* verify necessary file systems for target server */
    EXEC @CMDstatus = verify_sync_folders @dbName,
                                          @dumpPath,
                                          @dumpShare,
                                          @dumpFolder,
                                          @dumpArchive,
                                          @source,                     
                                          @debug
    IF (@CMDstatus <> 0) return @CMDstatus

    /* archive the maintenance output files */
    SELECT @CMD = 'dir ' + @dumpPath + '\*.out'
    EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output
    IF (@CMDstatus = 0) -- found
      BEGIN
        SELECT @CMD = 'dir ' + @dumpPath + '\*.001'
        EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output
        IF (@CMDstatus = 0) -- found
          BEGIN
            SELECT @CMD = 'del ' + @dumpPath + '\*.001'
            EXEC @CMDstatus = dispatch @CMD, @myName, @debug
          END
        SELECT @CMD = 'ren ' + @dumpPath + '\*.out '+ '*.001'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      END  

    /**************** MAINTENANCE PROCESSING *********************/
    /* build scripts to set permissions as they now exist */ 
    EXEC run_script_remote "permissions",
                           @dbName,
                           @source,
	                   @sourcePassword,
	                   @dumpPath,
	                   @saPassword,
                           'out',
	                   @debug
    SELECT @CMDstatus = @@ERROR
    IF (@CMDstatus <> 0) return @CMDstatus

    /* build scripts to set users and alias as they now exist */ 
    EXEC run_script_remote "users",
                           @dbName,
                           @source,
	                   @sourcePassword,
	                   @dumpPath,
	                   @saPassword,
                           'out', 
	                   @debug
    SELECT @CMDstatus = @@ERROR
    IF (@CMDstatus <> 0) return @CMDstatus

    /* build scripts to set row level locks as they now exist */ 
    EXEC run_script_remote "row_level_locks",
                           @dbName,
                           @source,
	                   @sourcePassword,
	                   @dumpPath,
	                   @saPassword,
                           'out', 
	                   @debug
    SELECT @CMDstatus = @@ERROR
    IF (@CMDstatus <> 0) return @CMDstatus

    /* Check the table allocation chain (capture elapsed time in outfile) */
    SELECT @CMD = 'isql -Usa -e -p -P'+ @sourcePassword + ' -S' + @source + 
                  ' -d' + @dbName + ' -Q"DBCC CHECKDB" -o' + 
                  @dumpPath + '\checkdb.out'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus

    /* Check the storage allocation chain (capture elapsed time in outfile)*/
    SELECT @CMD = 'isql -Usa -e -p -P' + @sourcePassword + ' -S' + @source + 
                  ' -d' + @dbName + ' -Q"DBCC NEWALLOC" -o' + 
                  @dumpPath + '\newalloc.out'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus

    /* Check the system tables allocation chain */
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                  ' -d' + @dbName + ' -Q"DBCC CHECKCATALOG" -o' + 
                  @dumpPath + '\checkcatalog.out'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus

    /* get the sp_help info for the database */
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                  ' -d' + @dbName + ' -Q"sp_helpdb ' + @dbName + '" -o' + 
                  @dumpPath + '\helpdb.out'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus

    /* check the space available in the database 
       notify somebody if its getting full*/
    EXEC run_script_remote "check_space",
                           @dbName,
                           @source,
	                   @sourcePassword,
	                   @dumpPath,
	                   @saPassword,
                           'out', 
	                   @debug
    SELECT @CMDstatus = @@ERROR
    IF (@CMDstatus <> 0) return @CMDstatus

    /************* MSDB ONLY *************/
    IF @dbName = 'msdb'
      BEGIN
        /* get systasks info */
        EXEC run_script_remote "tasks",
                               @dbName,
                               @source,
	                       @sourcePassword,
	                       @dumpPath,
	                       @saPassword,
                               'out',
	                       @debug
        SELECT @CMDstatus = @@ERROR
        IF (@CMDstatus <> 0) return @CMDstatus
  
        /* clean up task history tables */ 
        SELECT @CMD = "admin..purgeMSDB @source = '" + @source + 
                      "', @sourcePassword = '" + @sourcePassword + 
                       "', @debug = '" + @debug + "'"
                      
        EXEC (@CMD)

      END -- msdb database

    /************* MASTER ONLY *************/
    IF @dbName = 'master'
      BEGIN
        /* get the reverse engineered database create script */
        EXEC run_script_remote "devs_and_dbs",
                               @dbName,
                               @source,
	                       @sourcePassword,
	                       @dumpPath,
	                       @saPassword,
                               'out',
	                       @debug
        SELECT @CMDstatus = @@ERROR
        IF (@CMDstatus <> 0) return @CMDstatus
    
        /* build create scripts for any user extended stored procedures */ 
        EXEC run_script_remote "user_xps",
                               @dbName,
                               @source,
	                       @sourcePassword,
	                       @dumpPath,
	                       @saPassword,
                               'out',
	                       @debug
        SELECT @CMDstatus = @@ERROR
        IF (@CMDstatus <> 0) return @CMDstatus

        /* get the info from sysdatabase, sysdevices, and sysusages for
           critical failure recovery processing */
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -d' + @dbName + ' -Q"select * from sysdatabases" -o' + 
                      @dumpPath + '\sysdatabases.out'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -d' + @dbName + ' -Q"select * from sysdevices" -o' + 
                      @dumpPath + '\sysdevices.out'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -d' + @dbName + ' -Q"select * from sysusages" -o' + 
                      @dumpPath + '\sysusages.out'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus

        /* get the SQL Server configuration info */
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -d' + @dbName + ' -Q"sp_configure" -o' + 
                      @dumpPath + '\configure.out'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus

        /* get the memory usage info */
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source + 
                      ' -d' + @dbName + ' -Q"DBCC MEMUSAGE" -o' + 
                      @dumpPath + '\memusage.out'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END -- master database

    /**************** DUMP THE DATABASE ****************/
    IF @dumpMe = "true"
      BEGIN

        /* cleanup the dump files */
        EXEC @CMDstatus = delete_archive @dumpArchive,
                                         @debug 
        IF (@CMDstatus <> 0) select @CMDstatus

        /* dump the database */
        EXEC @CMDstatus =  dumpDatabase @dbName,
                                        @source,
                                        @sourcePassword,
                                        @dumpFolder,
                                        @drive, 
                                        @debug
        IF (@CMDstatus <> 0) RETURN @CMDstatus

        /* archive the dump */ 
        SELECT @CMD = @drive + ':&cd ' + @dumpfolder + '\' + @dbName + '&pkzip ' +
                      'archive\' + SUBSTRING(@dbName,1,4) + 'dump.zip ' + 
                       SUBSTRING(@dbName,1,4) + 'dump.dat -b' + @drive + ":"
        SELECT @CMD = "master..xp_cmdshell '" + @CMD + "'" 
        SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source +
                      ' -dmaster -Q"' + @CMD + '"'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus
      END

    /* Check for errors in the out files and mail results. Parse 
       the maintenance output files for specific words that may 
       indicate problems in the database. Mail the results. */
    EXEC check_outfiles @dbName,
    			@source,
                        @dumpPath,
                        @mailto,
                        @saPassword,
                        @debug

  END -- debug in batch 

RETURN
GO

/****** Object:  Stored Procedure dbo.dbSync    Script Date: 11/4/98 1:05:45 PM ******/
CREATE PROCEDURE dbSync
    @dbName VARCHAR(30) = ' ',           -- database 
    @source VARCHAR(30) = ' ',           -- source server
    @target VARCHAR(30) = ' ',           -- target server
    @sourcePassword VARCHAR(30) = ' ',   -- source sa password
    @targetPassword VARCHAR(30) = ' ',   -- target sa password
    @saPassword VARCHAR(30) = ' ',       -- admin sa password
    @maintain VARCHAR(5) = 'true',       -- maintain target
    @debug VARCHAR(5) = 'false'          -- enable debug  
AS
DECLARE 
    @candidateTable VARCHAR(30),   -- table to check
    @dumpShare VARCHAR(10),   -- source outfile share
    @dumpFolder VARCHAR(30),  -- source outfile path
    @dumpDrive VARCHAR(5),    -- logical drive name of dump share
    @dumpPath VARCHAR(50),    -- fully qualified dump path
    @dumpDevice VARCHAR(80),  -- fully qualified dump device
    @dumpArchive VARCHAR(80), -- source archive path
    @loadShare VARCHAR(10),   -- target outfile share
    @loadFolder VARCHAR(30),  -- target outfile path
    @loadDrive VARCHAR(5),    -- logical drive name of load share
    @loadPath VARCHAR(50),    -- fully qualified load path 
    @loadDevice VARCHAR(80),  -- fully qualified load device
    @loadArchive VARCHAR(80), -- target archive path 
    @CMD VARCHAR(255),        -- command holder 
    @CMDstatus INT,           -- return status of command prompt  
    @mailto VARCHAR(30),      -- admin email address
    @mailSubject VARCHAR(80),  
    @myName VARCHAR(50)       -- this procedure
/*
  Procedure dbSync will dump a database and restore the dump to 
  a hot backup destination. Both the source and target databases
  will be configured to support an async log dump and restore
  process. All dumps will be archived for the source and target.
*/

SET NOCOUNT ON

SELECT @myName = DB_NAME() + '..' + OBJECT_NAME(@@PROCID)

/* if no dbName display usage message and exit */
IF   (@dbName = ' ') 
  OR (@candidateTable = ' ') 
  OR (@source = ' ')  
  OR (@target = ' ')
  BEGIN
    SELECT @CMD = "Invalid parameter list supplied to procedure " + @myName
    RAISERROR(@CMD,1,1) WITH SETERROR
    RETURN 1
  END

/* if system db display error message and exit */
IF DB_ID(@dbName) < 6
  BEGIN
    SELECT @CMD = @myName + ' cannot be used on system database ' 
                  + @dbName + '. Only user databases can be safely '
                  + 'moved via dump/load.'
    RAISERROR(@CMD,1,1) WITH SETERROR
    RETURN 1
  END

/* if debug is on, submit job to batch and produce debug output info */
IF   (@debug="true") 
 AND NOT EXISTS (SELECT program_name 
                  FROM master..sysprocesses
                  WHERE spid = @@SPID
                  AND program_name LIKE "ISQL%")
  BEGIN
    /* load server info into work variables */
    SELECT @dumpShare=dumpShare, 
           @dumpFolder=dumpFolder
    FROM servers WHERE name = @source

    SELECT @CMD = 'isql -Usa -P' + @saPassword + ' -S' + @@SERVERNAME 
                  + ' -d' + DB_NAME() 
                  + ' -Q"' + OBJECT_NAME(@@PROCID) + " '" + 
                  + @dbName + "','" +
                  + @source + "','" + 
                  + @target + "','" +
                  + @sourcePassword + "','" +
                  + @targetPassword + "','" +
                  + @saPassword + "','" +
                  + @maintain + "','" +
                  + @debug + "'" +
                  + '" -o' + '\\' + @source + '\' + @dumpShare + '\' 
                  + @dumpFolder + '\' + @dbName + '\' + OBJECT_NAME(@@PROCID) + 'debug.txt'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF @CMDstatus <> 0 
      BEGIN
        SELECT @CMD = 'Attempt to submit the ' + @myName +
                      ' process for database ' + @dbName +
                      + ' from server ' + @source +   
                      + ' to server ' + @target +
                      + ' in debug mode has failed!'
        RAISERROR(@CMD,1,1) WITH SETERROR
     END
  END
ELSE
  BEGIN
    
    SELECT @mailto=emailAddress 
    FROM persons 
    WHERE role="DBA"

    SELECT @dumpShare=dumpShare, 
           @dumpDrive=dumpDrive,
           @dumpFolder=dumpFolder
    FROM servers 
    WHERE name = @source

    SELECT @loadShare=loadShare,
           @loadDrive=loadDrive,
           @loadFolder=loadFolder 
    FROM servers 
    WHERE name = @target

    SELECT @candidateTable=compareTableName
    FROM databases where name=@dbName

    /* build file system paths used by this replication process */
    SELECT @dumpPath = '\\' + @source + '\' + @dumpShare + '\' + 
                       @dumpFolder + '\' + @dbName 
    SELECT @dumpDevice = @dumpPath + '\' + SUBSTRING(@dbName,1,4) 
                         +"dump.DAT" 
    SELECT @dumpArchive = @dumpPath + '\archive'
    SELECT @loadPath = '\\' + @target + '\' + @loadShare + '\' + 
                       @loadFolder + '\' + @dbName
    SELECT @loadDevice = @loadPath + '\' + SUBSTRING(@dbName,1,4) 
                         + "dump.DAT" 
    SELECT @loadArchive = @loadPath + '\archive'

   IF @debug = 'true'
      BEGIN
        SELECT "!!! START Local Variables DEBUG Listing !!!"
        SELECT "    myName = ", @myName
        SELECT "    dumpDrive = ", @dumpDrive
        SELECT "    dumpPath = ", @dumpPath
        SELECT "    dumpDevice = ", @dumpDevice
        SELECT "    dumpArchive = ", @dumpArchive
        SELECT "    loadDrive = ", @loadDrive
        SELECT "    loadPath = ", @loadPath
        SELECT "    loadDevice = ", @loadDevice
        SELECT "    loadArchive = ", @loadArchive
        SELECT "    maintain Flag = ", @maintain
        SELECT "!!! END Local Variables DEBUG Listing !!!"
      END

    /* verify file systems for replication on source server */
    EXEC @CMDstatus = verify_sync_folders @dbName,
                                          @dumpPath,
                                          @dumpShare,
                                          @dumpFolder,
                                          @dumpArchive,
                                          @source,
                                          @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* cleanup the source dump files */
    EXEC @CMDstatus = delete_archive @dumpArchive,
                                     @debug 
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* verify file systems for replication on target server */
    EXEC @CMDstatus = verify_sync_folders @dbName,
                                          @loadPath,
                                          @loadShare,
                                          @loadFolder,
                                          @loadArchive,
                                          @target,
                                          @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* cleanup the target dump files */
    EXEC @CMDstatus = delete_archive @loadArchive,
                                     @debug 
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* are source and target databases are the same size */
    EXEC @CMDstatus = verify_sysusages @dbName,
                                       @source,
                                       @sourcePassword,
                                       @target,
                                       @targetPassword,
                                       @saPassword,
                                       @loadPath,
                                       @mailTo,  
                                       @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* count the number of rows in the candidate table 
       in the source database for use later to verify
       the load */
    SELECT @CMD = 'isql -Usa -P' + @sourcePassword + ' -S' + @source 
                  + ' -d' + @dbName + ' -Q"select count(*) as ' 
                  + @source + ' from ' + @candidateTable + '" -o' 
                  + @loadPath + '\sourceRows.txt'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* set source db to be the primary server */  
    EXEC @CMDstatus = set_to_primary @dbName,
                                     @source,
                                     @sourcePassword,
                                     @debug 
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* dump the database */
    EXEC @CMDstatus = dumpDatabase @dbName,
                                   @source,
                                   @sourcePassword,
                                   @dumpFolder,
                                   @dumpDrive,
                                   @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* Move the dump file to the target system */
    EXEC @CMDstatus = zip_n_move @dbName, 
                                 @dbName,  -- as @fileName  
                                 @source, 
                                 @target, 
                                 @sourcePassword, 
                                 @targetPassword, 
                                 @saPassword,  	
                                 @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* configure the target database for load of source dump */
    EXEC @CMDstatus = set_to_load @dbName,
                                  @target,
                                  @targetPassword,
                                  @debug 
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* make sure no one else is in the target database */
    EXEC @CMDstatus = admin.dbo.expunge_users @dbName, 
                                              @target,
                                              @targetPassword,
                                              @saPassword,
                                              @loadPath,
                                              @debug      
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* restore the dump to the target system */
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target 
                  + ' -dmaster -Q"LOAD DATABASE ' + @dbName + " FROM DISK = '" 
                  + @loadDrive + ":\"  + @loadFolder + "\" + @dbName + "\" 
                  + SUBSTRING(@dbName,1,4) +"dump.DAT" + "'" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus
    EXEC verify_load @target, 
                     @loadDevice,
                     @CMDstatus OUTPUT,
                     @debug
    IF (@CMDstatus = 0) 
      BEGIN
        SELECT @mailSubject = @dbName + ' dbSync Load failed.'
        EXEC master.dbo.xp_sendmail @recipients = @mailto,
                                    @subject = @mailSubject,
                                    @message = @CMD
        RETURN 1
      END
    /* configure the target database to be the hot site */
    EXEC @CMDstatus = set_to_hot_site @dbName,
                                      @target,
                                      @targetPassword,
                                      @debug 
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* perform data consistency check */
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target 
                  + ' -d' + @dbName + ' -Q"select count(*) as ' 
                  + @target + ' from ' + @candidateTable + '" -o' 
                  + @loadPath + '\targetRows.txt'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* put an explaination header in the messenger file */
    DELETE messenger where spid = @@SPID
    INSERT messenger 
    SELECT @@SPID, 'Count of the rows in a candidate table on the'
    INSERT messenger 
    SELECT @@SPID, 'source just before the dump and the same table'
    INSERT messenger 
    SELECT @@SPID, 'on the target just after the load.'
    INSERT messenger 
    SELECT @@SPID, '-   CANDIDATE TABLE: ' + @candidateTable
    INSERT messenger 
    SELECT @@SPID, '-   DATABASE: ' + @dbName 
    INSERT messenger 
    SELECT @@SPID, '-   SOURCE SYSTEM: ' + @source
    INSERT messenger 
    SELECT @@SPID, '-   TARGET SYSTEM: ' + @target
    INSERT messenger 
    SELECT @@SPID, '***** target row count should be equal *****'
    INSERT messenger 
    SELECT @@SPID, '.'

    EXEC hold_semaphore 'textreader'

      /* load the source system row count into messenger */
      TRUNCATE TABLE textreader
      SELECT @CMD = 'bcp admin..textreader in ' + @loadPath  
                    + '\sourceRows.txt -c -Usa -P' + @saPassword  
                    + ' -S' + @@SERVERNAME 
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) RETURN @CMDstatus
      INSERT messenger SELECT @@SPID, text from textreader

      /* load the target system row count into messenger */
      TRUNCATE TABLE textreader
      SELECT @CMD = 'bcp admin..textreader in ' + @loadPath  
                    + '\targetRows.txt -c -Usa -P' + @saPassword 
                    + ' -S' + @@SERVERNAME 
      EXEC @CMDstatus = dispatch @CMD, @myName, @debug
      IF (@CMDstatus <> 0) RETURN @CMDstatus
      INSERT messenger SELECT @@SPID, text from textreader
      TRUNCATE TABLE textreader 

    EXEC release_semaphore 'textreader' 

    /* clean up consistency check files */
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target  
                  + ' -dmaster -Q"master..xp_cmdshell ' + "'del "  
                  + @loadPath + "\*Rows.txt'" + '"'  
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) RETURN @CMDstatus

    /* send the report to the mailto(s) if a problem exists */ 
    SELECT @CMD = 'SELECT RTRIM(LTRIM(info)) FROM admin.dbo.messenger ' 
                  + 'WHERE info IS NOT NULL and spid = ' 
                  + CONVERT(VARCHAR(5), @@SPID)  
                  +' AND info NOT LIKE "(1 row %"' 
    SELECT @mailSubject = @dbName + ' dbSync consistency check'
    IF (SELECT info FROM messenger WHERE ID = 13) 
      <> 
       (SELECT info FROM messenger WHERE ID = 16) 
      BEGIN
        SELECT @mailSubject = @myName + ' ' + @dbName 
                              + ' CONSISTENCY ERROR'
        EXEC master.dbo.xp_sendmail @recipients = @mailto,
                                    @subject = @mailSubject,
                                    @query = @CMD
      END 

    /* clean up admin environment */
    DELETE messenger where spid = @@SPID
    TRUNCATE TABLE textreader

    /* maintain the database on the target system */
    IF @maintain = 'true'
      EXEC maintenance @dbName, 
                       @target, 
                       'false',        -- do not dump this database
                       @targetPassword, 
                       @saPassword,
                       @debug                  

  END -- debug in batch 

RETURN
GO

