The information in this article applies to:
SYMPTOMS
The WITH RECOMPILE clause for stored procedures does not function
properly if the objects referenced by the stored procedure are
temporary tables created outside the stored procedure but in the
same session.
returns these correct results before the table is changed:
However, if the table is changed as follows:
then the stored procedure p1 returns:
And a simple SELECT on table #t returns:
If you also use the WITH RECOMPILE clause when executing the stored procedure, the problem still exists. CAUSESQL Server incorrectly recompiles the stored procedures created with the recompile option if the referenced objects are temporary tables. WORKAROUNDUse permanent tables in place of temporary tables, or drop and recreate the stored procedure if the temporary tables are dropped and recreated. STATUSMicrosoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2 and Microsoft SQL Server versions 4.21 and 4.21a . We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available. Additional query words: stored procedure
Keywords : kbprg kbbug4.20 kbbug4.21 kbbug4.21a SSrvServer SSrvWinNT |
Last Reviewed: March 16, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |