Unable to open table at querey execution time error.

[Ruairi]

I took over development of a system that uses SQL Server 6.5 as the database. One of the functions performed by this system is printing payment vouchers for loads. A load is made up of logs which fall into many price categories. The stored procedures that are used in printing these payment vouchers were taking over a half hour to run, even for loads with only a couple of logs. One of the slow spots i noticed was a cursor on the logs table. It was taking 5 - 10 minutes to fetch the next row from the cursor, even though the cursor was being declared with the clustered index of the table (loadsrec) in the where clause. The logs table is very large (almost 3,000,000 rows). I created a temporary table , #logsthisload, which only contains rows where loadsrec matches the current load. This seemed to solve the problem. Instead of waiting 5-10 minutes for each cursor operation it was taking a few seconds to create the temporary table and response times to the cursor fetch on this small table are subsecond. The problem is that i sometimes get the error "[Microsoft][ODBC SQL Server Driver][SQL Server]Internal Error-Unable to open table at querey execution time.". This error seems to occur every other time i run the procedure. This is the first time i have used temporary tables and i wasnt able to find much info about them in the SQL Server books online. Any help would be greatly appreciated. I can't afford to abandon these changes, the time to print a voucher went from 30 minutes to 30 seconds with this change (and a few others, which made minor improvements). I have included the full text of the stored procedure below. Sorry about the long post.
Thank You.

if exists (select * from sysobjects where id = object_id('dbo.set_logs_cost') and sysstat & 0xf = 4)
	drop procedure dbo.set_logs_cost
GO

create procedure set_logs_cost
@voucher int,
@contractortyperec int,
@contractorrec int,
@vendorrec int,
@brandname varchar (8),
@brandsrec int,
@sortsrec int,
@dag datetime,
@destinationrec int,
@vbdpricerec int,
@loadsrec int,
@updateflag char (1),
@gross int,
@pgross int,
@net int,
@utility int,
@enet int,
@eutility int,
@cu int,
@subst int,
@xp int,
@scu int,
@mism int,
@weight int,
@paytyperec int,
@scaleflagrec int,
@merchpayrate money,
@utilitypayrate money,
@yardticket varchar (12),
@createscale char (1),
@loadsspeciesrec int,
@accumulatespecies int,
@pvolume real output,
@putility real output,
@loadcost money output,
@logscount int output,
@cdatabaserec int
as
-- 12/15/2000 addition 
declare @rec int 
-- end addition

declare @speciesrec int
declare @graderec int
declare @logssortsrec int
declare @defectrec int
declare @grosslen smallint
declare @grossdiam smallint
declare @netlen smallint
declare @netdiam smallint
declare @tgrosslen smallint
declare @tgrossdiam smallint
declare @tnetlen smallint
declare @tnetdiam smallint
declare @grossv int
declare @netv int
declare @enetv int
declare @pgrossv int
declare @logcost money
declare @logvalue money
declare @cost money
declare @price money
declare @volume real
declare @merchvolume real
declare @utilityvolume real
declare @vendorname varchar (40)
declare @sortsname varchar (4)
declare @destinationname varchar (40)
declare @gradetyperec int
declare @msg varchar (255)
--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 #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 

-- if no logs, then update load cost
if (@logscount = 0)
begin
   exec set_load_cost @voucher, @contractortyperec, @contractorrec, @vendorrec,
       @brandname, @brandsrec, @sortsrec, @dag, @destinationrec, @vbdpricerec,
       @gross, @pgross, @net, @utility, @enet, @eutility, @cu, @subst, @xp, @scu,
       @mism, @weight, @paytyperec, @scaleflagrec, @merchpayrate, @utilitypayrate, 
       @yardticket, @pvolume output, @putility output, @cost output, @cdatabaserec
   select @loadcost = @loadcost + @cost
   if (@contractortyperec = 4)
       update species set vamount = @cost where rec = @loadsspeciesrec
   if (@contractortyperec = 3)
       update species set samount = @cost where rec = @loadsspeciesrec
   if (@accumulatespecies = 1) -- accumulate volumes for the species
       update species set gross = @gross where rec = @loadsspeciesrec
end
GO

Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications