Scott,
I was having this problem and found your comment through google. I think I know what our German friends were experiencing and if not, at least we have a documented case of the print command not executing in ms sql 2005. Below is the code where the print command did not function and did not send anything to the client. In the code below the prints of the grand totals after the cursor is closed were not printing at all. Some of the subtotals were not printing also. The problem is doing math operations on a null field. For some reason when the math is done on a null field the print command refuses to execute. In many cases it shows no error message, the print command just does not execute. As I worked to shake this problem lose, I did get a "Domain Error" message on one iteration. That is what lead me to the null conclusion. When I removed all the nulls from the fields involved in the math commands it started to run perfectly. It's a little lame of MS to not print some kind of error message on the null operation or at least say that it could not execute the print command. This was a small table on a $50 personal copy of ms sql 2005. So in this case isnull(xxx,0) was the answer.
... Flash Gordon
BEGIN
declare @xTotPaid decimal(14,2), @xTotCurrVal decimal(14,2),
@xDaysDiff decimal(10,1), @xpr decimal (10,1), @xCat char(15),
@xPrior char(15), @xSymbol char(10),
@xSubTotPaid decimal(14,2),
@xSubTotCurr decimal(14,2),
@xGrdTotPaid decimal(14,2),
@xGrdTotCurr decimal(14,2), @xGain decimal(14,2),
@xprin varchar(133), @xcompnm char(40),
@xprin2 varchar(133)
set @xSubTotPaid = 0
set @xSubTotCurr = 0
set @xGrdTotPaid = 0
set @xGrdTotCurr = 0
declare inv_cursor cursor for
select primarycat,
symbol,
companyname,
totbuyprice,
totcurrvalue,
convert(decimal, (datediff(day,buydate,getdate())))
from portfolio
order by primarycat, companyname
open inv_cursor
FETCH NEXT FROM inv_cursor
into @xCat, @xsymbol, @xcompnm, @xtotpaid, @xtotcurrval, @xdaysdiff
set @xprior = @xcat
set @xtotpaid = case when @xtotpaid = 0 then 1 else @xtotpaid end
while @@FETCH_STATUS = 0
BEGIN
if @xprior = @xcat
BEGIN
-- regular processing logic
set @xpr = @xdaysdiff/365
--set @xgain = (((@xtotcurrval/@xtotpaid)-1)*100) / @xpr
set @xprin = @xcat + ' ' + @xsymbol + ' ' + @xcompnm + ' '
+ convert(varchar,cast(@xtotpaid as money),1)+ ' '
+ convert(varchar,cast(@xtotcurrval as money),1) + ' '
+ convert(varchar, (dbo.fn_ComputeReturn(@xtotpaid,@xtotcurrval,@xday sdiff))) + '% '
+ convert(varchar,@xpr)
print @xprin
END
ELSE
BEGIN
-- control break logic
print ' '
set @xprin = 'SubTotal for: ' + @xprior + ' Is: '
+ convert(varchar,cast(@xsubtotpaid as money),1) + ' '
+ convert(varchar,cast(@xsubtotcurr as money),1)
print @xprin
print ' '
set @xsubtotcurr = 0
set @xsubtotpaid = 0
set @xpr = @xdaysdiff/365
--set @xgain = (((@xtotcurrval/@xtotpaid)-1)*100) / @xpr
-- print the first line of the next set or it drops
set @xprin = @xcat + ' ' + @xsymbol + ' ' + @xcompnm + ' '
+ convert(varchar,cast(@xtotpaid as money),1)+ ' '
+ convert(varchar,cast(@xtotcurrval as money),1) + ' '
+ convert(varchar, (dbo.fn_ComputeReturn(@xtotpaid,@xtotcurrval,@xday sdiff))) + '% '
+ convert(varchar,@xpr)
print @xprin
END
set @xsubtotcurr = @xsubtotcurr + @xtotcurrval
set @xsubtotpaid = @xsubtotpaid + @xtotpaid
set @xgrdtotcurr = @xgrdtotcurr + @xtotcurrval
set @xgrdtotpaid = @xgrdtotpaid + @xtotpaid
set @xprior = @xcat
FETCH NEXT FROM inv_cursor
into @xCat, @xsymbol, @xcompnm, @xtotpaid, @xtotcurrval, @xdaysdiff
END
close inv_cursor
deallocate inv_cursor
-- print final subtotal
--set @xprin2 = convert(varchar, @xgrdtotcurr)
--print ' final total ' + @xprior + ' '
-- loses its mind from this point on, won't print any more
--+ convert(varchar,@xsubtotcurr)+ ' '
--+ convert(varchar, @xsubtotcurr)
--print ' '
--print ' Grand Total is '
--+ convert(varchar, @xgrdtotpaid) + ' '
--+ convert(varchar, @xgrdtotcurr)
print ' '
set @xprin2 = 'SubTotal for: ' + @xprior + ' Is: '
+ convert(varchar,cast(@xsubtotpaid as money),1) + ' '
+ convert(varchar,cast(@xsubtotcurr as money),1)
print @xprin2
print ' '
set @xprin = ' Grand Total Is: '
+ convert(varchar,cast(@xgrdtotpaid as money),1) + ' '
+ convert(varchar,cast(@xgrdtotcurr as money),1)
print @xprin
--set @xprin = ' Grand Total Is: '
-- + convert(varchar,cast(@xgrdtotpaid as money),1) + ' '
-- + convert(varchar,cast(@xgrdtotcurr as money),1)
--print @xprin
-- final total current value
--exec investments.dbo.grandtotal
END
|
