verify_load

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.verify_load') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.verify_load
GO

CREATE PROCEDURE verify_load
        @source VARCHAR(30) = ' ',
        @device VARCHAR(80) = ' ',
        @found INT OUTPUT, 
	@debug VARCHAR(5) = 'false'
AS
DECLARE @CMDstatus INT,
        @timeWindowStart VARCHAR(20), 
 	@myName VARCHAR(30)
/*
   Procedure verify_load uses the remote procedure 
   capability of SQL Server 6.5 to verify that a 
   load has been recorded in the msdb database.
   There is an attempt to identify possible duplicate
   loads. The server names are hard coded here for 
   proper operation of the remote stored procedure.  
*/

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

SET NOCOUNT ON

IF @source = @@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 = @device 
                     AND d.restore_id = h.restore_id 
                     AND h.restore_date > @timeWindowStart)
    RETURN
  END 
IF (@source = 'hot_site') 
  EXEC hot_site.admin.dbo.verify_load @source,
                                      @device,
                                      @found OUTPUT, 
                                      @debug
IF (@source = 'prod1') 
  EXEC prod1.admin.dbo.verify_load @source,
                                   @device,
                                   @found OUTPUT, 
                                   @debug
IF (@source = 'prod2') 
  EXEC prod2.admin.dbo.verify_load @source,
                                   @device,
                                   @found OUTPUT,
                                   @debug
GO