March 17, 2004 at 7:34 am
Hi
I have a stored procedure that has run fine for a while, suddenly it has an error once a while. The error is: (This error will cause the front end time out.)
Server: Msg 208, Level 16, State 1, Procedure Test9_imp_rpt_revenue_detail_summary, Line 22
Invalid object name '#officecode1'.
The stored procedure creates a lot of the temp tables, and this temp table is the first one. From the SQL trace, we know it has recompiled the SP prior to the error message.
The SP looks like:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE revenue_detail_summary
@office_id INT,
@date_from SMALLDATETIME,
@date_to SMALLDATETIME,
@criteria TINYINT = 0,
@shipments TINYINT = 0,
@index TINYINT = 0
AS
SET NOCOUNT ON
CREATE TABLE #officecode1
(
officeid int
, officecode char(3)
, officename varchar(50)
)
INSERT INTO #officecode1
SELECT office_id,
office_code,
office_name
FROM A_SYSTEM_OFFICES
WHERE (@office_id & office_id > 0)
create table #mawb_id_final
(
office_code char(3)
,mawb_id uniqueidentifier
,mawb_no char(13)
)
……
.......
Does anyone know how to solve this problem?
Thanks
March 19, 2004 at 5:54 pm
A large procedure is memory intensive; lots of temp tables means lots of memory allocation; Intermittent errors caused by reused memory area, like partitioning between functions. Something gets dropped, usually first in means first out...
#officecode1 is created & then not found
What server? System? memory restrictions?
Split procedure into smaller procedures or UDF's. Use global temp tables when needed.
Also, why (alter) instead of (drop & create)?
Coach James
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply