Mssql ошибка 2812

• 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: 35547

  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: 35547

  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: 35547

  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]

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.