SP should return null, but it is returning something else

Question

Post reply

SP should return null, but it is returning something else

Michelle Cook

SSCrazy

Points: 2119
More actions
October 27, 2006 at 12:56 pm

#174992

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michelle Cook SSCrazy Points: 2119 More actions · Answer 1

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

set @error2 = ''

there for @error2 IS NOT NULL

this should do it :

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

begin

--mail

end

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 3

@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

Michelle Cook SSCrazy Points: 2119 More actions · Answer 4

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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

Michelle Cook SSCrazy Points: 2119 More actions · Answer 6

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

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...