Very Computer

hi guys,

we keep getting these errors in some of our stored procs.i have some
temporary tables being created in that. it used to run fine but now somehow
we keep getting this error on and off. What could be the problem??

This error is 202 in sysmessages table.

Could some one help me on this.

Thanks
Manoj

Manoj,

 It's probably a SQL bug - can you post a repro script?

 Neil Pike MVP/MCSE
 Protech Computing Ltd

Manoj,

 I can't see anything specific, and without all the tables and data I
couldn't repro it - however this is non-trivial to do.  It does look
like a variant on a known problem - I'll see if I can find the status
of the bug and whether it will make SP5.

 It's well worth you opening a support call with MS PSS and going
through it with them - it won't cost as it's definitely a bug in their
source code.

 PSS ID Number: Q169376
Article last modified on 06-05-1997

6.5

WINDOWS

======================================================================
-----------------------------------------------------------------------
--
The information in this article applies to:

 - Microsoft SQL Server, version 6.5
-----------------------------------------------------------------------
--
BUG #: 16945 (WINDOWS: 6.5)

SYMPTOMS
========

When you execute a stored procedure, it fails and returns the following
error:

   Msg 202, Level 11, State 2
   Internal error -- Unable to open table at query execution time.

CAUSE
=====

The stored procedure does all the following:

 - Creates a permanent table using the CREATE TABLE statement

 - Drops the permanent table

 - Creates a temporary table

 - Declares a cursor on the temporary table

 - Drops the temporary table

WORKAROUND
==========

To work around this problem, do any of the following and then drop and
re-create the stored procedure:

 - Use the SELECT INTO statement to create the permanent table

 - Eliminate either of the DROP TABLE statements

 - Wrap the DECLARE CURSOR within an EXEC()

 - Create the stored procedure WITH RECOMPILE or always execute it with
the
   WITH RECOMPILE option.

STATUS
======

Microsoft has confirmed this to be a problem in Microsoft SQL Server
version 6.5. We are researching this problem and will post new
information
here in the Microsoft Knowledge Base as it becomes available.
======================================================================

 Neil Pike MVP/MCSE
 Protech Computing Ltd

Thanks neil,

 this information is enough for me to feel better  and take up this case
with MS

Once again appreciate your quick response on it.

manoj

Manoj,

 No problem - let me know how you get on.

 Neil Pike MVP/MCSE
 Protech Computing Ltd