Q135470 - FIX:Lvl 16 Error on Temp Tbl From SP w/

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

Q135470

The information in this article applies to:

Microsoft SQL Server version 6.0

BUG# NT: 10213 (6.00)

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:

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.

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.

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

You can reduce the size of the above stored procedure by creating two stored procedures each having a size less than 64 pages. Keep the name of this stored procedure the same as the original one to avoid code changes to the scripts that call this stored procedure.


   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

If the original stored procedure has an OUTPUT parameter, return that parameter back from Get_Account_Information_Part2 to Get_Account_Information and then back to the caller of Get_Account_Information.

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.

Additional query words: sql6 sproc

Keywords : kbprogramming
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600