Microsoft sql server ошибка 2812

SQL Server 2016 Developer SQL Server 2016 Enterprise SQL Server 2016 Enterprise Core SQL Server 2016 Standard SQL Server 2017 on Windows (all editions) More…Less

SQL Server 2016 Developer SQL Server 2016 Enterprise SQL Server 2016 Enterprise Core SQL Server 2016 Standard SQL Server 2017 on Windows (all editions) Еще…Меньше

• September 24, 2010 at 12:37 pm

  #238776

  I am able to run the stored procedure in management studio.

  when i create a dataset in BIDS, the metadata came up, but when i execute the stored procedure in a dataset, its giving me the following error.

  «Could not find stored procedure. SQL Server error 2812»

  Thanks for your help guys.

  Satish

• Luke L

  SSC-Dedicated

  Points: 35545

  Make sure the user you are using to connect to the database has permissions to that stored procedure. Also, depending on the version of SQL you are using make sure that you’re using the correct owner/schema to call the stored procedure if it’s needed.

  I.E. in 2000 and the owner is not DBO or the user callign the procedure you must specify the owner Exec owninguser.myprocedure

  in 2005+ make sure you are specifying the schema if it’s not in the default schema of the user attempting to execute the procedure.

  -Luke.

  To help us help you read this[/url]For better help with performance problems please read this[/url]

• SatishAyyar

  SSCrazy

  Points: 2823

  All the permissions are good.Thanks for the reply.

  I found the soultion. Its really crazy, I do not know why and how but it worked.

  I droped the procedure and recreated and it worked like that.

  Thanks

  Satish

• Luke L

  SSC-Dedicated

  Points: 35545

  Were you the one that originally created the procedure? IF not, and this is a SQL 2000 instance, you were not the owner, therefor you would have needed to specify the owner. Since you dropped and recreated the procedure, you are now the owner and do not need to specify the owner, but other users will need to.

  -Luke.

  To help us help you read this[/url]For better help with performance problems please read this[/url]

• SatishAyyar

  SSCrazy

  Points: 2823

  I created the sp first and then I gave exec permissions to other person. so the other person got the error which I pasted. we tried and tried but worked only after I recreated the proc. I was the one who created the proc the first time and the second time also.

• SatishAyyar

  SSCrazy

  Points: 2823

• Luke L

  SSC-Dedicated

  Points: 35545

  satishseth143 (9/24/2010)

  ---

  Its SQL2008

  Thanks for finally posting the version number. Sorry I assumed this was 2000 as that’s what the largest majority of articles I could find on this error number were dealing with. Glad you got it sorted.

  -Luke.

  To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 7 posts — 1 through 6 (of 6 total)

I ran your code using a table from the AdventureWorks2012 database.

create procedure SEL_STUDENT
as
begin
select * from [Person].[Person]
end
go

execute SEL_STUDENT

And it works fine.

Although I have moved GO above execute SEL_STUDENT that is not the issue here as @CoOl points out because you specifically say you execute the stored procedure after your block of code.

The only possible explanation would be that you are querying execute SEL_STUDENT on the wrong database.

Try the following code —

USE [DatabaseName] 
GO

execute SEL_STUDENT

Here, [DatabaseName] is the database where the stored procedure SEL_STUDENT table is stored.

Make sure your table is also stored in the same database or else you would have to modify select * from student to select * from [DatabaseName].[SchemaName].[student]

Additionally, you can use the Object Explorer to identify where your stored procedure has been saved. I am unable to post a snapshot of how to do this as my reputation is below 10.

EDIT : Now that my reputation is in two digits —

Kindly note that [dbo] is the default schema in SQL Server.

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

Сообщение об ошибке: Ошибка 2812: не удалось найти хранимую процедуру sp_password.

Подготовка: скрипт восстановления хранимой процедуры,Пожалуйста, нажмите «Загрузить», чтобы получить его.После загрузки поместите корневой каталог диска C и разархивируйте его.

1. Пуск — Все программы — Microsoft SQL Server — дважды щелкните [Enterprise Manager] (рисунок 1-1).

2. Найдите в [Корневой каталог консоли] — [Microsoft] — [Группа SQL Server] — [(локальная) (Windows)] — [База данных] -щелкните [мастер] -нажмите [Инструменты] — [Анализатор запросов SQL ] (Рисунок 1-2).

3. Скопируйте и вставьте следующее содержимое в [Query Analyzer] и нажмите [►], чтобы выполнить запрос. Внизу отображается [Пакетный запрос завершен] и выполнение завершено (рис. 1-3).

sp_configure ‘allow updates’,1

RECONFIGURE WITH OVERRIDE

4. Очистите содержимое на предыдущем шаге [Анализатор запросов], затем щелкните [Загрузить сценарий SQL]. Во всплывающем окне [Открыть файл запроса] найдите путь к хранилищу файла C, который нужно упомянуть, и выберите [sp_password.sql] — [Открыть] (Рисунок 1-4).

5. После открытия нажмите [►], чтобы выполнить запрос. Внизу отображается [Пакетный запрос завершен] для завершения выполнения (рисунок 1-5).

6. Очистите содержимое на предыдущем шаге [Query Analyzer], затем скопируйте и вставьте следующее содержимое и нажмите [►], чтобы выполнить запрос. Внизу отображается [Пакетный запрос завершен] для завершения выполнения. После завершения выполнения пароль можно изменить обычным образом. Чтобы

sp_configure ‘allow updates’,0

RECONFIGURE WITH OVERRIDE