Stored Procedure fails to carry out delete.

[joeller]

I have a SS2005 database with a stored procedure that is called from ASP.net. This SP is supposed to read the contents of a row in a table, place the contents into output parameters and then delete that row. When the stored procedure is run from Management Studio. Everything works as designed. However when the stored procedure is called from ASP.Net 2.0, the delete does not occur, and the table becomes very large very quickly. This is not the first time I've seen this. A former co-worker had the same thing occur in ASP.Net 1.1 and SS2K. In Addition I have another stored procedure that outputs the contents of a table as a resultset and then deletes the table that works just fine when called from code. What would cause this Stored Procedure to fail in this manner. Here is the SP:

Create PROCEDURE [dbo].[ut_RetrieveSessions_sp]
(
@Guidval varchar(50),
@UserID varchar(75)out,
@strPW varchar(20) out
)
AS

SET NOCOUNT ON

SET @UserID = (Select UserID From tblJumpToNet WHERE GUID = @Guidval)
SET @strPW = (Select strPW From tblJumpToNet WHERE GUID =@Guidval)
DELETE FROM tblJumpToNet WHERE GUID = @Guidval
SET NOCOUNT OFF

Return

[joeller]

And by the way, it does not fail consistently. That stored procedure has been called 50 to 100 times since I last cleared it out but the table had only 25 entries.

[ck9663]

if you can monitor the times it failed it would be best. if you're sure that it just failed, grab the value of those paramaters and do a test. display the stored proc call from your client side and there could be something on the parameter.

what i usually do is do a response.write thevariable...then do an immediate response.end....then copy the entire displayed string and paste it to management studio...run it...if it did not fail, something else is wrong...

keep us posted...

-- CK