Stored procedure gets deleted just prior to use

[larrym]

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:


[quote]

IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id(N'[dbo].[yaf_company_delete]')
AND Objectproperty(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[yaf_company_delete]
GO
CREATE PROCEDURE [dbo].[yaf_company_delete](
@CompID INT)
AS
IF ((SELECT COUNT(1)
FROM yaf_User
WHERE Comp_ID = @CompID)) > 0
BEGIN
SELECT Success = CONVERT(BIT,1)
RETURN
END
ELSE
BEGIN
Delete from yaf_company
where companyID = @CompID
SELECT Success = CONVERT(BIT,0)
END
</CODE>


[quote]


static public bool company_delete(object compID)
{
using (SqlCommand cmd = new SqlCommand("yaf_company_delete"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CompID", compID);
return (bool)ExecuteScalar(cmd);
}
}</CODE>
Thanks in advance;

-Larry

[wisemx]

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

[larrym]

Hi Mark;


Thats just the original creation script.


-Larry

[wisemx]

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:

[quote]

/****** Object: StoredProcedure [dbo].[yaf_company_delete] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[yaf_company_delete](
@CompID INT)
AS
IF ((SELECT COUNT(1)
FROM yaf_User
WHERE Comp_ID = @CompID)) > 0
BEGIN
SELECT Success = CONVERT(BIT,1)
RETURN
END
ELSE
BEGIN
Delete from yaf_company
where companyID = @CompID
SELECT Success = CONVERT(BIT,0)
END</CODE>


Then you'll have the procedure available at all times.
Salute,
Mark

[larrym]

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

[bruce]

The stored proc Mark provided should not delete the SP any more. How are you saving your stored proc?


Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[larrym]

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

[wisemx]

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. http://community.discountasp.net/emoticons/tongue.gif

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]

LOL...


Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[larrym]

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

[wisemx]

You deserve credit for being so honest bro. http://community.discountasp.net/emoticons/wink.gif
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. http://community.discountasp.net/emoticons/tongue.gif

[larrym]

Hey thanks, I appreciate it.


To manage my SQL Server YAF DB I am using:
[quote]

Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600</CODE>
To work on YAF customization I am using:


[quote]

Microsoft Visual Studio 2005 Team Edition for Software Developers - ENU Service Pack 1 (KB926601) </CODE>

-Larry

[wisemx]

Thanks...You did post more than I needed to know. http://community.discountasp.net/emoticons/wink.gif

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 (http://search.live.com/results.aspx?q=SQL+Server+permissions)] Will pull up a ton of articles for you.

[larrym]

Lots of promising looking stuff here. Thanks again.


-L

[larrym]

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. http://community.discountasp.net/emoticons/cool.gif

[bruce]

well.. at least they are not playing Grand Theft Auto :>





Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[wisemx]

My lovely Brazilian wife would shoot me. http://community.discountasp.net/emoticons/tongue.gif
She agrees with Zoo Tycoon, actually does seem to teach them some skills, with no violence.

[wisemx]

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

[larrym]

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.

[larrym]

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?

[wisemx]

Wow, odd but thanks a lot for posting the problem and solution bro. http://community.discountasp.net/emoticons/yeah.gif

[bruce]

hmmm.. this is really weird!!!


We host over 10000 SQL databases and I have never heard of this problem. Must be some obsure bug.


Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[larrym]

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