dispatch

Ideally, all command line requests are routed through dispatch. This will assure consistent error handling and consistent notification.

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

CREATE PROCEDURE dispatch
   @CMD VARCHAR(255)   = NULL,  -- command line string
   @caller VARCHAR(30) = NULL,  -- name of calling procedure or script
   @mailto VARCHAR(30) = 'bwunder',     -- name in Exchange Address Book
   @sendto VARCHAR(30) = 'bwunder', -- any known node or user
   @debug VARCHAR(5)   = 'false'
AS
  /* Invoke the command interperter. If the command fails, notify
     designate(s) by email and network message. The default behavior 
     of the function is to place command line strings into an email 
     message. Use this behavoir to email admin info message strings.*/

  DECLARE @CMDstatus int 	

  IF @debug = 'false'
    EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output
  ELSE
    BEGIN
      SELECT "**** dispatch command from " + @caller + " ****" 
      SELECT @CMD
      EXEC @CMDstatus = master..xp_cmdshell @CMD
    END
  IF (@CMDstatus <> 0) -- failed
    BEGIN
      SELECT @CMD = 'The ' + @caller + ' process failed at :' + @CMD +
                    '. Status:'+ CONVERT(VARCHAR(2),@CMDstatus)
      EXEC master..xp_sendmail @mailto , @CMD
      SELECT @CMD = 'net send ' + @sendto + ' "' + @CMD + '"'
      IF @debug = 'false'
        EXEC @CMDstatus = master..xp_cmdshell @CMD, no_output
      ELSE
        BEGIN
          SELECT "**** dispatch fail ****"
          SELECT @CMD
          EXEC @CMDstatus = master..xp_cmdshell @CMD
        END
      IF (@CMDstatus <> 0) -- failed
        BEGIN
          SELECT @CMD = 'CALL TO --- ' + @CMD + ' --- failed!' 
          RAISERROR(@CMD,1,2) WITH LOG
        END
    END
  RETURN @CMDstatus
GO