• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

MSSQL print shows a value but cannot display value otherwise

Can some one tell  why this print commands succeed but  fails when an attempt is made to  display the values otherwise  .

Will show only part of the script for brevity .

Within a loop  the information is echo ed  to make sure it is passed properly and it appears it is.
But when the same information is included  in a set statement it fails
any directions will be appreciated


BEGIN
..
..
LOOP

print @drives
print @Totals
print @DiskFreeSpace
print @percentage
 
set @MailSubject =  +@@servername +'   Drive ' + @drives + '  is low on  Disk  space '
--
set @AlertMessage =  ' Drive  '  + @drive + '  ,  has   '  +@DiskFreeSpace+  ' MB left out of ' +@TotalSize+ ';'
..
..
END LOOP
END
 

Output
C
102398
38804
37
Msg 245, Level 16, State 1, Line 77
Conversion failed when converting the varchar value ' Drive  L  ,  has   ' to data type int.

 
0
mngong_rc
Asked:
mngong_rc
  • 6
  • 5
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
you need to cast your numeric values:

set @AlertMessage =  ' Drive  '  + @drive + '  ,  has   '  + cast(@DiskFreeSpace as varchar) +  ' MB left out of ' +cast(@TotalSize as varchar)+ ';'
0
 
mngong_rcAuthor Commented:
Thanks that appeared to be the issue  

This is also the other part that was not clear

select * from  #drive_space


drive FreeSpace   TotalSize   Percent_free
----- ----------- ----------- ------------
C     17993       31980       56
D     44015       69884       62
L     38804       102398      37
M     90348       204797      44
N     232338      358395      64
O     201915      204797      98
P     102331      102398      99

But  passing this information through a cursor brings the values all mixed up


DECLARE DriveSpace CURSOR FAST_FORWARD FOR
SELECT
drive,
FreeSpace, --as 'Total(MB)',
TotalSize ,--as 'Free(MB)',
 Percent_free
 FROM #drive_space ORDER BY drive
open DriveSpace
fetch next from DriveSpace into @drives ,@DiskFreeSpace ,@Totals ,@percentage --(values defined earlier)
WHILE (@@FETCH_STATUS = 0)
Begin
if @percentage<90
Begin
print  'The drive letter is  +cast (@drives  as varchar)+'
print  'The total is +cast(@Totals as varchar)+'
print  'Total Diskspace available is +cast (@DiskFreeSpace  as varchar)+'
print 'The total percentage is +cast (@percentage as varchar)+'


End
fetch next from DriveSpace into  @Drive ,@DiskFreeSpace,@Totals,@percentage
End
close DriveSpace



These returns the values all jumbled up and for some reason skips the c drive which is the first

drive
----- ----------- ----------- ------------
D     44014       69884       62
L     38804       102398      37
M     90348       204797      44
N     232339      358395      64
O     201915      204797      98
P     102331      102398      99


0
 
Éric MoreauSenior .Net ConsultantCommented:
is there a problem with your copy-paste?

this line (all the others too) :
print  'The drive letter is  +cast (@drives  as varchar)+'

should read:
print  'The drive letter is '  +cast (@drives  as varchar)

and why do you have:
fetch next from DriveSpace into @drives , ...
fetch next from DriveSpace into  @Drive , ...

it is not the same variable!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mngong_rcAuthor Commented:
Thanks for the quick response will provide the information again for clarity
Made the corrections suggested with the quotes . The main problem
was the results not coming in order with the cursors for example
the C drive did not show up on the list or will have the total space for the
L drive. Could be missing something with the cursor statement.




Variables are declared before statement is run
Select * from table gives the following result .

select * from  #drive_space


drive FreeSpace   TotalSize   Percent_free
----- ----------- ----------- ------------
C     17994       31980       56
D     44014       69884       62
L     38804       102398      37
M     90348       204797      44
N     232339      358395      64
O     201915      204797      98
P     102331      102398      99

Now a cursor is created to process this information but it does not return the rows as expected
see below

DECLARE DriveSpace CURSOR FAST_FORWARD FOR
SELECT
drive,
FreeSpace, --as 'Total(MB)',
TotalSize ,--as 'Free(MB)',
 Percent_free
 FROM #drive_space ORDER BY drive
open DriveSpace
fetch next from DriveSpace into @drives ,@DiskFreeSpace ,@Totals ,@percentage
WHILE (@@FETCH_STATUS = 0)
Begin
if @percentage<90
Begin
print  'The drive letter is  +cast (@drives  as varchar)+'
print  'The total is +cast(@Totals as varchar)+'
print  'Total Diskspace available is +cast (@DiskFreeSpace  as varchar)+'
print 'The total percentage is +cast (@percentage as varchar)+'

End
fetch next from DriveSpace into  @Drive ,@DiskFreeSpace,@Totals,@percentage
End
close DriveSpace
deallocate DriveSpace





0
 
Éric MoreauSenior .Net ConsultantCommented:
you have the very same 2 errors I explained before. can you please fix them?
0
 
Éric MoreauSenior .Net ConsultantCommented:
and only drive O and P should be outputted right?
0
 
mngong_rcAuthor Commented:
The issue was a bad substitution which better scrutinized should not have even come up.
The first issue you pointed out was very pertinent . The reason for the inconsistency of the
results was bad substitution

@Drive instead of @drives

Thanks for your time and help
MNT
0
 
mngong_rcAuthor Commented:
Found a typo

instead of drives there was Drive  and that was the reason for the failure.

MNT
0
 
Éric MoreauSenior .Net ConsultantCommented:
This is exactly what i meant with

and why do you have:
fetch next from DriveSpace into @drives , ...
fetch next from DriveSpace into  @Drive , ...

it is not the same variable!
0
 
mngong_rcAuthor Commented:
There you go ! That was the typo ..

0
 
mngong_rcAuthor Commented:
Thanks quick and helpful responses
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now