ADODB doesn't raise SQL errors in VBA

Question

I've noticed that when a query is run in VBA, VBA doesn't always inform errors raised by it. For example if I try to do a bulk insert with a null value in a row that doesn't accept nulls I get this error in SQL Server:

Msg 4869, Level 16, State 1, Line 1
The bulk load failed. Unexpected NULL value in data file row 2, column 7. The destination column (IFSFxRate) is defined as NOT NULL.

However it doesn't raise any errors when I execute it with ADODB, though I noticed that if I do rs.NextRecordset I get an debug window with the error message.

Public Sub ExecuteQuery(sql As String)    Dim Error As ADODB.Error
    Dim Error As ADODB.Error
    Dim rs As ADODB.recordset
    Dim Conn As New ADODB.Connection
    Conn.CommandTimeout = 0
    Call Conn.Open(ConfigFactory.DB_CONNECTION)
    Set rs = Conn.Execute(sql) ' "raiserror ('xyz', 10, 127)"
    Debug.Print Conn.Errors.Count ' Returns 0
    rs.NextRecordset ' The bulk load failed. Unexpected NULL value in data file row 2
    Connection.Close
    Set Connection = Nothing
End Sub

Why is the error not raised on .Execute? Is there something I can do to get the log in these cases?

no, that's the whole code. It does notify of some errors, I've been reading a bit and there seem to be bugs with SQL Server and errors during bulk inserts. However I added an user defined error and still I get nothing. — lisovaccaro
– lisovaccaro, Commented Jan 5, 2016 at 13:45

Ben · Accepted Answer · 2016-01-05 17:29:32Z

1

SQL Server with ADO will generate a recordset for every "result set" which is returned.

That is, for every statement in the batch which either produces a "rows affected" output or an output rowset. You must use Set rs = rs.NextRecordSet to page through the results.

Each error is attached to an individual statement, so they will may not be processed on the client, (and added to the Connection object) until you reach that resultset of that statement.

Generally you won't be interested in empty resultsets, so you will see your code sprinkled with this:

While rs.EOF Do
  Debug.Print "Empty Recordset with " & rs.RecordCount & " rows affected"
  Set rs = rs.NextRecordset
Next

If you are interested, you can get the "rows affected" information by consulting the RecordCount property as above.

Alternatively, you can suppress the empty recordsets by using SET NOCOUNT ON in your SQL statement (or stored procedure).

answered Jan 5, 2016 at 17:29

Ben

35.8k6 gold badges81 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lisovaccaro Over a year ago

just for people having the same problem, SET NOCOUNT ON did the trick. I wasn't able to get multiple RecordSets and use the NextRecordset method though.

Collectives™ on Stack Overflow

ADODB doesn't raise SQL errors in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related