Microsoft KB Archive/175359

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Knowledge Base


FIX: Force Index Option Fails with Temporary Tables in Stored Procedures

Article ID: 175359

Article Last Modified on 3/14/2006



APPLIES TO

  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition



This article was previously published under Q175359

BUG #: 16815 (6.5) 9986 (6.0)

SYMPTOMS

If the "force index" Optimizer hint is used on a query against a temporary table, the query may fail with the following error:

Error 308 Severity 16
Index %s on table %s (specified in the from clause) does not exist.

This error message occurs if both of the following conditions are true:

  • The query is within a stored procedure.


-and-

  • The temporary table was created outside that stored procedure.

Note that the stored procedure will work if it is run under the same connection it was created on. Also, if it is run successfully under the same connection it was created on, then it will always work for all connections after that.

WORKAROUND

The stored procedure runs correctly if you EXEC it in the same batch in which the stored procedure is created. However, if you execute the stored procedure from another session, it must be recompiled (as when the server restarts).

A more permanent workaround is to submit the SELECT statement within the stored procedure by using the SQL Server 6.0 EXEC feature, rather than a straight SELECT.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a


For more information, contact your primary support provider.

MORE INFORMATION


Additional query words: proc

Keywords: kbbug kbfix kbusage KB175359