Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSSQL  print shows a value but cannot display value otherwise

Posted on 2011-10-13
11
Medium Priority
?
229 Views
Last Modified: 2012-06-27
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
Comment
Question by:mngong_rc
  • 6
  • 5
11 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 36963042
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
 

Author Comment

by:mngong_rc
ID: 36964142
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 36964217
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:mngong_rc
ID: 36964339
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 36964593
you have the very same 2 errors I explained before. can you please fix them?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 36964601
and only drive O and P should be outputted right?
0
 

Author Comment

by:mngong_rc
ID: 36964676
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
 

Author Comment

by:mngong_rc
ID: 36964795
Found a typo

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

MNT
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 36964825
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
 

Author Comment

by:mngong_rc
ID: 36964835
There you go ! That was the typo ..

0
 

Author Closing Comment

by:mngong_rc
ID: 36964953
Thanks quick and helpful responses
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question