Hi,

I have a SQL database that accepts data loads from Lotus Notes thru
ADO calls. We have several tables and from the ADO connection these
tables are populated thru stored procedures(with parameters). Inside
each stored procedures, we are doing proper error handling within
transactions and returning an error code if it encounters any errors
and rolling back. If the insert/update is success, we are returning a
value to designate the whether an insert or update was done to the
calling ADO component.

The issue is at times, the loads fails, but from the ADO side, the
insert/update code was returned even though the data is not inserted
nor updated. We are able to re-produce this when we have a time
situation due to a table locking from the ADO side executing these
procedures.

Here is the basic syntex we have inside each procedure.
--================================================
-- inside procedure
Declare @iError Int,
@iStep Int

SEt @iError = 0

BEGIN TRAN

[ Check table for existence of data] if exists
BEGIN
[ update data]
set @iError = @@Error
set @iStep = -1
END

ELSE

BEGIN
[insert data]
set @iERROR = @@Error
Set @iStep = -2
END

IF @iError <> 0
BEGIN
ROLLBACK Tran
RaisError ...
END

ELSE

BEGIN
Commit Tran
Set @iERROR = @iStep
END

Return @iERROR

--================================================

We even tried doing another error check after the commit and we still
get the same error!!!

So, my assumption is that when the procedure executes fine and issues
the commit statement and returns the right code to say whether an
insert/update was performed. After that, when SQL server is trying to
commit the transaction, it fails to commit it and rolls back(may be
because of a table lock or connection error). So, from the ADO side,
there is no way to catch this error outside the scope of the
procedure.

If I am correct, is there a way to check if a commit is successful? or
is it an issue on the ADO side or from the way we do the error
handling on the procedure side.

If I am wrong, can anyone suggest any reason for this error?


Any comments/suggestions would be greatly appreciated...

Thank you all for your time...
Regards,
Aravin.

Query Builder (querybuilder (AT) gmail (DOT) com) writes:
A couple of things. First, add SET NOCOUNT ON to your procedure. This is
likely to help. But you should also code your client code to loop
over .NextRecordset until it returns Nothing.

An INSERT or UPDATE does not generate a proper result set, but it
generates a row count, and this row count shows up as a closed recordset
in ADO. Any errors that occurrs after the rowcount has produced, will
not show up until you move to the next recordset.

With NOCOUNT ON in effect, these rowcounts are not produced, why the
errors are likely to show up directly.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx