VBA通过ADO调用带输出参数的SQL存储过程执行报错求助

核心报错原因 #

• VBA代码中cmd.Parameters.Refresh和手动Append参数冲突：Refresh会自动从数据库拉取存储过程的所有参数定义，后续再手动追加参数会导致参数重复，执行时直接报错。
• 存储过程逻辑缺陷：
  • sp_start_job是异步执行的，硬编码等待10秒无法保证作业一定执行完成，若作业仍在运行，sysjobhistory中不会生成本次执行记录，会导致@vJobStatus返回空值
  • 历史记录查询条件错误：WHERE step_name = 'JOBName-Test'筛选的是作业步骤名，不是作业名，会导致匹配不到正确的执行记录
  • 当传入的作业名不等于JOBName-Test时，没有给@vJobStatus赋值，会返回空值，VBA接收空的整型参数会触发报错
• 权限缺失：数据库连接账号需要有执行msdb.dbo.sp_start_job、查询msdb.dbo.sysjobhistory/msdb.dbo.sysjobs的权限，否则执行会报错。

修复后的VBA代码 #

Public cnn As ADODB.Connection
Public cmd As ADODB.Command
Public pm As ADODB.Parameter
Dim vJobStatus As Integer

Set cnn = New ADODB.Connection
' 替换为实际的数据库连接字符串
cnnStr = "PROVIDER=XXXXX;DATA SOURCE=XXXX;INITIAL CATALOG=XXXX;INTEGRATED SECURITY=XXX"
cnn.Open cnnStr

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Run_SQL_Job_Test"
cmd.CommandTimeout = 120

' 删掉Parameters.Refresh，避免和手动添加参数冲突
Set pm = cmd.CreateParameter("@vJobName", adVarChar, adParamInput, 50, "JOBName-Test")
cmd.Parameters.Append pm          
Set pm = cmd.CreateParameter("@vJobStatus", adInteger, adParamOutput)
cmd.Parameters.Append pm

cmd.Execute
' 处理空值避免报错
If Not IsNull(cmd.Parameters("@vJobStatus").Value) Then
    vJobStatus = cmd.Parameters("@vJobStatus").Value
Else
    vJobStatus = -1 ' 自定义异常状态码
End If

' 资源释放
Set pm = Nothing
Set cmd = Nothing
cnn.Close
Set cnn = Nothing

修复后的存储过程代码 #

CREATE PROCEDURE [dbo].[Run_SQL_Job_Test]  
    @vJobName VARCHAR(50),
    @vJobStatus INTEGER OUTPUT 
AS
BEGIN
    -- 给输出参数默认值，避免空值
    SET @vJobStatus = -99
    DECLARE @JobId UNIQUEIDENTIFIER
    -- 获取作业ID，避免匹配错误
    SELECT @JobId = job_id FROM msdb.dbo.sysjobs WHERE name = @vJobName
    
    IF @JobId IS NULL
    BEGIN
        SET @vJobStatus = -2 -- 作业不存在状态码
        RETURN
    END

    EXEC msdb.dbo.sp_start_job @job_name = @vJobname 
    
    -- 循环等待作业执行完成，替换硬编码等待
    DECLARE @IsRunning INT = 1
    WHILE @IsRunning = 1
    BEGIN
        SELECT @IsRunning = COUNT(*) 
        FROM msdb.dbo.sysjobactivity 
        WHERE job_id = @JobId 
        AND start_execution_date IS NOT NULL 
        AND stop_execution_date IS NULL
        
        IF @IsRunning = 1
        BEGIN
            WAITFOR DELAY '00:00:02' -- 每2秒轮询一次
        END
    END

    -- 正确查询本次作业执行的状态
    SELECT TOP 1 @vJobStatus = run_status 
    FROM msdb.dbo.sysjobhistory
    WHERE job_id = @JobId
    AND step_id = 0 -- step_id=0代表作业整体执行结果
    ORDER BY instance_id DESC
END

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