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