Microsoft KB Archive/196789

= BUG: CREATE TABLE in Stored Procedure with the Name "sp_" May Fail =

Article ID: 196789

Article Last Modified on 10/15/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q196789



BUG #: 53427 (SQLBUG_70)



SYMPTOMS
Creating a stored procedure in the master database in which both of the following conditions are true


 * procedure name begins with 'sp_'
 * the stored procedure creates and drops a table

and then executing the same stored procedure from some other database fails. The table is created in master and then the DROP TABLE statement fails with the following error message:

Server: Msg 3701, Level 11, State 5, Procedure sp_test, Line 5

Cannot drop the table 'test', because it does not exist in the system

catalog.



WORKAROUND
Avoid using the sp_ prefix for user defined stored procedures. Avoid creating and dropping tables in stored procedures that are prefixed with sp_. If this cannot be avoided, fully qualify the object name. For example, use pubs.dbo.authors.



STATUS
Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words: prodsql

Keywords: kbbug kbpending KB196789

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.