FIX:Lvl 16 Error on Temp Tbl From SP w/ > 64 Pgs May Cause AV

• Microsoft SQL Server, version 6.0

SYMPTOMS

If you execute a stored procedure that makes reference to objects in TempDB with a query plan size of 64 pages or more after you get a Level 16 Error, a client access violation (AV) may occur.

CAUSE

All of the following conditions must exist for the client to AV:

1. The query plan size of the stored procedure must be greater than 64 pages.

   You can find the size of the stored procedure by compiling the stored procedure and running the DBCC MEMUSAGE command.

2. A Level 16 or greater error message inside or before executing the stored procedure.

   An example of a Level 16 Error message is a SELECT statement that tries to access a non-existent table.

3. The stored procedure must make a reference to an object in TempDB.

WORKAROUND

Reduce the size of the stored procedures such that their individual sizes are less than 64 pages.

EXAMPLE:

Assume that the following stored procedure has a size greater than 64 pages.

   Create Procedure Get_Account_Information <Parameters> as
   Begin
     <Several Data Manipulation Statements say 1 through 10>
     <Several Data Manipulation Statements say 11 through 20>
   End

   Create Procedure Get_Account_Information <Parameters> as
   Begin
      <Data Manipulation Statements 1 through 10>
      exec Get_Account_Information_Part2 <Parameters>
   /**  Keep these parameters exactly the same as the exec
   statement that called Get_Account_Information     **/
   End

   Create Procedure Get_Account_Information_Part2 <Parameters> as
   Begin
      <Data Manipulation Statements 11 through 20>
   End

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 1 for SQL Server version 6.0. For more information, contact your primary support provider.