ODBC 调用失败，存储过程 - 通过查询

我创建了一个传递查询并尝试从中调用存储过程.

I have created a pass through query and trying to call a stored procedure from it.

我能够成功地在 sql server 数据库上执行查询，但是当涉及到存储过程时，我收到一个错误:

I am able to execute the queries on sql server database sucessfully but when it comes to stored procedures, i am getting an error as :

“ODBC 调用失败"

问题仅在于存储过程.查询执行良好.

The problem is with stored procedures only. The queries are executing fine .

这是我的代码:

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

如果我遗漏了什么，请告诉我?

推荐答案

获取有关ODBC–调用失败”原因的更多信息.error 我们可以遍历 DBEngine.Errors 集合，看看是否还有其他可能更具描述性的消息.例如，用代码

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

我们可能会在 VBA 立即窗口中看到以下内容:

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.

当然

对象SAMPLE_TEST”、数据库myDb”、架构dbo”的 EXECUTE 权限被拒绝.

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

比仅仅有用得多

ODBC–调用失败.

ODBC–call failed.

这篇关于ODBC 调用失败，存储过程 – 通过查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，