Stored procedure returns NULL value to the output parameter IN VBA

Question

I have my stored procedure with an input parameter and an output parameter. I created parameters and was able to execute the job for the first time and received the output value in vJobstatus variable. When I triggered the stored procedure for a second time, I am getting NULL in cmd.Parameters("@vJobStatus").Value.

Please help me to get the value of output parameter. TestJob1 is triggered, but the output value is NULL.

Public pm As ADODB.Parameter
Public cnnStr, StrQuery, vJobName As String
Public RunStatus, vJobStatus As Integer
Global Flag_Chkbox As Integer


Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Run_SQL_Job"
cmd.CommandTimeout = 120
Set pm = New ADODB.Parameter

Set pm = cmd.CreateParameter("@vJobName", adVarChar, adParamInput, 50, "TestJob")
cmd.Parameters.Append pm
      
Set pm = cmd.CreateParameter("@vJobStatus", adInteger, adParamOutput, , vJobStatus)
cmd.Parameters.Append pm
    
cmd.Execute
vJobStatus = cmd.Parameters("@vJobStatus").Value
       
If vJobStatus <> 1 Then
    cnn.Close
    Application.Interactive = True
    Application.StatusBar = ""
    Exit Sub
Else
    cmd.Parameters("@vJobName").Value = "TestJob1"
    cmd.Execute
    vJobStatus = cmd.Parameters("@vJobStatus").Value

CREATE PROCEDURE [dbo].[Run_SQL_Job] 
      @vJobName VARCHAR(50)  
     ,@vJobStatus INTEGER  OUTPUT 
AS

DECLARE @vRunStatus as INT
DeclarE @vJobStats as INT

WAITFOR DELAY '00:00:5';
SET @vRunStatus = (SELECT Runstatus from [dbo].[VIEW])

WHILE @vRunStatus >= 1
 BEGIN
   SET @vRunStatus = (SELECT Runstatus from [dbo].[VIEW])
 END

IF @vRunStatus = 0 
  BEGIN
    EXEC msdb.dbo.sp_start_job @vJobname 
    WAITFOR DELAY '00:00:10';
  END
  
SET @vRunStatus = (SELECT Runstatus from [dbo].[VIEW])
    
WHILE @vRunStatus >= 1
 BEGIN
   SET @vRunStatus = (SELECT Runstatus from [dbo].[VIEW])
 END

SELECT @vJobStatus = run_status FROM msdb.dbo.sysjobhistory
             where instance_id = (SELECT max(instance_id) from msdb.dbo.sysjobhistory where step_name = @vJobName)
GO

There are at leat four different ways to return values from stored procedures. Please post your stored procedure code so we know how you're doing it. — Nick.Mc, Oct 10 '21 at 22:46
@GSerg, when I used Debug.print cmd.Parameters("@vJobStatus").Value, it is NULL — Sree, Oct 10 '21 at 22:54
And how do you know that the NULL is not what actually came from the stored procedure? — GSerg, Oct 10 '21 at 22:56
I ran the sql statements manually when the job was running, procedure gives either 1 or 0 but not NULL. — Sree, Oct 10 '21 at 23:04
Add `set @vJobStatus = isnull(@vJobStatus, 42)` to the end of your procedure and see if you get 42 in VBA. — GSerg, Oct 10 '21 at 23:08
Accirding to this, you appear to have all the syntax correct. https://stackoverflow.com/questions/34145873/using-output-parameter-from-sql-server-stored-procedure-in-access managing SQL Agent jobs is a bit of a nightmare. I suggest you literally run this SP manually at the same time and confirm it doesn't return NULL — Nick.Mc, Oct 11 '21 at 00:24
`vJobStatus` should be declared as a `Long` if you are using `adInteger`. `adInteger` is a 4 byte integer. — Ryan Wildry, Oct 11 '21 at 15:45

Stored procedure returns NULL value to the output parameter IN VBA

0 Answers0