Disappearing Stored Procedure

Question

So, not sure what is happening. But I have stored procedure and it keeps disappearing out of my DB in SQL 2k.

I can add it again and then try to execute it from my web app and i get an exception saying the stored procedure cant be found. So then ill go back to management and refresh and its gone again !?!

here is the config for the stored proc:

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[USP_Equipment_Delete]
    @EquipmentID int
AS

DELETE FROM [dbo].[Equipment]
WHERE
    [EquipmentID] = @EquipmentID

None of my other stored procedure disappear. This is the only one. I have easily 100 in there. They all use the same SQLHelper class. This one just keeps disappearing!!!??!!

Any help or suggestions are appreciated!

Thanks so much!

Answer 1

Do you have a CREATE PROCEDURE anywhere? You can't ALTER a procedure if it doesn't exist.

Answer 2

Perhaps the code to access the stored procedure is using a different context other than dbo. Make sure to add dbo.USP_Equipment_Delete to the code using it.

Answer 3

Are you "using" the correct database?

Try using database name above where you're executing your stored procedure, just to make sure.

Answer 4

So what does this return?

sp_help USP_Equipment_Delete

Answer 5

Check if the "Initial Catalog" in your connection string is set to the correct database.

Put the database in single user mode (and make sure you're the single user) and check if the procedure still disappears every hour?

Answer 6

Perhaps there's a job thats restoring an old backup periodically?

Answer 7

You were creating this or another stored proc and at the end of your code, maybe after a comment, where you did not see it you have a drop of this proc.

Take a look at your db using:

select syo.name
from syscomments syc
    join sysobjects syo on
        syo.id = syc.id
where syc.[text] like '%DROP PROC%'

Answer 8

If it's there, then this query must return a record:

SELECT * FROM sysobjects 
WHERE id = OBJECT_ID('USP_Equipment_Delete') 
      AND OBJECTPROPERTY(id, N'IsProcedure') = 1

Answer 9

Did you fix it? I am having the same problem with SQL 2008

CREATE PROCEDURE insertarVisita @email varchar(50), @IP ntext, @url ntext, @timeStarted char(25) AS INSERT INTO registroDeVisitas (email, IP, ID, url, timeStarted) VALUES
( @email,@IP, NEWID(),@url, @timeStarted) GO

Answer 10

I had the same problem and I just fixed it:

In the script file it was missing the "GO" statement between the end of the stored procedure and the beginning of the next "IF EXIST THEN DROP" statement.

So what happened was that the drop statement was getting appended to the end of whatever stored procedure was above it in the script. So when the software ran the stored procedure it would drop whatever stored procedure was below it in the script.

It seems so obvious to us now but didn't make any sense at the time. We found it running the SQL profiler against a customer's database that was having the problem in the field.