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

ID: Q196789

The information in this article applies to:

Microsoft SQL Server version 7.0

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 : SSrvStProc kbbug7.00
Version : WINNT:7.0
Platform : winnt
Issue type : kbbug