M
Matt Williamson
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
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