ODBC Call Failed with stored procedure – Pass through query

by

To get more information about the cause of an “ODBC–call failed.” error we can loop through the DBEngine.Errors collection and see if there are other messages that might be a bit more descriptive. For example, with the code

    qdf.Connect = strConnectionString
    qdf.SQL = " EXEC [dbo].[SAMPLE_TEST]"
    qdf.ReturnsRecords = True
    On Error GoTo oops
    Set rst = qdf.OpenRecordset
    Debug.Print rst!RecordCount
    rst.Close
    Set rst = Nothing
    Exit Sub
oops:
    Dim dbeError As Error
    For Each dbeError In DBEngine.Errors
        Debug.Print "(" & dbeError.Number & "): " & dbeError.Description
    Next
End Sub

we might see the following in the VBA Immediate window:

(229): [Microsoft][ODBC SQL Server Driver][SQL Server]The EXECUTE permission was denied on the object 'SAMPLE_TEST', database 'myDb', schema 'dbo'.
(3146): ODBC--call failed.

Certainly

The EXECUTE permission was denied on the object ‘SAMPLE_TEST’, database ‘myDb’, schema ‘dbo’.

is considerably more helpful than just

ODBC–call failed.

More Related Contents:

  1. Calling stored procedure using VBA
  2. How to programmatically create an ODBC Linked Table to a SQL Server View and have it be editable?
  3. SQL Server Passthrough query as basis for a DAO recordset in Access
  4. T-SQL stored procedure that accepts multiple Id values
  5. SQL Server: Query fast, but slow from procedure
  6. Escape a string in SQL Server so that it is safe to use in LIKE expression
  7. Retrieve column definition for stored procedure result set
  8. Dapper.NET and stored proc with multiple result sets
  9. How to list all dates between two dates [duplicate]
  10. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  11. EXEC sp_executesql with multiple parameters
  12. T-SQL Dynamic SQL and Temp Tables
  13. How do I find a stored procedure containing ?
  14. How do I search an SQL Server database for a string?
  15. Handling date in SQL Server
  16. SQL Server history table – populate through SP or Trigger?
  17. Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?
  18. Stored Procedure and Permissions – Is EXECUTE enough?
  19. #DELETE viewing SQL Server table in Access
  20. Avoid Naming User Stored Procedures SP% or SP_%
  21. Execute stored procedure with an Output parameter?
  22. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  23. Stored procedure – return identity as output parameter or scalar
  24. How to find a text inside SQL Server procedures / triggers?
  25. Python – pyodbc call stored procedure with parameter name
  26. SQL Server stored procedure return code oddity
  27. How to make a passthrough / passthru query editable?
  28. Populate Dataset With Table Names From Stored Procedure
  29. How to cleanse (prevent SQL injection) dynamic SQL in SQL Server?
  30. Why can’t Entity Framework see my Stored Procedure’s column information?

Leave a Comment