Help with a loop sending e-mail in SQL

microsoft.public.sqlserver.programming

16 Feb 2006 8:25 PM

Matthew

I have a script that collects drive information. Now what I need it to
do is to send one e-mail message to per critical error reported. So I
need the query to loop through the temp table and for each instance
that it finds an error it will send a message.

Thanks

-Matt-

[code]

DECLARE @TEXT NVARCHAR(MAX);
SET @TEXT = ''
DECLARE @Critical_Value INT
DECLARE @GigaByte_Conv INT
SET @GigaByte_Conv = 1024
SET @Critical_Value = 5

CREATE TABLE #Critical_Alert (
    Computer   VARCHAR (128),
    Drive      VARCHAR (2),
    DiskSpace DECIMAL (28,2),
    UsedSpace DECIMAL (28,2),
    FreeSpace DECIMAL (28,2),
    Percentage DECIMAL (10,2),
    )

INSERT INTO #Critical_Alert (Computer, Drive, DiskSpace, UsedSpace,
FreeSpace, Percentage)
SELECT Computer,
       Drive,
       DiskSpace=convert(decimal(28,2),(DiskSize/@GigaByte_Conv)),

UsedSpace=convert(decimal(28,2),((DiskSize-FreeSpace)/@GigaByte_Conv)),

       FreeSpace=convert(decimal(28,2),(FreeSpace/@GigaByte_Conv)),
       Percentage
FROM SERVER.DATABASE.dbo.DiskMonitor_DriveSpace
WHERE date > DATEADD(minute,-60,GETDATE())
ORDER BY Computer, Drive
Select * From #Critical_Alert
/***********************
Send E-Mails
***********************/
DECLARE @Rowcount INT
SET @RowCount = ''
SELECT @RowCount=COUNT(*) from #Critical_Alert
WHILE @RowCount > 0
BEGIN
    IF Percentage <= @Critical_Value
    SET @TEXT = Computer + ' Drive ' + Drive + ' Disk Space Critical! ' +
               FreeSpace + 'GB (' + Percentage + '%) of ' + DiskSpace +
'Remain!'
    Print @TEXT
        EXEC msdb.dbo.sp_send_dbmail
        @recipients='some***@somewhere.com',
        @subject = computer + 'Disk Space Critical',
        @importance = 'High',
        @body = @TEXT,
        @body_format = 'TEXT' ;
    SET @RowCount = @@ROWCOUNT
END

Drop Table #Critical_Alert

[/code]