Error in row -2147467259 calling sql stored proc from excel 2003

  • Thread starter Matt Williamson
  • Start date
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
 
J

joel

Usually to debug I recommend commenting out the On Error statement or
stepping through the code. From what you posted the error is occuring after
the ON Error Statement which means one of the CopyFromRecordSet commands is
failing or lLastRowis too large.

1) Check you worksheet in column A and make sure the data doesn't go to the
LastRow. In excel 2003 it would be 65536
2) Yo may be returning too much data that will excede the number of rows in
the worksheet.
3) Yo umay be returning nothing in the rst. One thing you may try is to
manualy get the data instead of using the macro. you can go to the worksheet
and use the menu

Data - Import External Data - New Database query and then select the command
(query) adCmdStoredProc and see if any data gets returned. You may get a
better error message indicating the problem.


Matt Williamson said:
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
Click to expand...
 
M

Matt Williamson

joel said:
Usually to debug I recommend commenting out the On Error statement or
stepping through the code. From what you posted the error is occuring after
the ON Error Statement which means one of the CopyFromRecordSet commands is
failing or lLastRowis too large.

1) Check you worksheet in column A and make sure the data doesn't go to the
LastRow. In excel 2003 it would be 65536
2) Yo may be returning too much data that will excede the number of rows in
the worksheet.
3) Yo umay be returning nothing in the rst. One thing you may try is to
manualy get the data instead of using the macro. you can go to the worksheet
and use the menu

Data - Import External Data - New Database query and then select the command
(query) adCmdStoredProc and see if any data gets returned. You may get a
better error message indicating the problem.


Matt Williamson said:
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
Click to expand...
Click to expand...

This appears to be the problem

Set rst = cmd.Execute()

On Error GoTo Err_Trap

Set rst = cmd.Execute()

I had it executing 2x. I must have been moving stuff around when I added
in the error trap and missed deleting one of the entries.
 
R

Ralph

Matt Williamson said:
joel wrote:

I had it executing 2x. I must have been moving stuff around when I added
in the error trap and missed deleting one of the entries.
Click to expand...

I hate it when that happens. :)

-ralph
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ado copyfromrecordset and nextrecordset 3
Progress from Stored proc in Excel 5
Call stored procedure in SSMS from Access 2016 doesn't work. 0
executing stored proc from Excel 2
Need help with ADO in Excel 2
Excel 2007: Copy from recordset not working properly 4
Run a Stored Procedure from Excel 12
Executing Stored Procedure 1

Top