查询sys表时MS Access与SSMS执行存储过程返回结果不一致问题

问题现象 #

• 当向临时表#views插入虚拟数据'BLAH'时，SSMS与Access返回结果一致：

  SSMS result: @Status = 'ERROR'
  MS Access result: @Status = 'ERROR'

• 当向#views插入来自sys.objects和sys.sql_modules的真实数据时，结果出现差异：

  SSMS result: @Status = 'ERROR'
  MS Access result: @Status = 'OK'

存储过程代码 #

ALTER PROCEDURE dbo.TestSP 
    @Status nvarchar(255) OUTPUT
AS
BEGIN
    IF OBJECT_ID('tempdb..#views') IS NOT NULL
       DROP TABLE #views

    IF OBJECT_ID('tempdb..#GSSTables') IS NOT NULL
       DROP TABLE #GSSTables

    CREATE TABLE #views (objectName nvarchar(100))
    CREATE TABLE #GSSTables (objectName nvarchar(100))

    SET @Status = 'OK'

    INSERT INTO #views (objectName)
        SELECT -- 此分支会导致Access中@Status返回'OK'
            a.name
        FROM
            sys.objects a WITH (NOLOCK)
        INNER JOIN
            sys.sql_modules b WITH (NOLOCK) ON a.object_id = b.object_id
        WHERE
            a.type = 'V'
            AND b.definition LIKE '%OPENQUERY(GSS%,%'

        -- select -- 此分支会导致Access中@Status返回'ERROR'
        --  'BLAH' 
    
    IF EXISTS (SELECT * 
               FROM #views v 
               LEFT JOIN #GSSTables g ON v.objectName = g.objectName 
               WHERE g.objectName IS NULL)
    BEGIN
        SET @Status = 'ERROR'
        RETURN
    END

    IF OBJECT_ID('tempdb..#views') IS NOT NULL
       DROP TABLE #views

    IF OBJECT_ID('tempdb..#GSSTables') IS NOT NULL
       DROP TABLE #GSSTables
END

Access触发存储过程的VB代码 #

Dim con As ADODB.Connection, cmd As New ADODB.Command, prm As New ADODB.Parameter
Dim Status As String

If Not EstablishConnection(con) Then
   ErrHandler glbLastErrNum, glbLastError
   GoTo ExitLabel
End If

Set cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "TestSP"

Set prm = cmd.CreateParameter("@Status", adVarChar, adParamOutput, 255)
cmd.Parameters.Append prm

cmd.Execute

Status = cmd.Parameters("@Status")
MsgBox Status

内容的提问来源于stack exchange，提问作者TheMortiestMorty