asked on 10/2/2011

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

Microsoft SQL Server 2008

Last Comment

tablaFreak

10/3/2011

Kevin Cross

10/3/2011

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.

OriNetworks

10/3/2011

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

JHolycloud

10/3/2011

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...

Member_2_4226667

10/3/2011

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.

ASKER CERTIFIED SOLUTION

AlokJain0412

10/3/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

tablaFreak

10/3/2011

ASKER

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

tablaFreak

10/3/2011

ASKER

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

Kevin Cross

10/3/2011

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. :)

tablaFreak

10/3/2011

ASKER

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