Unanswered: How to know when SQL Agent job completes
If I use dbo.sp_startjob to start a SQL Agent Job, how do I know when it completes. Once the job starts it just continues through the following queries. I was hoping for something like openquery where SQL Server waits for the query to be completed before continuing to the next query.
I am trying to create a stored procedure on my SQL Server machine that performs some queries on a remote Oracle Server using SSIS, then transfers the data to SQL Server. So it is important for me to somehow get SQL Server to not continue on to other queries until the job is done. Any ideas on how to approach this problem? Thanks.
Hmm ok. I can do the polling of table. I could check the table in a while loop, and when it is done just exit the loop. Is there a way to make the while loop sleep and only check every minute or something? I don't want to it to eat up too much resources by checking.
Does anyone else have an alternative solution? I am curious of other methods.