Stored Procs disappearing from SQL Server


Are you facing a strange problem that your stored procedures are disappearing from your SQL Server for no reason? Here is the solution for magically disappearing stored procs.

I have been facing a problem for last several weeks - one of our stored proc was disappearing from SQL Server database of our web application. This has been happening as soon as we hit a specific page in the website.

Initially, I suspected there may be some DROP PROC script somewhere in the code or in other stored procs/scripts. I did global search all over the code and also in the database. Checked the dependencies to find if there is any such script anywhere and we found there was no place we have a DROP PROC code except in the script that we use to create the stored proc. Our script to create a new stored proc look like this:


IF EXISTS(SELECT * FROM sysobjects WHERE name = 'MyStoredProc' AND Type = 'P')
DROP PROC dbo.MyStoredProc
GO

CREATE PROCEDURE [dbo].[MyStoredProc]
AS
BEGIN
-- script
END
GO


This script was never called directly from the IIS App. Instead, it is executed directly in the SQL Server using the Management Studio.

As soon as we hit a specific page in our website, the above proc was getting deleted. I debugged the code in our local computer and strangely, this does not happen in our development machines. So, what is special in our Windows 2008 production server?

I did Google for this problem and found a lot of people have reported this disappearing stored proc problem. Many people have later updated that they had a wrong script somewhere which caused the deletion of the proc, but that was not the case for us. I made sure there is no place where we call the DROP PROC from the web application.

Strangely, the problem continued for many days. As soon as we hit a page, the stored proc will disappear. Then, we had to manually run the scripts in the server to recreate the proc, but it will disappear again.

My next try was to run the SQL Profiler on the server. I filtered all the statements that contain the proc name/table name. After repeating the few tests, I isolated the problem as follows:

As soon as another specific proc is called, a statement is executed by the IIS AppPool, that executes the following statement:


IF EXISTS(SELECT * FROM sysobjects WHERE name = 'MyStoredProc' AND Type = 'P')
DROP PROC dbo.MyStoredProc
GO


In that page, we are executing the "MyStoredProc" and then we are calling "MyNextStoredProc". As soon as the "MyNextStoredProc" is called, IIS App Pool sends a command to DROP the "MyStoredProc" mysteriously.

It was really strange. I debugged the code line by line and there was no place from the code we are calling the DROP PROC command.

I analyzed the "MyNextStoredProc" to see if there is anything wrong in that proc which may be causing IIS to call the DROP PROC on the other stored procedure. I discovered one thing - we had missed the GO statement at the end of this procedure. Well, how can that be a problem and cause deletion of another procedure?

Just for the heck of it, I added the GO statement at the end of the procedure and ran it. Then I tried visiting the page again. Surprisingly, the problem disappeared. IIS no longer calls the mysterious DROP PROC command and the Stored Proc no longer disappears.

Solution to disappearing stored proc problem


If you are facing this strange problem, here is the solution that worked for me:

1. Use SQL Profiler and find out when is the DROP PROC statement is called on the stored procedure.

2. Find out what statement/proc is executed immediately after the DROP PROC command.

3. Debug the statements/proc that was executed after the DROP PROC command. See if there is anything unusual and make sure there is a GO command at the end.

I still do not have a proper justification on why it happened and why it does not happen always. How could a missing GO statement at the end of stored proc make IIS call DROP PROC command on a previously executed stored proc? If you have a justification, please share below.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: