Error: Unable to open table at querey execution time.

[Ruairi]

I get this message returned from SQL Server when calling an SP from visual basic. In the code below i commented out the select into #logsthisload statement and the error goes away. This stored procedure is called as part of the process of ensuring that conversions are present for data in a file before trying to import the data. There can be many loads in a file and this procedure is called once per load. The error was occuring after running through the SP many times, but i am dropping the temp table each time so the space it uses should be released, right?
Note: all of this is occuring between a BeginTrans and a conditional Rollback or commit from VB.
Any ideas on what is going on much appreciated.
TIA

--12/15/2000 added temp table with only logs from load. whole log table taking too long 
--select rec, speciesrec, graderec, sortsrec, defectrec, grosslen, grossdiam, netlen, netdiam, tgrosslen, tgrossdiam, tnetlen, tnetdiam, grossv, netv, enetv, pgrossv, logcost, logvalue, loadsrec into #logsthisload 
--from logs where loadsrec = @loadsrec and (statusrec = 1 or statusrec = 3) 
-- end addition 

-- select logs for loads where statusrec = 1, normal or statusrec = 3, sold
declare logs_cursor cursor for
   select rec, speciesrec, graderec, sortsrec, defectrec, grosslen, grossdiam, netlen, netdiam, tgrosslen, tgrossdiam, tnetlen, tnetdiam, grossv, netv, enetv, pgrossv, logcost, logvalue from logs where loadsrec = @loadsrec and statusrec = 1 or statusrec = 3 --#logsthisload 
select @logscount = 0
select @loadcost = 0
-- if scale data is to be created, then search for logs for a load
if (@createscale = 'Y')
begin
   open logs_cursor
   fetch next from logs_cursor into @rec, @speciesrec, @graderec, @logssortsrec, @defectrec, @grosslen, @grossdiam, @netlen, @netdiam, @tgrosslen, @tgrossdiam, @tnetlen, @tnetdiam, @grossv, @netv, @enetv, @pgrossv, @logcost, @logvalue
   while (@@fetch_status = 0)
   begin
-- here, set individual log cost/value
       exec set_log_cost @voucher, @contractortyperec, @contractorrec, 
           @vendorrec, @brandname, @brandsrec, @sortsrec, @dag, @destinationrec, 
           @vbdpricerec, @loadsrec, @gross, @weight, @paytyperec, @scaleflagrec, 
           @merchpayrate, @utilitypayrate, @yardticket, @speciesrec, @graderec, 
           @logssortsrec, @defectrec, @grosslen, @grossdiam, @netlen, @netdiam, 
           @tgrosslen, @tgrossdiam, @tnetlen, @tnetdiam, @grossv, @netv, @enetv, 
           @pgrossv, @volume output, @price output, @cost output, 
           @gradetyperec output, @cdatabaserec
       -- accumulate total volume
       if (@gradetyperec = 2 or @gradetyperec = 3) -- utility or cull grade
           select @putility = @putility + @volume
       else
           select @pvolume = @pvolume + @volume
       -- end accumulate total volumes
       -- update log cost/value, if updateflag = 'U' or 'A'
       if (@updateflag = 'U') -- update log cost
           if (@contractortyperec = 5)
               select @logvalue = @cost
           else
               select @logcost = @cost

       if (@updateflag = 'A') -- add log cost
           if (@contractortyperec = 5)
               select @logvalue = @logvalue + @cost
           else
               select @logcost = @logcost + @cost
       -- if contractortype is 4, vendor, accumulate cost into species table
       if (@contractortyperec = 4)
           update species set vamount = vamount + @cost where rec = @speciesrec
       -- if contractortype is 3, stumpage, accumulate cost into species table
       if (@contractortyperec = 3)
           update species set samount = samount + @cost where rec = @speciesrec
       -- accumulate volumes for the species
       if (@accumulatespecies = 1)
           update species set gross = gross + @grossv where rec = @speciesrec
       -- end update log cost
       -- update load cost/value
       select @loadcost = @loadcost + @cost
       -- count logs
       select @logscount = @logscount + 1
       -- set logs cost/value
       update logs set logcost = @logcost, logvalue = @logvalue where loadsrec = @loadsrec and rec = @rec -- current of logs_cursor
       fetch next from logs_cursor into @rec, @speciesrec, @graderec, @logssortsrec, @defectrec, @grosslen, @grossdiam, @netlen, @netdiam, @tgrosslen, @tgrossdiam, @tnetlen, @tnetdiam, @grossv, @netv, @enetv, @pgrossv, @logcost, @logvalue
   end
   close logs_cursor
end
deallocate logs_cursor

-- 12/15/2000 addition 
--drop table #logsthisload
-- end addition

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com