Comando ADO que ejecuta varias sentencias SQL: no se puede recuperar el post de error: USE THE Connection.Errors collection

Tengo algunos triggers en varias tablas que usan raiserror para decirle al usuario que intentó hacer algo malo. Me gustaría ejecutar varios insertos y actualizaciones atómicamente, retrocediendo si uno de los factores desencadenantes lo dice. Fui malvado en una vida anterior, así que estoy usando Access VBA.

Si la primera statement es errónea, recibo un error de time de ejecución en mi VBA. Sin embargo, si el segundo enunciado posterior llama a raiserror, entonces no puedo ver ningún post de error.

SOLUCIÓN: examine la connection. Colección de errores.

Este es un front-end ADP de Access 2007 y una database SQL 2005.

El VBA a continuación es el mínimo indispensable para mostrar el efecto. Obtengo el mismo efecto si envuelvo todas mis declaraciones atómicas en un process almacenado.

Pensé que estaba siendo inteligente, poniendo mi validation lo más cerca posible de la database. ¡Obviamente no!

Si le interesa probar el siguiente código, cambie el order de s1 y s2 en CommandText y admire los diferentes resultados.

Public Sub TestSqlErrors2() 'CREATE TABLE [dbo].[tblStuff]([id] [int] IDENTITY primary key NOT NULL,[stuff] [varchar](255) NULL) Dim cmd As ADODB.Command Dim cnn As ADODB.Connection Dim r As Long Dim s1 As String Dim s2 As String s1 = " insert into tblStuff(stuff) values ('aaa') " s2 = " if 1=1 begin raiserror ('me, i''m just a lawnmower',16,1) rollback transaction end " Set cnn = Application.CurrentProject.Connection Set cmd = New ADODB.Command Set cmd.ActiveConnection = cnn 'cnn.BeginTrans 'makes no difference cmd.CommandText = "begin transaction " & s2 & s1 & " commit transaction " cmd.Execute r 'cnn.CommitTrans 'cnn.Errors.count is either 2 or 0 End Sub 

He probado varias combinaciones. En el process almacenado a continuación estoy explícitamente (re) levantando un error. Sin embargo, ese error no se transfiere a VBA.

 --CREATE TABLE [dbo].[tblStuff]([id] [int] IDENTITY primary key NOT NULL,[stuff] [varchar](255) NULL) alter proc AddMoreStuff2 as begin try begin transaction --swap the two statements below around to see the difference --if the first statement is raiserror then a run-time error appears in VBA --if not, no error appears in VBA, although no records are added insert into tblStuff(stuff) values ('aaa') if 1=1 begin raiserror ('me, i''m just a lawnmower',16,1) rollback transaction end commit transaction end try begin catch declare @errormessage varchar(1024) set @errormessage = error_message() raiserror ( @errormessage, 16, 1 ) end catch 

El código VBA para llamar a este nuevo process es …

 Public Sub TestSqlErrors2() Dim cmd As ADODB.Command Dim cnn As ADODB.Connection Dim r As Long Set cnn = Application.CurrentProject.Connection Set cmd = New ADODB.Command Set cmd.ActiveConnection = cnn cmd.CommandText = "exec AddMoreStuff2" cmd.Execute r End Sub 

Por favor, consulte http://support.microsoft.com/kb/254304

PRB: La colección de errores de ADO no contiene posts de error definidos por el usuario

La ejecución de un procedimiento almacenado de SQL Server (SP) mediante el uso de objects de datos ActiveX (ADO) no llena la colección Errores de ADO del object de connection con errores definidos por el usuario que se generan en el SP. Este comportamiento solo se produce cuando se utiliza el proveedor OLE DB para SQL Server (SQLOLEDB) para establecer una connection ADO a la database de SQL Server.

Tiene que establecer explícitamente nocount al comienzo de su script sql.

DAO no genera un error de VBA pero llena la connection. Colección de errores. Entonces puedo verificar esto después de cada llamada a Ejecutar para get la descripción del error. p.ej:

 Dim errorMessage As String Dim e As Object For Each e In cnn.Errors errorMessage = errorMessage & e.Description Next If Len(errorMessage) > 0 Then Err.Raise 64000 - 1, Left(cmd.CommandText, 20), errorMessage End If