Stored procedure gets deleted just prior to use

I have a very simple stored procedure which deletes a row from a table.


This procedure and the web application which calls it was working perfectly fine for weeks. Then starting yesterday, whenever my application calls this procedure, the application fails saying that the procedure can't be found. When I go look on the server, it is indeed gone. The stored procedure is deleted off the server.

I have not made any code changes to this procedure or the class method that calls it (although I have madechanges to related classes in the site). I have 30 or so other procedures on the site which still all work without any problems. Just this one procedure is having this problem.

I have re-created this procedure a dozen times, verified, its existence, and have even run it directly on the server with no problems. On my test (local) server I have the exact same procedure and web application -- and it runs fine without any problems.However, as soon as I run the application on DiscountASP's server, WAM~, the procedure gets deleted and the app fails

I have run a dotnet trace on my end usingTry/Catch, but the results offer very little information other than the procuedre not being found.

I started a HelpDesk Ticket at Discount, but they are telling me this is a programming issue. I am really scratching my head on this one.

This is very strange. Anybody have any idea whats going on, or has anyone run into this before?

Here is the code for the procedure, followed by the code that calls it in my dotnet app:

 

Hi Larry,
Why are you running Drop for the SQL at all?
Is there any reason you don't want to keep that procedure available?
Salute,
Mark

 

Hi Mark;


Thats just the original creation script.


-Larry

 

OK...
You can restructure that with more parameters to clear up the chance it will drop all the time but...
I'd suggest just running this on your remote SQL Server to create the procedure:

 

Thanks Mark;


I created the procedure using your suggestion, however, the problem remains.


I am reading up on commitement control, and there seems to be circumstances where a procedure's updates will be automatically rolled back, and the procedure deleted by the SQL DB Engine if a commit transaction is not explicitly issued. Not sure why the absence of that is notaffecting my other procedures though. Just poking at anything now...


-Larry

 

Hi Bruce;


I am not sure what you mean by 'saving' it.


To CREATE or ALTER the procedure I simply open a query window in SQL Server Management Studio Express, paste the CREATE or ALTER script in it and then runthe query. I am not performaing any kind of save operation. I don't even know what that would be.


I am not really trained in SQL Server so your patience is appreciated. Am I missing a Save operation of some kind?


-Larry

 

Oh man, now you've got me...
I'm going to have to install SQL Express on my kids PC so I can test some of this stuff on it.

My kids are 3, 6 and 9, they use an older XP box to play Zoo Tycoon, man do they have fun with it.
Salute,
Mark

 

Bruce/Mark;


At this point I am pretty well convinced that my problem is a SQL permissions problem.


Can eitherof you suggest a really easy noob article or tutorial or link thatexplains SQL security users/roles/permissions for dumies? I have been fiddling around in Management Studio under the Security menu of my DB and I am not makling heads nor tails of it. So far every tut or article I have seen gets in over my head within the first paragraph.I need something really basic.


This is what I get for being an applications programmer pretending to also be DB admin.


-L

 

You deserve credit for being so honest bro.
OK, I just love SQL Server so helping you with this will in-turn help others.
Reason being, the videos I'm working on right now are all SQL Server for DASP accounts.

If you will, please tell me first...which SQL Server tools are you using locally?

For now,two great resources for ya:
http://search.live.com/macros/sql_server_user_education/booksonline

http://www.sql-server-helper.com/default.aspx
Salute,
Mark

PS - Keep in mind "Books on-line" is just a bad name for a great SQL Server resource.

 

Hey thanks, I appreciate it.


To manage my SQL Server YAF DB I am using:

 

Thanks...You did post more than I needed to know.

btw, give this a shot at times, it's a new Microsoft search engine I'm working on:
http://search.live.com/macros/wisemx/aspnet/

i.e. -> [ SQL Server permissions] Will pull up a ton of articles for you.

 

Lots of promising looking stuff here. Thanks again.


-L

 

Where I come fromgrown adultsplay Grand Theft Auto -- the real life version. Thats why I moved out to where I am now -- where most of my neighbors are four-legged and furry.

 

My lovely Brazilian wife would shoot me.
She agrees with Zoo Tycoon, actually does seem to teach them some skills, with no violence.

 

I've got the latest SQL Server Express on that other machine now and just ran some tests.
Was not able to Import and Export with the Express Management but I was able to use the clipboard.
Created a table locally, would also work with a procedure...
Scripted the table as Create to the Clipboard...
Then with the DASP remote SQL Server 2005 connection open I used the New Query button at the top.
...Pasted my table in from the Clipboard and ran the query.
The remote table was successfully created.

I'm now working on a video of this as it did work out very well.
Salute,
Mark

 

Yes this exactly how I have been creating and altering tables and procedureswith Management Express: the query window. After creation, I then use the Save-As dialogue to save the script to a local project folder as a .sql file.

 

I got my stored procedure to work. Well sort of. I re-created my stored procedure under a totally different name,and now it works fine.

I have been scouring search engines for anybody reportingstoredprocedures in SQL Server 2005which automatically delete themselves upon use.

I found one thread at http://www.codeprof.com/dev-archive/188/19-85-1881884.shtm. In this thread there are two differen tpeople reporting similar problems. Nobody has an explanation or a cause. One of the folks re-created their procedure under a different name and said it works now.

So I did same, and now it works for me too.

This sounds like maybe a MS bug in SQL Server 2005 perhaps?

 

Wow, odd but thanks a lot for posting the problem and solution bro.

 

figures I would be one of the select few who got bit !