CODEWITHSUNDEEP
sql-serverstored-procedures
Kyle
Kyle

Reputation: 17677

Dropping and recreating procedures (SQL Server)

I'm working with a product, that during upgrades drop's and recreates all of the existing procedures (basically just finding all of the proper proc's from the INFORMATION_SCHEMA and drops then) then re-creates all of the new ones. I believe the intent is to ensure all of the procedures are up to date.

The problem that I am currently running into, is that on a really slow customers database, the script that recreates all of the procedures fails stating that the procedure already exists. It looks like for some reason or another, the database server has not caught up.

I have tried adding a DBCC FREEPROCCACHE in between the drops and the creates, but still to no avail. And ideas on how I can get this working?

Upvotes: 0

Views: 262

Answers (2)

Paddy
Paddy

Reputation: 33857

As an aside to my answer, a possibly more robust way of doing this would be to have scripts for each sproc that does something like:

IF SPROC EXISTS
THEN
DROP IT
GO

CREATE SPROC
GO

This keeps each drop/creation as one item, and you can be more sure that the sproc is actually being deleted...

(Incidentally - is there any chance that your big deletion script is looking for everything in the dbo schema, and this database accidentally has a sproc created in another schema)?

Upvotes: 1

Mathias F
Mathias F

Reputation: 15891

Check if the proc belongs to a different schema than expexted.

Something like myaccount.MyProc if it is expected to be dbo.MyProc

Upvotes: 3

Related Questions