sql - ADO Command Timeout when Stored Procedure set to UPDATE or DELETE -

sql - ADO Command Timeout when Stored Procedure set to UPDATE or DELETE -

the stored procedure

i have stored procedure on sql server 2000. depending on @action parameter sproc insert, update, or delete record. checks error, commits transaction, , returns 1. works sql query analyzer.

class="lang-sql prettyprint-override">create procedure dbo.spweb_goodsinrequestproducts @action int, -- 0 = insert, 11 = update, 2 = delete @goodsinrequestref int, @productref int, @qty int = null set nocount on declare @error int begin transaction if @action = 0 begin insert dbo.tblgoodsinrequestproducts ( goodsinrequestref, productref, qty ) values ( @goodsinrequestref, @productref, @qty ) end if @action = 1 begin update dbo.tblgoodsinrequestproducts set qty = @qty goodsinrequestref = @goodsinrequestref , productref = @productref end if @action = 2 begin delete dbo.tblgoodsinrequestproducts goodsinrequestref = @goodsinrequestref , productref = @productref end set @error = @@error if @error = 0 begin commit transaction select 1 end if @error <> 0 begin rollback transaction select 0 end set nocount off go the ado command

then on asp classic vbscript web page have ado command passes parameters , executes sproc. works fine if @action set 0 (insert record), set 1 (update) or 2 (delete) , page times out.

class="lang-vbscript prettyprint-override">function goodsinproduct() dim objcmd set objcmd = server.createobject("adodb.command") objcmd.activeconnection = conn objcmd.commandtype = adcmdstoredproc objcmd.commandtext = "dbo.spweb_goodsinrequestproducts" objcmd.parameters.append objcmd.createparameter("@action", adinteger, adparaminput, , 2) objcmd.parameters.append objcmd.createparameter("@goodsinrequestref", adinteger, adparaminput, , 1) objcmd.parameters.append objcmd.createparameter("@productref", adinteger, adparaminput, , 10110) objcmd.parameters.append objcmd.createparameter("@qty", adinteger, adparaminput, , 8) dim objrs set objrs = objcmd.execute goodsinproduct = objrs(0) set objrs = nil set objcmd = nil end function response.write goodsinproduct

i have near identical sproc, called near identical ado command on same page, insert, update, , delete without fuss. running sql profiler, see insert commands come through, never see update or delete, page times out. have said, works in sql query analyzer.

is able see doing wrong? alternative or way of improving error checking can see error occurres.

thank you.

things tried

i have tried bare minimum , still times out on web page works in sqlqa:

dim strsql strsql = "delete dbo.tblgoodsinrequestproducts goodsinrequestref = 1 , productref = 10110" dim objconn set objconn = server.createobject("adodb.connection") objconn.open conn objconn.execute (strsql) objconn.close set objconn = nil

solved

for in similar situation: remember close sql enterprise manager, sql query analyzer, , sql profiler if run problems.

i still don't understand complex inner workings of sql server, had somehow caused backlog of transactions had not been committed. after closing programs above, transactions completed , things worked expected.

if understands had done wrong please allow know others can remain clear of happening.

sql vbscript asp-classic sql-server-2000 ado