locked
where is my store procedure RRS feed

  • Question

  • I created a new SP, parsed and executed. But my application can not find it, and I didn't see it in my database either. But SQL server 2008 R2 says it existed already. How can I check it out ?  Thanks
    Sunday, February 26, 2012 4:35 PM

Answers

  • You can find your procedures by executing the following query in your database.

    select * from sys.procedures

    Also Sql server does not automatically refresh the database objects, hence you need to refresh your Database by just right click and select refresh, then you will get your SP under Database -> Programmability -> Store procedures.


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Hasham NiazEditor Monday, February 27, 2012 5:29 AM
    • Marked as answer by Maggie Luo Tuesday, March 6, 2012 7:24 AM
    Sunday, February 26, 2012 4:58 PM

All replies

  • Right clickk the "Stored Procedure" folder and use "Refresh" to show the latest changes to that folder. Your sproc should be there.

    If your app can't find it, it might be a permission issue. Make sure to grant "execute" permission to the user or role the app is using when calling the sproc.

    Sunday, February 26, 2012 4:39 PM
  • You can find your procedures by executing the following query in your database.

    select * from sys.procedures

    Also Sql server does not automatically refresh the database objects, hence you need to refresh your Database by just right click and select refresh, then you will get your SP under Database -> Programmability -> Store procedures.


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Hasham NiazEditor Monday, February 27, 2012 5:29 AM
    • Marked as answer by Maggie Luo Tuesday, March 6, 2012 7:24 AM
    Sunday, February 26, 2012 4:58 PM
  • Perhaps  you have created it under  'master' database....

    Use dbname

    GO

    CREATE PROCEDURE....


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

    Monday, February 27, 2012 5:50 AM
    Answerer
  • But SQL server 2008 R2 says it existed already.
    How, exactly, is "says" this?

    www.sqlCMD.ru - all around MS SQL Server

    Monday, February 27, 2012 7:12 AM
  • Execute the following and confirm that your SP is listed or not !

     

    select name from sys.procedures


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, February 27, 2012 7:15 AM
  • Possibly the procedure was created in a schema which is not the default schema of the person trying to execute the procedure. Try specifying the schema, in the format EXEC schema_name.proc_name

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, February 27, 2012 4:12 PM
    Answerer