ADO Command running multiple SQL statements: can't get error message back: USE THE Connection.Errors collection
ado, adodb, ms-access, sql, vba
Solution
Please see http://support.microsoft.com/kb/254304
PRB: ADO Errors Collection Does Not Contain User-Defined Error Messages
Executing a SQL Server stored procedure (SP) by using ActiveX Data Objects (ADO) does not populate the ADO Errors collection of the Connection object with user-defined errors that are raised in the SP. This behavior only occurs when using the OLE DB Provider for SQL Server (SQLOLEDB) to establish an ADO connection to the SQL Server database.
You have to explicitly set nocount on at the beginning of your sql script.
Problem
I have some triggers on various tables that use raiserror to tell the user that he tried to do something bad. I would like to run several inserts and updates atomically, rolling back if one of the triggers says so. I was evil in a former life so I'm using Access VBA. If the first statement is at fault, I get a run time error in my VBA. However if the second or subsequent statement calls raiserror then I can't see any error message. SOLUTION: examine the connection.Errors collection. This is an Access 2007 ADP front-end and a SQL 2005 database. The VBA below is the bare minimum to show the effect. I get the same effect if I wrap all my atomic statements in a stored proc. I thought i was being clever, putting my validation as close to the database as possible. Obviously not! If you care to try out the code below, swap the order of s1 and s2 in CommandText and admire the different results. ``` 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 ``` I've tried various combinations. In the stored proc below I'm explicitly (re-) raising an error. However, that error doesn't get passed back to 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 ``` The VBA code to call this new proc is... ``` 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 ```