Error 2812

Troubleshooting SQL Server

Troubleshooting

Error 2812

Severity Level 16
Message Text

Could not find stored procedure '%.*ls'.

Explanation

An attempt was made to execute a stored procedure that does not exist. If the procedure does exist (it appears when sp_help is run with no parameters), the error might have occurred because you did not fully qualify the procedure name. If the procedure is not owned by the user attempting to execute it, and it is not owned by the database owner (dbo), all references to it must include the owner name. For example, suppose user1 creates a procedure called proc1. Any users must add the owner name before the procedure name, as shown in the following example:

EXECUTE user1.proc1

Naming conventions for database objects are as follows:

[[[server_name.][database_name].][owner_name].]object_name

The default value for database_name is the current database; the default value for owner_name is the current user. If the current user is not the owner, the current user must specify the owner name when using the procedure. Because the owner name is part of the object name, two different users can have procedures with the same object name in the same database (for example user1.proc1 and user2.proc1). For more information about naming conventions, see Transact-SQL Syntax Conventions.

The only exceptions to this naming convention are system procedures, which can be executed from any database. System procedures reside in the master database, are owned by the system administrator, and have names that begin with sp_. System procedures reference the system tables for the current database.

Action

If you do not know who owns the procedure, use sp_help to display the owner. If you run sp_help without any parameters, it displays objects owned by other users. To determine which procedures exist in a database and who owns them, use the following:

USE master
GO
SELECT name,owner = USER_NAME(uid) 
FROM sysobjects
WHERE type = 'P'
GO

If the procedure does not appear in the output of this query, the procedure is either in a different database or does not exist.

If you do not own the procedure in question, you can avoid error 2812 by qualifying the procedure name with the owner name, as shown in the following example:

EXECUTE user1.proc1

For procedures used by many users of a database, it is usually easiest if the dbo creates the procedure. This allows any user to find the procedure without specifying an owner name.

If the procedure is not in the database where it is executed, you can avoid this error by fully qualifying the procedure name with the database name, as shown in the following example:

EXECUTE database_1.user1.proc1 

The owner name is not needed if you or the dbo own the procedure. For example:

EXECUTE database_1..proc1 

Execute permission must be provided so that other users can execute this procedure, but no permissions are required to see the text of the procedure.

If this error occurs on system procedures, it might be resolved by running Instmstr.SQL. This reinstalls all system procedures and initializes various other structures.

See Also

Errors 2000-2999

EXECUTE

sp_configure

sp_help