存储过程导致ODBC调用失败-通过查询 [英] ODBC Call Failed with stored procedure - Pass through query

问题描述

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

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

我能够在sql服务器数据库上成功执行查询，但是当涉及到存储过程时，我会收到以下错误消息:

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 call Failed"

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

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-调用失败"原因的更多信息.错误，我们可以遍历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调用失败-通过查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！