SP should return null, but it is returning something else

  • 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

  • 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

  • set @error2 = ''

    there for @error2 IS NOT NULL

    this should do it :

    IF NULLIF('', @OUT) IS NOT NULL

    begin

    --mail

    end

  • @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

  • I tried removing the @OUT is not null and it still didn't work, besides it was checking for not null OR  <> ''.

  • as we said '' is NOT NULL so it will always return true.

  • I agree but evaluating for @OUT <> '' also returned true, which it shouldn't since it was set to ''.

  • 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