SQL Server 2012 Stored procedure is getting deleted automatically

Question

I am having a strange problem. One of my stored procedure is getting dropped automatically. Its not that I am creating it in a wrong place. Every time I create the SP and execute my web page, it shows. After few min, if I execute the page again, it displays a error message which is due to missing SP. I have tried recreating again and again, and everytime it repeats. I even did restart the machine (Windows Server 2008 R2), but no use. The same procedure is fine on my SQL Server instance on Windows 8 machine. I tried the SQL Server Profiler, but of no use. Don't see any drop procedure.

Also for some strange reason, I am getting a lot of requests every second to my SQL Server which is hosting a dotnetnuke based website. Can someone help me with this.

Thanks

Answer 1

Just had this issue presented by another developer. It turned out that there was a "DROP procedure" included at the end of another SP. To look for such hidden statement in another database SP, run a statement like

select OBJECT_NAME(sc.id), sc.* from syscomments sc where text like '%<name_of_SP>%'

where <name_of_SP> is the SP being dropped.