The Stored Procedure
I have a stored procedure on SQL Server 2000. Depending on the @Action parameter the sproc will INSERT, UPDATE, or DELETE a record. It then checks for an error, commits the transaction, and returns 1. This works perfectly from SQL Query Analyzer.
我在SQL Server 2000上有一个存储过程。根据@Action参数,sproc将INSERT,UPDATE或DELETE一条记录。然后它检查错误,提交事务,并返回1.这完全可以从SQL查询分析器中运行。
CREATE PROCEDURE dbo.spWeb_GoodsInRequestProducts
@Action Int, -- 0 = Insert, 11 = Update, 2 = Delete
@GoodsInRequestRef Int,
@ProductRef Int,
@Qty Int = Null
AS
Set NoCount On
Declare @Error Int
Begin Transaction
If @Action = 0 Begin
INSERT INTO dbo.tblGoodsInRequestProducts (
GoodsInRequestRef,
ProductRef,
Qty
) VALUES (
@GoodsInRequestRef,
@ProductRef,
@Qty )
End
If @Action = 1 Begin
UPDATE dbo.tblGoodsInRequestProducts
SET Qty = @Qty
WHERE GoodsInRequestRef = @GoodsInRequestRef
AND ProductRef = @ProductRef
End
If @Action = 2 Begin
DELETE FROM dbo.tblGoodsInRequestProducts
WHERE GoodsInRequestRef = @GoodsInRequestRef
AND 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 an asp classic vbscript web page I have an ado command that passes some parameters to and executes the sproc. This works fine if the @Action is set to 0 (INSERT record), but set to 1 (UPDATE) or 2 (DELETE) and the page times out.
然后在asp经典的vbscript网页上,我有一个ado命令,它将一些参数传递给sproc并执行它们。如果@Action设置为0(INSERT记录),但设置为1(UPDATE)或2(DELETE)并且页面超时,则此方法正常工作。
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 = Nothing
Set objCmd = Nothing
End Function
Response.Write GoodsInProduct
I also have a near identical sproc, called from a near identical ado command on the same page, that will insert, update, and delete without any fuss. Running SQL Profiler, I see the INSERT commands come through, but I never see the UPDATE or DELETE, the page just times out. As I have said, it all works perfectly in the SQL Query Analyzer.
我也有一个几乎相同的sproc,在同一页面上从一个几乎相同的ado命令调用,它将插入,更新和删除而不用大惊小怪。运行SQL事件探查器,我看到INSERT命令通过,但我从未看到UPDATE或DELETE,页面只是超时。正如我所说,它在SQL查询分析器中完美运行。
Is anyone able to see what I am doing wrong? Even just an alternative or some way of improving the error checking so I can see where the error occurres.
有人能够看到我做错了吗?即使只是改进错误检查的替代方法或某种方法,我也可以看到错误发生的位置。
Thank you.
谢谢。
Things Tried
I have just tried the bare minimum and still times out on the web page but works in SQLQA:
我刚刚在网页上尝试了最低限度但仍然超时,但在SQLQA中工作:
Dim strSQL
strSQL = "DELETE FROM dbo.tblGoodsInRequestProducts WHERE GoodsInRequestRef = 1 AND ProductRef = 10110"
Dim objConn
Set objConn = server.CreateObject("ADODB.Connection")
objConn.Open CONN
objConn.Execute (strSql)
objConn.Close
Set objConn = Nothing
1 个解决方案
#1
0
SOLVED
For anyone in a similar situation: Remember to close SQL Enterprise Manager, SQL Query Analyzer, and SQL Profiler if you run into any problems.
对于处于类似情况的任何人:如果遇到任何问题,请记得关闭SQL企业管理器,SQL查询分析器和SQL事件探查器。
I still don't fully understand the complex inner workings of SQL Server, but I had somehow caused a backlog of TRANSACTIONS that had not been committed. After closing the programs above, the transactions all completed and things worked as expected.
我仍然不完全理解SQL Server的复杂内部工作方式,但我以某种方式导致了尚未提交的积压的TRANSACTIONS。关闭上述程序后,所有事务都已完成,事情按预期工作。
If anyone understands what I had done wrong then please let us all know so that others can stay clear of this happening.
如果有人理解我做错了什么,那么请让我们都知道,以便其他人可以避免这种情况发生。
#1
0
SOLVED
For anyone in a similar situation: Remember to close SQL Enterprise Manager, SQL Query Analyzer, and SQL Profiler if you run into any problems.
对于处于类似情况的任何人:如果遇到任何问题,请记得关闭SQL企业管理器,SQL查询分析器和SQL事件探查器。
I still don't fully understand the complex inner workings of SQL Server, but I had somehow caused a backlog of TRANSACTIONS that had not been committed. After closing the programs above, the transactions all completed and things worked as expected.
我仍然不完全理解SQL Server的复杂内部工作方式,但我以某种方式导致了尚未提交的积压的TRANSACTIONS。关闭上述程序后,所有事务都已完成,事情按预期工作。
If anyone understands what I had done wrong then please let us all know so that others can stay clear of this happening.
如果有人理解我做错了什么,那么请让我们都知道,以便其他人可以避免这种情况发生。