c# - Consuming the return value of sp_start_job in .NET -

i hit frustrating problem result of sp_start_job when use in stored procedure, store result in variable, , select variable consumed .net. works when sp run in management studio query window, i'm @ loss wrong.

i have object populated result set of sp:

public class sqlserveragentresult : storedprocresult {     public int? jobstatus { get; set; }     public int? jobstartresult { get; set; } //this property result of sp_start_job     public int? lastjobresult { get; set; } } 

it populated method using data context:

public ilist<sqlserveragentresult> executesqlagent(string jobname, bool? runjob, timespan? pollinginterval) {     const string myname = "usp_run_monitor_agent_job";      list<sqlparameter> spparams = new list<sqlparameter>();      spparams.add(new sqlparameter()     {          dbtype = system.data.dbtype.string,         parametername = "jobname",         value = jobname     });     string sp_exec = "usp_run_monitor_agent_job @jobname";      if (runjob != null)     {         spparams.add(new sqlparameter()         {              sqldbtype = system.data.sqldbtype.bit,             dbtype = system.data.dbtype.boolean,             parametername = "runjob",             value = runjob         });         sp_exec += ", @runjob";     }     if (pollinginterval != null)     {         spparams.add(new sqlparameter()         {             sqldbtype = system.data.sqldbtype.varchar,             dbtype = system.data.dbtype.time,             parametername = "timerdelay",             value = pollinginterval         });         sp_exec += ", @timerdelay";     }      try     {         context.commandtimeout = 120;         list<sqlserveragentresult> result = context.sqlserveragentresult.sqlquery(sp_exec, spparams.toarray()).tolist();          return result;      }     catch (exception ex)     {         log.error(myname + " error", ex);         throw ex;     } } 

it should noted object populated data stored procedure, , jobstatus , lastjobresult correct. however, jobstartresult, should contain result sp_start_job, ever has initialized value variable used in stored procedure (in case, value null). valid result (the sp can decide not run sp_start_job), when sp_start_job finished, result not seem saved variable , object contain initialized value instead.

here sql agent sp:

create procedure usp_run_monitor_agent_job @jobname nvarchar(max), @runjob bit = 1, @timerdelay varchar(50) = '00:00:01'  declare @jobstatus int declare @jobstart int declare @jobresult int declare @sql nvarchar(max) declare @params nvarchar(max) declare @isprocessing bit  set @jobstatus = 0  --works fine in .net set @jobstart = null --if initialized -1, 5, or 43276852349, result see in .net, without exception set @jobresult = 0  --works fine in .net set @isprocessing = 0  set @sql = n'select @jobstatus_out = current_execution_status openrowset(''sqlncli'', ''server=localhost;trusted_connection=yes;'', ''exec msdb.dbo.sp_help_job @job_name = ''''' + @jobname + ''''', @job_aspect = ''''job'''' '')' set @params = n'@jobstatus_out int output'  --check see if job running exec sp_executesql @sql, @params, @jobstatus_out = @jobstatus output;  --check see if job idle if @jobstatus = 4 begin     --check see if should run job     if @runjob = 1     begin         --this next line problem. runs, @jobstart doesn't contain result of sp_start_job when run through .net.         exec @jobstart = msdb.dbo.sp_start_job @job_name = @jobname         set @isprocessing = 1         while @isprocessing = 1         begin             --now need wait job finish             waitfor delay @timerdelay             exec sp_executesql @sql, @params, @jobstatus_out = @jobstatus output;              --4 "idle"             --5 "suspended", means job isn't running won't able re-executed either             if @jobstatus = 4 or @jobstatus = 5                 set @isprocessing = 0         end     end end  set @sql = n'select top 1 @jobresult_out = run_status openrowset(''sqlncli'', ''server=localhost;trusted_connection=yes;'', ''set fmtonly off; exec msdb.dbo.sp_help_jobhistory @job_name = ''''' + @jobname + ''''', @mode = ''''full'''' '') step_id = 0 order run_date desc, run_time desc;' set @params = n'@jobresult_out int output' exec sp_executesql @sql, @params, @jobresult_out = @jobresult output;  --return job statuses select cast(1 bigint) id, @jobstatus jobstatus, @jobstart jobstartresult, @jobresult lastjobresult 

in course of troubleshooting, i've attempted manipulate value of @jobstart after sp_start_job executed. again, i'm able in ssms , receive expected results, .net variable still contain initialized value.

i'm open information can explain why works fine in ssms , not in .net, , how work in .net. thanks.

i revamped repository methods use ado.net call stored procedure. originally, using data context so. whatever reason, correctly populates jobstartresult. so, code works expected now.

however, not know why stored procedure behaves differently when called via data context , called via ado.net.