locked
Stored Procedure could not find RRS feed

  • Question

  • Few users are getting ‘could not find stored procedure error’ for several SPs in SQL 2012 even these SPs exist in DBs. 

    if it’s a bug in SQL 2012 and its suggested workaround. Thanks.


    Wednesday, June 10, 2015 7:37 PM

Answers

  • Make sure the schema matches what you're in. For example, the AdventureWorks2012 samples use different schemas (dbo, humanresources, person, sales, ...). If you don't specify the schema, SQL Server will default to your schema which is probably dbo.

    Try looking at the procs with this command in the database in question:

    SELECT OBJECT_SCHEMA_NAME(object_id) as ObjSchema, Name 
    FROM sys.all_objects 
    WHERE type = 'P' and is_ms_shipped = 0


    and compare that with your default schema with this code:

    SELECT default_schema_name, name 
    FROM sys.database_principals
    WHERE name = SUSER_SNAME()

    Wednesday, June 10, 2015 7:52 PM
  • Few users are getting ‘could not find stored procedure error’ for several SPs in SQL 2012 even these SPs exist in DBs. 

    if it’s a bug in SQL 2012 and its suggested workaround. Thanks.


    This is definately NOT A BUG its either mistake from user side or some security restriction. Ofcourse you can share the error message with us and tell us when they are getting this message

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by Katherine Xiong Tuesday, June 16, 2015 7:46 AM
    • Marked as answer by Eric__Zhang Monday, June 22, 2015 5:59 AM
    Thursday, June 11, 2015 6:54 AM
  • Which SP are you talking about? Is that system SP's? 

    • Did you check BOL to confirm that they are carry forward to later version or not?
    • If that SP exists in SQL Server 2012, then make sure you are referring it right.

    If you could not find SP, then it will either be wrong name or that SP doesn't really exists. It has nothing to do with SQL bug.

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    • Proposed as answer by Katherine Xiong Tuesday, June 16, 2015 7:46 AM
    • Marked as answer by Eric__Zhang Monday, June 22, 2015 5:59 AM
    Thursday, June 11, 2015 7:01 AM

All replies

  • Try to add the schema when calling the SP.
    Wednesday, June 10, 2015 7:39 PM
  • Few users are getting ‘could not find stored procedure error’ for several SPs in SQL 2012 even these SPs exist in DBs. 

    if it’s a bug in SQL 2012 and its suggested workaround. Thanks.


    • Merged by Olaf HelperMVP Thursday, June 11, 2015 9:03 AM Same question from same OP
    Wednesday, June 10, 2015 7:41 PM
  • Does users have proper rights on Objects/DB ???

    Can you please share the whole error text ...


    If this answer is helpful to you .. Please mark as Answer....

    Wednesday, June 10, 2015 7:43 PM
  • Hari_Kumar8,

    Pls don't post the same question twice.

    Try to add the schema when calling the SP.

    Wednesday, June 10, 2015 7:44 PM
  • What you are describing is almost certainly a default schema issue on those users.

    Wednesday, June 10, 2015 7:48 PM
  • Make sure the schema matches what you're in. For example, the AdventureWorks2012 samples use different schemas (dbo, humanresources, person, sales, ...). If you don't specify the schema, SQL Server will default to your schema which is probably dbo.

    Try looking at the procs with this command in the database in question:

    SELECT OBJECT_SCHEMA_NAME(object_id) as ObjSchema, Name 
    FROM sys.all_objects 
    WHERE type = 'P' and is_ms_shipped = 0


    and compare that with your default schema with this code:

    SELECT default_schema_name, name 
    FROM sys.database_principals
    WHERE name = SUSER_SNAME()

    Wednesday, June 10, 2015 7:52 PM
  • Are they getting this error after running SELECT * FROM sys.procedures WHERE.... command?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 11, 2015 5:16 AM
  • Few users are getting ‘could not find stored procedure error’ for several SPs in SQL 2012 even these SPs exist in DBs. 

    if it’s a bug in SQL 2012 and its suggested workaround. Thanks.


    This is definately NOT A BUG its either mistake from user side or some security restriction. Ofcourse you can share the error message with us and tell us when they are getting this message

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by Katherine Xiong Tuesday, June 16, 2015 7:46 AM
    • Marked as answer by Eric__Zhang Monday, June 22, 2015 5:59 AM
    Thursday, June 11, 2015 6:54 AM
  • Which SP are you talking about? Is that system SP's? 

    • Did you check BOL to confirm that they are carry forward to later version or not?
    • If that SP exists in SQL Server 2012, then make sure you are referring it right.

    If you could not find SP, then it will either be wrong name or that SP doesn't really exists. It has nothing to do with SQL bug.

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    • Proposed as answer by Katherine Xiong Tuesday, June 16, 2015 7:46 AM
    • Marked as answer by Eric__Zhang Monday, June 22, 2015 5:59 AM
    Thursday, June 11, 2015 7:01 AM
  • You can see the below discussion :

    http://dba.stackexchange.com/questions/23983/could-not-find-stored-procedure-even-though-the-stored-procedure-have-been-cre


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Wednesday, June 24, 2015 5:22 PM