Integrity Check file sender Script

These first five or so posts use DB mail and the stored procedure sp_send_dbmail. I like information sent to me, rather than me looking for it. In this case the weekly Database integrity checks are run and with over ten instances of SQL Server (all 2005). I don't want to spend my time every Monday morning (when I remember) to go look for each intergrity check output file.

Thinking about this, I should probably turn this into a SSIS package. That will be another post.

Each server needs DB mail running, it's easier if there is a common DB mail profile across all servers. This stored procedure should run on each machine and it would be one less thing to change in the code.

The code also uses xp_cmdshell. It's used to get directory listing where the intergrity output file exists. Here is the DOS command I use to get the list:
dir "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\*Integrity*.txt" /B /TC /O-D

where /B is base formating, /TC show time of creation and /O-D means order by desending date. So this will give us a list of files with the newest file at the top of the list. By dumping this into a temp table and getting the first one we have the file we want. Then we just have to mail it.
Code: /*

created by Dave Ott

This emails the Database Intergrity checks to me. PLace on all DB servers

Note: db mail must be set up and use the profile that is on that server

*/

-- OS command to get file. Change path as needed

DECLARE @cmd NVARCHAR(500)

SET @cmd = 'dir "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\*Integrity*.txt" /B /TC /O-D'

/*

/B - base format

/TC - Time, Creation

/O - List Files by (D)ate (-) reversed order

*/

SET nocount ON

CREATE TABLE #list

(

ikey INT IDENTITY(1, 1)

NOT NULL,

filename VARCHAR(1000)

)

INSERT INTO #list ( filename )

EXEC xp_cmdshell @cmd

--Most servers have only one file, but keep logic to loop through multiple files

DELETE FROM #list

WHERE ikey > 2

DECLARE @file VARCHAR(1000),

@file_attachments VARCHAR(3000)

WHILE EXISTS ( SELECT * FROM #list )

BEGIN

SET @file = (SELECT MIN(filename) FROM #list)

SET @file_attachments = CASE WHEN @file_attachments IS NULL THEN ''

ELSE @file_attachments + ';'END

+ 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\'

+ @file

DELETE FROM #list

WHERE filename = @file

END

-- Email file

DECLARE @subject NVARCHAR(100)

SET @subject = 'Integrity checks - ' + @@servername

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profile',

@recipients = 'DBAGroup@MyCo.com', @body = 'Integrity check',

@subject = @subject, @file_attachments = @file_attachments,

@importance = 'High'

GO