ODBC Call Failed to Execute Stored Procedure - Skip Request
I created a pass through the request and tried to call a stored procedure from it.
I can execute queries on a SQL Server database successfully, but when it comes to stored procedures, I get the error:
"ODBC call Failed"
The only problem is stored procedures. The requests are running fine.
Here is my code:
Dim qdf As DAO.QueryDef, rst As ADODB.Recordset
Dim DatabaseName As String
Dim Server As String
ServerName = "XXXX"
DatabaseName = "XXX"
Set qdf = CurrentDb.CreateQueryDef("")
strConnectionString = "ODBC;DRIVER={sql server};" & _
"DATABASE=" & DatabaseName & ";" & _
"SERVER=" & ServerName & ";" & _
"Trusted_Connection=YES;"
qdf.Connect = strConnectionString
qdf.SQL = " EXEC [dbo].[SAMPLE_TEST]"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
Debug.Print rst!RecordCount
rst.Close
Set rst = Nothing
Please let me know if I am missing something?
source to share
For more information on the cause of the "ODBC Call" failure. we can loop through the collection DBEngine.Errors
and see if there are other messages that might be a little 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 can 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.
Sure,
EXECUTE permission was denied on object "SAMPLE_TEST" in database "myDb" in schema "dbo".
much more useful than just
ODBC - The call failed.
source to share