October 30, 2009 at 1:33 pm
my table script is below
USE [JobsMonitor]
GO
/****** Object: Table [dbo].[Job_Audit] Script Date: 10/30/2009 15:31:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Job_Audit](
[ServerName] [varchar](20) NULL,
[JobName] [varchar](50) NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
error message for store proc is
Msg 102, Level 15, State 1, Procedure P_Job_Audit, Line 19
Incorrect syntax near '1'.
October 30, 2009 at 1:43 pm
Include the END as the last line in your script to create the Stored Proc
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 30, 2009 at 1:58 pm
Its working, now i got one issue, I am running a job step1 after step1 is success it goes to step2 where this store proc is executed and it is executed successfully and after I select the table it will show null value on endtime column. but if i execute that store proc in a query pane again then it will show end time also. do u have any idea why it is doing that.
October 30, 2009 at 2:09 pm
now i got it, I need to figure out the end time of first job step so after executing the store proc on next step will insert the record on endtime column. but have no idea how do i do? your help would b greatly appriciated.
thanks
October 30, 2009 at 2:28 pm
I am able to understand your concern, but would like to say that, with your table not having any column for Step Information, how do you want to monitor that info for each step. I suggested a script in the morning which had the Step Names also, try to use that along with the one you currently have.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 30, 2009 at 2:37 pm
Here I have my new table script, so now we need jobstep as our parameterr? could u please send me the script for store proc with step on it.
thanks
USE [JobsMonitor]
GO
/****** Object: Table [dbo].[Job_Audit] Script Date: 10/30/2009 16:36:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Job_Audit](
[ServerName] [varchar](20) NULL,
[JobName] [varchar](50) NULL,
[JobStep] [varchar](50) NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
October 30, 2009 at 3:13 pm
Try this script, and I had no time to check for the accuracy, so take a while and check your data
SELECT DISTINCT JH.[SERVER] SERVER_NAME, SJ.[Name] AS JOBNAME, SJH.STEP_NAME,
SJA.start_execution_date, SJA.stop_execution_date
FROM MSDB.dbo.sysjobhistory JH
INNER JOIN MSDB.dbo.sysjobs SJ
ON JH.JOB_ID = SJ.JOB_ID
INNER JOIN MSDB.dbo.sysjobhistory SJH
ON SJ.JOB_ID = SJH.JOB_ID
INNER JOIN msdb.dbo.sysjobactivity SJA
ON SJA.JOB_ID = SJH.JOB_ID
WHERE SJH.RUN_STATUS = 1
AND SJH.STEP_ID <> 0
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply