|
|
You're in the
sql server dts
group:How to get job result from sp_start_job
sql server dts:
The return value I get is only whether or not it was able to start the SSIS job. It does not tell me when the SSIS job actually completed (if it did). Is there a way to keep the job connection open and monitor the progress of the SSIS package it is running, and return a value based on the SSIS job's completion? In other words, it is nice to know the sp_start_job started OK, but I need to know how the SSIS job ran (and wait for it to finish) before
On Mar 12, 11:42 pm, Steve Jensen
[quoted text, click to view] <SteveJen...@discussions.microsoft.com> wrote: > I am running an SSIS job from a VB.NET application by calling sp_start_job. > The return value I get is only whether or not it was able to start the SSIS > job. It does not tell me when the SSIS job actually completed (if it did). > > Is there a way to keep the job connection open and monitor the progress of > the SSIS package it is running, and return a value based on the SSIS job's > completion? In other words, it is nice to know the sp_start_job started OK, > but I need to know how the SSIS job ran (and wait for it to finish) before > moving on to other things in my program that depend upon the job completion. You might want to execute sp_help_job after executing sp_start_job. Regards, Enrique Martinez Sr. SQL Server Developer
Could you be more specific? I am using sqlCommand in a VB function. Here is
the code: jobConnection = New SqlConnection(My.Settings.connMaster) jobCommand = New SqlCommand("sp_help_job", jobConnection) jobCommand.CommandType = CommandType.StoredProcedure jobParameter = New SqlParameter("@job_name", SqlDbType.VarChar) jobParameter.Direction = ParameterDirection.Input jobCommand.Parameters.Add(jobParameter) jobParameter.Value = stJob jobReturnValue = New SqlParameter("@execution_status", SqlDbType.Int) jobReturnValue.Direction = ParameterDirection.ReturnValue jobCommand.Parameters.Add(jobReturnValue) jobConnection.Open() jobCommand.ExecuteNonQuery() jobResult = jobReturnValue.Value jobConnection.Close() According to the documentation, the execution_status parameter should show 1 if executing and 4 if idle. The function returns 0, which apparently is the return value of the procedure (success) not the execution status of the job 'stjob'. How can I return the value of the parameter I asked for? [quoted text, click to view] "EMartinez" wrote:
> On Mar 12, 11:42 pm, Steve Jensen > <SteveJen...@discussions.microsoft.com> wrote: > > I am running an SSIS job from a VB.NET application by calling sp_start_job. > > The return value I get is only whether or not it was able to start the SSIS > > job. It does not tell me when the SSIS job actually completed (if it did). > > > > Is there a way to keep the job connection open and monitor the progress of > > the SSIS package it is running, and return a value based on the SSIS job's > > completion? In other words, it is nice to know the sp_start_job started OK, > > but I need to know how the SSIS job ran (and wait for it to finish) before > > moving on to other things in my program that depend upon the job completion. > > > You might want to execute sp_help_job after executing sp_start_job. > > Regards, > > Enrique Martinez > Sr. SQL Server Developer >
Are you creating the "Job" in your program or just executing the existing
"job" would lilke to know since I do not see the full code. However if you want to know the status if you are creating the job in your code use sp_help_job to know the status and other details OR if you want to notify somone check enterprise manager for job properties . Hope this hleps Cheers Sundar [quoted text, click to view] "Steve Jensen" wrote:
> Could you be more specific? I am using sqlCommand in a VB function. Here is > the code: > jobConnection = New SqlConnection(My.Settings.connMaster) > jobCommand = New SqlCommand("sp_help_job", jobConnection) > jobCommand.CommandType = CommandType.StoredProcedure > jobParameter = New SqlParameter("@job_name", > SqlDbType.VarChar) > jobParameter.Direction = ParameterDirection.Input > jobCommand.Parameters.Add(jobParameter) > jobParameter.Value = stJob > jobReturnValue = New SqlParameter("@execution_status", > SqlDbType.Int) > jobReturnValue.Direction = ParameterDirection.ReturnValue > jobCommand.Parameters.Add(jobReturnValue) > jobConnection.Open() > jobCommand.ExecuteNonQuery() > jobResult = jobReturnValue.Value > jobConnection.Close() > > According to the documentation, the execution_status parameter should show 1 > if executing and 4 if idle. The function returns 0, which apparently is the > return value of the procedure (success) not the execution status of the job > 'stjob'. How can I return the value of the parameter I asked for? > > > > "EMartinez" wrote: > > > On Mar 12, 11:42 pm, Steve Jensen > > <SteveJen...@discussions.microsoft.com> wrote: > > > I am running an SSIS job from a VB.NET application by calling sp_start_job. > > > The return value I get is only whether or not it was able to start the SSIS > > > job. It does not tell me when the SSIS job actually completed (if it did). > > > > > > Is there a way to keep the job connection open and monitor the progress of > > > the SSIS package it is running, and return a value based on the SSIS job's > > > completion? In other words, it is nice to know the sp_start_job started OK, > > > but I need to know how the SSIS job ran (and wait for it to finish) before > > > moving on to other things in my program that depend upon the job completion. > > > > > > You might want to execute sp_help_job after executing sp_start_job. > > > > Regards, > > > > Enrique Martinez > > Sr. SQL Server Developer > >
Thanks, I did not include all the code, but the program invokes sp_start_job
to run a job that is set up in the SQL 2005 SQL Server Agent. The code I included is the part that is attempting to invoke sp_help_job to get the status of the job I started with sp_start_job. That part runs on a timed loop every couple of seconds to check to see if the job is finished, as further processing depends upon its completion. The problem is that the return value is always 0. Again, according to the documentation, the return value should be 1 if executing and 4 if idle (which would imply finished). If my code is not correct, what is the correct way to use SQLCommands in a VB.NET function to return the status of the job? [quoted text, click to view] "Sundar Murugappan" wrote:
> Are you creating the "Job" in your program or just executing the existing > "job" would lilke to know since I do not see the full code. > > However if you want to know the status if you are creating the job in your > code use sp_help_job to know the status and other details OR if you want to > notify somone check enterprise manager for job properties . > > Hope this hleps > > Cheers > Sundar > > "Steve Jensen" wrote: > > > Could you be more specific? I am using sqlCommand in a VB function. Here is > > the code: > > jobConnection = New SqlConnection(My.Settings.connMaster) > > jobCommand = New SqlCommand("sp_help_job", jobConnection) > > jobCommand.CommandType = CommandType.StoredProcedure > > jobParameter = New SqlParameter("@job_name", > > SqlDbType.VarChar) > > jobParameter.Direction = ParameterDirection.Input > > jobCommand.Parameters.Add(jobParameter) > > jobParameter.Value = stJob > > jobReturnValue = New SqlParameter("@execution_status", > > SqlDbType.Int) > > jobReturnValue.Direction = ParameterDirection.ReturnValue > > jobCommand.Parameters.Add(jobReturnValue) > > jobConnection.Open() > > jobCommand.ExecuteNonQuery() > > jobResult = jobReturnValue.Value > > jobConnection.Close() > > > > According to the documentation, the execution_status parameter should show 1 > > if executing and 4 if idle. The function returns 0, which apparently is the > > return value of the procedure (success) not the execution status of the job > > 'stjob'. How can I return the value of the parameter I asked for? > > > > > > > > "EMartinez" wrote: > > > > > On Mar 12, 11:42 pm, Steve Jensen > > > <SteveJen...@discussions.microsoft.com> wrote: > > > > I am running an SSIS job from a VB.NET application by calling sp_start_job. > > > > The return value I get is only whether or not it was able to start the SSIS > > > > job. It does not tell me when the SSIS job actually completed (if it did). > > > > > > > > Is there a way to keep the job connection open and monitor the progress of > > > > the SSIS package it is running, and return a value based on the SSIS job's > > > > completion? In other words, it is nice to know the sp_start_job started OK, > > > > but I need to know how the SSIS job ran (and wait for it to finish) before > > > > moving on to other things in my program that depend upon the job completion. > > > > > > > > > You might want to execute sp_help_job after executing sp_start_job. > > > > > > Regards, > > > > > > Enrique Martinez > > > Sr. SQL Server Developer > > >
I am working around the problem right now by getting the current timestamp
from the SQL Server just before running the SSIS job, finding a record of the job in sysjobactivity with a start date greater than the timestamp, then checking the stop date in that record. When it is no longer null, I assume the job is finished. This is not a great solution, however, especially if multiple users run the same job in close succession. The ideal solution would be for the sp_help_job to return the proper execution status value. [quoted text, click to view] "Sundar Murugappan" wrote:
> Are you creating the "Job" in your program or just executing the existing > "job" would lilke to know since I do not see the full code. > > However if you want to know the status if you are creating the job in your > code use sp_help_job to know the status and other details OR if you want to > notify somone check enterprise manager for job properties . > > Hope this hleps > > Cheers > Sundar > > "Steve Jensen" wrote: > > > Could you be more specific? I am using sqlCommand in a VB function. Here is > > the code: > > jobConnection = New SqlConnection(My.Settings.connMaster) > > jobCommand = New SqlCommand("sp_help_job", jobConnection) > > jobCommand.CommandType = CommandType.StoredProcedure > > jobParameter = New SqlParameter("@job_name", > > SqlDbType.VarChar) > > jobParameter.Direction = ParameterDirection.Input > > jobCommand.Parameters.Add(jobParameter) > > jobParameter.Value = stJob > > jobReturnValue = New SqlParameter("@execution_status", > > SqlDbType.Int) > > jobReturnValue.Direction = ParameterDirection.ReturnValue > > jobCommand.Parameters.Add(jobReturnValue) > > jobConnection.Open() > > jobCommand.ExecuteNonQuery() > > jobResult = jobReturnValue.Value > > jobConnection.Close() > > > > According to the documentation, the execution_status parameter should show 1 > > if executing and 4 if idle. The function returns 0, which apparently is the > > return value of the procedure (success) not the execution status of the job > > 'stjob'. How can I return the value of the parameter I asked for? > > > > > > > > "EMartinez" wrote: > > > > > On Mar 12, 11:42 pm, Steve Jensen > > > <SteveJen...@discussions.microsoft.com> wrote: > > > > I am running an SSIS job from a VB.NET application by calling sp_start_job. > > > > The return value I get is only whether or not it was able to start the SSIS > > > > job. It does not tell me when the SSIS job actually completed (if it did). > > > > > > > > Is there a way to keep the job connection open and monitor the progress of > > > > the SSIS package it is running, and return a value based on the SSIS job's > > > > completion? In other words, it is nice to know the sp_start_job started OK, > > > > but I need to know how the SSIS job ran (and wait for it to finish) before > > > > moving on to other things in my program that depend upon the job completion. > > > > > > > > > You might want to execute sp_help_job after executing sp_start_job. > > > > > > Regards, > > > > > > Enrique Martinez > > > Sr. SQL Server Developer > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |