multiple recordset error trapping
From: Dave Vick (thevicksNOSPAM_at_adelphia.net)
Date: 01/07/05
- Next message: Larry: "Re: turning clipboard contents into a variable in a .vbs file"
- Previous message: Thiago Ferreira: "Re: Copy if different"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 07 Jan 2005 11:44:37 -0500
Hi everyone:
I'm on a project where I need to check for errors returned from a stored
procedure. The stored procedure has internal error checking so that it
will try 5 times to execute a query before giving up if it is blocked.
When I test it all in query analyzer it looks like I'm getting multiple
error results, once for each time it tries to run the statement, and
then either the final error or a successful set of rows returned. My
thought was that it wold be similar in the recordset returned to the ASP
page, but in trying to check a next recordset, they are all closed and I
get the same lock timeout error.
Some code might help...
The stored procedure:
___________________________________________________________________
CREATE PROCEDURE testErr
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 1000 -- for testing only
declare @err int, @errCount int
set @errCount = 0
THESTATEMENT:
SELECT * from testTable
set @err = @@ERROR
-- retry 5 times, wait .5 seconds between tries
if (@err = 1222 or @err = 1205) and @errCount = 5
BEGIN
RAISERROR ('I tried and tried but couldnt do it', 10, 3)
return -100
END
if @err = 1222 or @err = 1205
BEGIN
WAITFOR DELAY '00:00:00.5'
set @errCount = @errCount + 1
GOTO THESTATEMENT
END
GO
___________________________________________________________________
The code in my ASP page:
___________________________________________________________________
On Error Resume Next
conn = "DRIVER={SQL
SERVER};SERVER=Franklin;DATABASE=DaveVTests;UID=daveVTest;PWD=tester;"
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = conn
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "testErr"
Set oRS = Server.CreateObject("ADODB.RecordSet")
set oRS = oCmd.execute
' ****************
response.write "1st record set"
do while true
' ****************
' see if got results, if not check for errors
response.write "<p>state: " & oRS.state
if oRS.state = adStateOpen then
oRS.movefirst
do while not oRS.eof
response.write "<p>" & oRS("rowID") & ", " & oRS("fullname")
oRS.movenext
loop
else
Set theErrs = oCmd.ActiveConnection.Errors
If oCmd.ActiveConnection.Errors.Count <> 0 then
For Each oErr In theErrs
response.write "<p>Got the following error:<br>"
response.write "Error number: " & oErr.Number
response.write "<br> " &
oErr.description
response.write "<br>Source: " & oErr.source
Next
End If
end if
' ****************
response.write "<p>getting next record set"
set oRS = oRS.nextRecordSet()
if oRS is nothing then
response.write "<p>no more recordsets"
exit do
end if
loop
___________________________________________________________________
I've got a transaction in query analyzer that I run to update all of the
records in the table in question so that it locks them. The transaction
has a 10 second WAITFOR in it so that I know the stored procedure gets
blocked.
If I just run the ASP without blocking it I get results as expected:
___________________________________________________________________
1st record set
state: 1
1, Fred
2, Fred
3, Fred
4, Fred
5, Fred
6, Fred
7, Fred
getting next record set
no more recordsets
___________________________________________________________________
However, when I run the blocking transaction and then the ASP page I get
what is looking like an infinite number of recordsets, all of which are
closed. Here is a portion of it:
___________________________________________________________________
1st record set
state: 0
Got the following error:
Error number: -2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]Lock request time
out period exceeded.
Source: Microsoft OLE DB Provider for ODBC Drivers
getting next record set
state: 0
Got the following error:
Error number: -2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]Lock request time
out period exceeded.
Source: Microsoft OLE DB Provider for ODBC Drivers
getting next record set
state: 0
Got the following error:
Error number: -2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]Lock request time
out period exceeded.
Source: Microsoft OLE DB Provider for ODBC Drivers
getting next record set
___________________________________________________________________
I thought that eventually I would get to a record set that was open and
be able to show the results, but they are all closed. Can anyone shed
any light on this? Am I doing it correctly or is there something else I
should be testing. I know I'm getting recordsets back or the loop would
exit - right?
Any help would be appreciated.
Thanks
Dave
- Next message: Larry: "Re: turning clipboard contents into a variable in a .vbs file"
- Previous message: Thiago Ferreira: "Re: Copy if different"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
