October 14, 2010 at 2:27 am
Hi 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 appreciated
Thanks
Narayana
October 14, 2010 at 3:23 am
These procedures should help.
-- Returns the status of each job
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, SUSER_SNAME
-- Returns the last execution outcome
EXECUTE msdb.dbo.sp_help_job
The first one is undocumented, but you should find some documentation with a Google search.
-- Gianluca Sartori
October 14, 2010 at 5:16 am
Thanks 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 help
Thanks
Narayana
October 14, 2010 at 6:04 am
OK, so you just have to query sysjobservers:
SELECT last_run_outcome -- 0 = Fail
-- 1 = Succeed
-- 3 = Cancel
FROM msdb.dbo.sysjobservers sjs
INNER JOIN msdb.dbo.sysjobs sj
ON sj.job_id = sjs.job_id
WHERE sj.name = 'YOUR JOB NAME'
-- Gianluca Sartori
October 14, 2010 at 6:27 am
Hi,
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 clientno
OPEN Csr_CursorEmail
FETCH NEXT FROM Csr_CursorEmail into @ClientNo, @Depot, @Service, @Sent
WHILE (@@FETCH_status =0)
BEGIN
EXEC msdb..sp_start_job @job_name = 'Job Name'
FETCH NEXT FROM Csr_CursorEmail into @ClientNo, @Depot, @Service, @Sent
END --CursorEmail
close Csr_CursorEmail
DEALLOCATE Csr_CursorEmail
___________________________________________________
When i ran the cursor for sum records job is executing successfully, but for some records the job not executing
and giving the error message as
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent 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 executed
How can i resolve this problem
Please help.
Narayana
October 14, 2010 at 6:33 am
I 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.
-- Gianluca Sartori
October 14, 2010 at 6:37 am
Not 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.
---------------------------------------------------------------------------------------
It begins by taking the first step.
October 14, 2010 at 6:51 am
I 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 appreciated
Narayana
October 14, 2010 at 7:02 am
Hi ,
Actually my task is to send report to different clients to their email address using respective parameter values
The cursor will give a result set with columns like clientno, emailadddress, depot
need to generate a report using values client1,depot1,email1 and to send mail to email1
similarly
need to generate a report using values client2,depot2,email2 and to send mail to email2
need to generate a report using values client3,depot3,email3 and to send mail to email3
Parameter values changes with clientno. and report format is same, data is different to different clients
I 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 clients
Any help is appreciated
Narayana
September 4, 2012 at 5:08 am
I 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
RunMyJob
AS
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
END
so, 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 fun
TheSpyder
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply