I have a stored procedure on a SQL 2000 instance that creates 2 tables
from various other tables and then does a comparison of each column to
determine mismatched records. This proc runs fine from SQL QA and
returns all data with no errors but when I use the ADO provider to dump
it into my Excel SS, it hits my error handler which returns:

Microsoft OLE DB Provider for ODBC Drivers
-2147467259
[Microsoft][ODBC SQL Server Driver]Error in row

It was working fine for months with no issues. I updated the proc a few
weeks ago and I only changed one routine. I've since tried commenting
out that section of the proc and running it but it still hits the error
handler. I can't figure out how to troubleshoot it. I've compared the
datatypes between the 2 comparison tables and I've specifically cast to
matching datatypes any fields that might cause a problem. Is there
anything else I can add to the routine to help narrow down what the
error is? I can post the proc if it will help but it's about 800 lines
long.

Here is my VBA routine:

Sub RunQuery()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim lLastRow As Long, n As Long
Dim lNumRecs As Long, i As Long

Set cn = New ADODB.Connection
cn.Open "Driver={SQL
Server};Server=SERVER\INST;Trusted_Connection=yes; Database=MYDB"


'Execute the stored procedure into a returned record set
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "Compare_TABLES" 'Name of stored procedure
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
Set rst = cmd.Execute()

On Error GoTo Err_Trap

Set rst = cmd.Execute()
Range("A1").CopyFromRecordset rst

Do
lLastRow = ActiveSheet.Range("a65536").End(xlUp).Row

n = n + 1

If Abs(n Mod 2) = 0 Then
Range("A" & lLastRow).Font.Bold = True
Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
Else
Range("A" & lLastRow).Offset(2, 0).CopyFromRecordset rst
End If

Set rst = rst.NextRecordset
DoEvents
Loop Until rst.State < 1


Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing

ActiveSheet.Columns.AutoFit

Exit Sub

Err_Trap:

Debug.Print Err.Source & vbCrLf & Err.Number & vbCrLf & _
Err.Description
MsgBox Err.Source & vbCrLf & Err.Number & vbCrLf & Err.Description

Set rst = Nothing
Set cmd = Nothing
Set cn = Nothing

End Sub