Microsoft KB Archive/135470

{|
 * width="100%"|

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:


 * 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.
 * 1) 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.
 * 1) 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  as   Begin   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  as   Begin  exec Get_Account_Information_Part2  /** Keep these parameters exactly the same as the exec statement that called Get_Account_Information    **/ End Create Procedure Get_Account_Information_Part2  as  Begin  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