October 27, 2006 at 12:56 pm
I am using the SP below to evaluate disk space on a server and send e-mail when the disk space falls below a certain percentage. The SP always sends mail even whent the disk space percentage is above the percentage passed to the procedure. The @OUT parameter should be null or '', but it is returning something else. How can I determine the value of @OUT? The print statement doesn't show anything, I think I can use the CONVERT function to see what char value is being returned, but I haven't figured out how to yet? Any help is appreciated.
declare @out varchar(8000)
declare @From varchar(200)
declare @To varchar(200)
declare @subject varchar(300)
declare @minimumspace int
set @minimumspace = 10
set @From = 'sql.admin@ups-scs.com'
set @To ='critical.sql@ups-scs.com'
set @subject = 'Running out of Hard Disk space on the Server: '+@@servername
exec masterDBA..usp_diskspace @minimumspace,@out OUTPUT
if @OUT is not null or ltrim(rtrim(@OUT))<>''
begin
exec master..usp_send_cdosysmail @From ,@To ,@Subject,@OUT
end
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE usp_diskspace
@Percentagefree int,
@error2 varchar(8000) OUTPUT
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint
SET @MB = 1048576
DECLARE @COUNT int
DECLARE @Maxcount int
DECLARE @error varchar(700)
DECLARE @errordrive char(1)
DECLARE @errortotalspace varchar(20)
DECLARE @errorfreespace varchar(20)
DECLARE @free int
DECLARE @date varchar(100)
declare @query varchar(1300)
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB, ServerName = replace( @query , 'master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
set @maxcount =(select max(id) from #drives)
set @count=1
while @count <=@maxcount
begin
select @errortotalspace =convert(varchar(20),Totalsize), @errorfreespace =freespace, @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),@errordrive=Drive from #drives where id = @count
if @free<@percentagefree
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB ate =' +@date
set @error2=@error2+@error+char(13)
end
else
begin
set @error = 'Server = '+@@servername+': Drive=' + @errordrive+': Percentage free=' +convert(varchar(2),@free)+'% TotalSpace ='+ @errortotalspace +'MB : FreeSpace ='+ @errorfreespace +'MB ate =' +@date
end
set @count=@count+1
end
DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)
GO
October 27, 2006 at 1:13 pm
I was able to resolve the problem by changing the execution of the SP to use ascii(@OUT), as in:
declare @out varchar(8000)
declare @From varchar(200)
declare @To varchar(200)
declare @subject varchar(300)
declare @minimumspace int
set @minimumspace = 10
set @From = 'sql.admin@ups-scs.com'
set @To ='critical.sql@ups-scs.com'
set @subject = 'Running out of Hard Disk space on the Server: '+@@servername
exec masterDBA..usp_diskspace @minimumspace,@out OUTPUT
if ascii(@OUT) is not null or ltrim(rtrim(@OUT))<>''
begin
exec master..usp_send_cdosysmail @From ,@To ,@Subject,@OUT
end
October 27, 2006 at 1:13 pm
set @error2 = ''
there for @error2 IS NOT NULL
this should do it :
IF NULLIF('', @OUT) IS NOT NULL
begin
end
October 27, 2006 at 1:14 pm
@OUT will always be not null, because you initialize it to an empty string in usp_diskspace. Removing the "@OUT is not null" part of your if test should make it work correctly.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
October 27, 2006 at 4:08 pm
I tried removing the @OUT is not null and it still didn't work, besides it was checking for not null OR <> ''.
October 28, 2006 at 6:30 am
as we said '' is NOT NULL so it will always return true.
October 30, 2006 at 6:32 am
I agree but evaluating for @OUT <> '' also returned true, which it shouldn't since it was set to ''.
October 30, 2006 at 6:47 am
From the original code :
if @OUT is not null or ltrim(rtrim(@OUT))<>''
For the statement to return true, only 1 of the 2 parts need to be true, not both of 'em. I'm pretty sure you knew that but just to make sure there are no confusions further down the line...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply