Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.sqlserver.programming,microsoft.public.vb.database.ado
|
|||
|
|||
Error in row -2147467259 calling sql stored proc from excel 2003
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 |
#2
Posted to microsoft.public.vb.database.ado,microsoft.public.excel.programming,microsoft.public.sqlserver.programming
|
|||
|
|||
Error in row -2147467259 calling sql stored proc from excel 2003
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" wrote: 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 |
#3
Posted to microsoft.public.vb.database.ado,microsoft.public.excel.programming,microsoft.public.sqlserver.programming
|
|||
|
|||
Error in row -2147467259 calling sql stored proc from excel 2003
joel wrote:
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" wrote: 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 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. |
#4
Posted to microsoft.public.vb.database.ado,microsoft.public.excel.programming,microsoft.public.sqlserver.programming
|
|||
|
|||
Error in row -2147467259 calling sql stored proc from excel 2003
"Matt Williamson" wrote in message ... 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. I hate it when that happens. :-) -ralph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Progress from Stored proc in Excel | Excel Programming | |||
run stored proc from excel with a parameter | Excel Programming | |||
executing stored proc from Excel | Excel Programming | |||
execute stored proc in Excel | Excel Programming | |||
Calling stored proc with parameters .. | Excel Programming |