Can't Drop Stored Procedure

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?

Thanks,
Steve
tablaFreakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
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.
0
OriNetworksCommented:
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
0
JHolycloudCommented:
Probably you run it in the wrong DB. Try to use :

USE [YourDBName]
GO

Open in new window


when ALTER or RUN your SP.

Hopei it helps...
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Brian ChanDBACommented:
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.
0
AlokJain0412Commented:
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:
 
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%foobar%'
    AND ROUTINE_TYPE='PROCEDURE'

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tablaFreakAuthor Commented:
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...

Thanks
0
tablaFreakAuthor Commented:
fixed the problem, but the underlying reason remains a mystery.
0
Kevin CrossChief Technology OfficerCommented:
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. :)
0
tablaFreakAuthor Commented:
Gotchya - thanks, if it happens again I'll know what to do. Cheers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.