BUG: CREATE TABLE in Stored Procedure with the Name "sp_" May Fail (196789)
The information in this article applies to:
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.
| Modification Type: | Major | Last Reviewed: | 10/15/2002 |
|---|
| Keywords: | kbBug KB196789 |
|---|
|