InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 00:37:43 GMT20http://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxI know this is a while ago but just in case anyone is looking......A technique I use is to look in the job history. The history record is only written when the job completes. Try this:CREATE PROC RunMyJobAS BEGIN SET NOCOUNT ON DECLARE @LastID BIGINT SELECT @LastID = MAX(S.instance_id) FROM msdb.dbo.sysjobhistory S join msdb.dbo.sysjobs SJ ON S.job_id = SJ.job_id WHERE SJ.name = 'MyJob' EXEC msdb.dbo.sp_start_job 'MyJob' WHILE NOT EXISTS ( SELECT * FROM msdb.dbo.sysjobhistory S join msdb.dbo.sysjobs SJ ON S.job_id = SJ.job_id WHERE SJ.name = 'MyJob' AND S.instance_id > @LastID ) BEGIN WAITFOR DELAY '00:00:01' END ……more code here ENDso, try every one second to see if the history record is there (you need to make sure there is at least one history record before the sproc runs). Once the history record is there, you code can continue confident that the job has completed (but not necessarily successfully).Have funTheSpyderTue, 04 Sep 2012 05:08:13 GMTTheSpyderhttp://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxHi ,Actually my task is to send report to different clients to their email address using respective parameter valuesThe cursor will give a result set with columns like clientno, emailadddress, depotneed to generate a report using values client1,depot1,email1 and to send mail to email1similarlyneed to generate a report using values client2,depot2,email2 and to send mail to email2need to generate a report using values client3,depot3,email3 and to send mail to email3Parameter values changes with clientno. and report format is same, data is different to different clientsI am looping the job with records because.I created a email delivery subscription to email a report in reporting services. and i observed that a subscription creates a job and with parameter fields and values in the subscriptions table.I want to send a report to different clients having different parameter values, email address. To do this for every record i am overwriting the parameter values in subscription table and then executing the job.so that i can generate the reports with different parameters.Same like data driven subscription.And we are using the STANDARD EDITION of sql server.Please help if you know any other way to do this task. LIKE using webservices or any other method to email reports to different clientsAny help is appreciatedNarayanaThu, 14 Oct 2010 07:02:25 GMTNarayana_17http://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxI am looping the job with records because.I created a subscription to email a report in reporting services. and i observed that a subscription creates a job and parameter fields in the subscriptions table.I want to send a report to different clients having different parameter values, email address. Top do this for every record i am overwriting the parameter values in subscription table and then executing the job.so that i can generate the reports with different parameters.And we are using the standard edition of sql server.Please help if you know any other way to do this task.Any help is appreciatedNarayanaThu, 14 Oct 2010 06:51:36 GMTNarayana_17http://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxNot sure why you are looping and then running the job based on the client records.Instead have a single job that would loop through the client records and run the required tasks.Thu, 14 Oct 2010 06:37:54 GMTLeju Geevarghesehttp://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxI don't think you can. sp_start_job doesn't actually start the job, but tells SQLAgent that there's a start request and then returns immediately.I think you could add a sleep (WAITFOR) in the loop, but I don't know if this suits your needs. It could slow down things significantly.Thu, 14 Oct 2010 06:33:54 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxHi, The above srcipt u gave is only for Fail,Success, Cancel.the code i used is like as follows_____________________________________________DECLARE Csr_CursorEmail CURSOR FOR SELECT top(5) ClientNo, Depot, Service, Sent FROM EmailFileSent WHERE SENT = '' order by clientnoOPEN Csr_CursorEmailFETCH NEXT FROM Csr_CursorEmail into @ClientNo, @Depot, @Service, @SentWHILE (@@FETCH_status =0)BEGIN EXEC msdb..sp_start_job @job_name = 'Job Name'FETCH NEXT FROM Csr_CursorEmail into @ClientNo, @Depot, @Service, @SentEND --CursorEmailclose Csr_CursorEmailDEALLOCATE Csr_CursorEmail___________________________________________________When i ran the cursor for sum records job is executing successfully, but for some records the job not executingand giving the error message as Msg 22022, Level 16, State 1, Line 0SQLServerAgent Error: Request to run job C5E96594-9876-42FE-9324-5F6DF8F5CC0B (from User Narayana) refused because the job already has a pending request from User Narayana.It seems Cursor is forcing to start the JOB while the previous record job is still executing.I think if i control job like = job have to wait until the previous record job is successfully executedHow can i resolve this problemPlease help.NarayanaThu, 14 Oct 2010 06:27:18 GMTNarayana_17http://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxOK, so you just have to query sysjobservers:[code]SELECT last_run_outcome -- 0 = Fail -- 1 = Succeed -- 3 = CancelFROM msdb.dbo.sysjobservers sjsINNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = sjs.job_idWHERE sj.name = 'YOUR JOB NAME'[/code]Thu, 14 Oct 2010 06:04:30 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxThanks for the reply,I am using the code EXEC msdb..sp_start_job @job_name = 'Jobname'inside a cursor for every record of the cursor dataset.I want to execute the job only after successfully completion of same Job for previous record.Like a session for each record.Please helpThanksNarayanaThu, 14 Oct 2010 05:16:15 GMTNarayana_17http://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxThese procedures should help.[code]-- Returns the status of each jobEXECUTE master.dbo.xp_sqlagent_enum_jobs 1, SUSER_SNAME-- Returns the last execution outcomeEXECUTE msdb.dbo.sp_help_job[/code]The first one is undocumented, but you should find some documentation with a Google search.Thu, 14 Oct 2010 03:23:52 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1004175-163-1.aspxHi guys,I stuck in an issue with sql server jobs. and struggling from 4 days to resolve the issue.I am generating the reports using reporting services subscriptions in SQL Server 2005 standard edition and i m blocked at one stage where i need to send reports to multiple(clients) email address and the parameter values are different to every client. I am changing the parameter settings with the client parametrs and manually running the job of the subscription. but the job the executing with same parameter values and generating same report. Can anyone tell me how to find the status of the job in sql server, whether it is started, running, executed successfully or not.Any help is greatly appreciatedThanksNarayanaThu, 14 Oct 2010 02:27:26 GMTNarayana_17