check_outfiles

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

CREATE PROCEDURE check_outfiles
    @dbName VARCHAR(30) = ' ',      -- the database name
    @source VARCHAR(30) = ' ',      -- SQL Server name
    @path VARCHAR(50) = ' ',        -- fully qulaified output path    
    @mailto VARCHAR(30) = 'DBA',    -- valid email account
    @saPassword VARCHAR(30) = ' ',  -- sa password for admin server
    @debug VARCHAR(5) = 'false'      -- debug codependant

AS
DECLARE @CMD VARCHAR(255),     -- reusable command holder 
        @CMDstatus INT,        -- return status of command prompt
        @mailSubject VARCHAR(255), 
	@myName VARCHAR(30),   -- 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

  DELETE messenger where spid = @@SPID

  SELECT @myName = 'admin.check_outfiles'

  /* scan outfiles */
  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 = dispatch @CMD, @myName, @debug

  /* 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

  /* add the scanned results to the messenger table */
  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 -- errors found
        BEGIN
          INSERT messenger SELECT @@SPID, text FROM textreader
          SELECT @mailSubject = 'Maintenance problems identified! database: ' 
                                + @dbName + ' server: ' + @source 
          TRUNCATE TABLE textreader
          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