Thanks for your response, I removed the drop tables deleted the dataset and
re added it, and get the following error
Invalid object name '#temptable'. (Microsoft SQL Server, Error: 208)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdV

Also we are not doing dynamic SQL.

Here is the actual SP

ALTER Procedure [dbo].[sp_jNewAging]
    @AgingDate smalldatetime,
    @OfficeID int
AS

set nocount on

create table #tAgingDetail (
    cusID int,
    locationID bigint,
    orderID bigint,
    Completed smalldatetime,
    OrderDue money)

insert into #tAgingDetail
select s.cusid, s.locationid, s.orderid, s.issuedate,
s.saletotal - isnull((select sum(p.payamount)
    from payment p
    where p.orderid = s.orderid
    and p.paydate <= @AgingDate), 0) 'OrderDue'
from vnewsales s
where s.officeid = @OfficeID
and s.issuedate <= @AgingDate

create table #tAgingSummary (
    cusID int,
    dTotal money,
    dCurrent money,
    d30 money,
    d60 money,
    d90plus money,
    lpDate smalldatetime,
    lpType varchar(16),
    lpAmount money)

insert into #tAgingSummary (cusID, dTotal, dCurrent, d30, d60, d90plus)
select tad1.cusid, isnull(sum(tad1.OrderDue), 0),
    isnull((select sum(tad2.OrderDue)
        from #tAgingDetail tad2
        where tad2.cusid = tad1.cusid and  
        tad2.Completed >= dateadd(minute, 1, @AgingDate)  - 30), 0),
    isnull((select sum(tad3.OrderDue)
        from #tAgingDetail tad3
        where tad3.cusid = tad1.cusid and
        tad3.Completed between dateadd(minute, 1, @AgingDate) - 60 and @AgingDate-
30), 0),
    isnull((select sum(tad4.OrderDue)
        from #tAgingDetail tad4
        where tad4.cusid = tad1.cusid and
        tad4.Completed between dateadd(minute, 1, @AgingDate) - 90 and @AgingDate
- 60), 0),
    isnull((select sum(tad5.OrderDue)
        from #tAgingDetail tad5  
        where tad5.cusid = tad1.cusid
        and tad5.Completed <= @AgingDate - 90), 0)
from #tAgingDetail tad1
group by tad1.cusid
having isnull(sum(tad1.OrderDue), 0) != 0

--drop table #tAgingDetail

update #tAgingSummary set lpDate = r.entered, lpType = t.typename, lpAmount
= r.recamount
from receipts r
inner join rectype t on r.rectypeid = t.rectypeid
where r.entered <= @AgingDate
and r.recid = (select max(r2.recid) from receipts r2 where r2.cusid =
#tAgingSummary.cusid and r2.rectypeid <= 2)

select c.cusname, c.phone, a.*
from #tAgingSummary a
inner join customer c on c.cusid = a.cusid
order by c.cusname

--drop table #tAgingSummary

set nocount off