Can't Drop Stored Procedure

Posted on 2011-10-02
Last Modified: 2012-05-12
Hello Experts - I'm experiencing some strange behavior - for some reason I can't update a stored proc in SQL Server 2008 R2 Express. WHen I run an ALTER command, it runs fine, but if I execute the query, it keeps giving me the old version. When I try to drop the procedure, it tells me it doesn't exist. Then if I run it again, it runs fine, and with the old version. Can't update it, can't drop it - HUH??

Any ideas?

Question by:tablaFreak
    LVL 59

    Expert Comment

    by:Kevin Cross
    Is it possible, it is in another database? i.e., did you accidentally create the procedure under master? What does the ALTER statement look like, how are you calling it when it works -- just really need the name part, i.e., schema.procname or db.schema.procname depending on how you are calling it...maybe something is in that to give us a clue.
    LVL 17

    Expert Comment

    mwvisa1 brings up a good point that i run into frequently.

    Also, can you update anything else in the database? Is it possible that the procedure is in a different namespace? e.g. myname.spMyProcedure instead of dbo.spMyProcedure
    LVL 3

    Expert Comment

    Probably you run it in the wrong DB. Try to use :

    USE [YourDBName]

    Open in new window

    when ALTER or RUN your SP.

    Hopei it helps...
    LVL 5

    Expert Comment

    by:Brian Chan
    Just a quick thought, did you check which schema did you set to default for? This happened to one of my developers who came yelling at me one day about why his dropped SP still alive. And what I found is that he was set to default schema as some temp schema instead of dbo. So when he creates or drops objects, SQL Server will look at his default schema in the first place. In no offense, I wish you are not in the same position.
    LVL 5

    Accepted Solution

    I am giving you some queries which will help you
    in which database your required (Corrected Useful ) Stored Procedure residing then
    then you can recognize them then either you want to EDIT or DROP them

    SELECT name, create_date, modify_date
    FROM sys.objects
    WHERE type = 'P'
    AND name = 'your Stored Procedure name'

    Let's say you are searching for 'foobar' in all your stored procedures. You can do this using the INFORMATION_SCHEMA.ROUTINES view, or syscomments:


    Author Comment

    Thanks for your suggestions, and yes, I've fallen prey to not running queries on the right db before, but that wasn't the case this time - when I ran that query you sent, AlokJain0412, two SPs came up with the same name. I had to run the Drop Procedure... query twice, then recreate it for the old version to actually be updated. Must have just been cached somewhere - it's working now, thanks, but I'm still baffled why simply running the 'Alter procecure' statement didn't update it. Mysteries...


    Author Closing Comment

    fixed the problem, but the underlying reason remains a mystery.
    LVL 59

    Expert Comment

    by:Kevin Cross
    The query supplied included neither the CATALOG nor the SCHEMA, so mystery would have been removed by seeing where they lived. Anyway, glad you are fixed now. :)

    Author Comment

    Gotchya - thanks, if it happens again I'll know what to do. Cheers.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SQL Query 2 45
    How to check data in sql table 11 31
    SSRS Columns as Rows in a Table 14 25
    Import csv files to MS SQL 5 38
    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now