Best way to schedule a SQL Job : Please help very urgent

• Sunday, December 09, 2012 5:37 AM

   

   
  

  0

  Hi,

  I need help in scheduling a SQLJob related to my project. The SQL Job I have written will update a table on a specific period of time . Below is the schedule in which the SQL job should run. 

  Note: This is basically for a SSRS report which users would be running at 12 PM night and 7 AM morning.

  SQL Job will run at the specific period of time intervals mentioned below.( 3 hrs gap we have set for this SQL job)

  11 PM -Start ( This can not be changed since users are very strict about the starting period )

  1 AM -2nd time 

  5 AM -3rd time 

  8AM- 4th time 

  11 AM -4th time 

  2PM - 5th time 

  5 PM - 6th time 
  

  8 PM -7th time 

  Now most imp. thing is a set of users would be running the report at sharp 7 AM early morning in which they specifically want the data from 5 AM - 7 AM everyday. For this I am thinking of creating another SQL job which will run at this specific point of time only.

  I am worried about

  1:  How can I make sure that data will not be duplicated when both jobs are running parallel. 

  2: How can I manage these two jobs with out any hard coding values in both jobs.

  Please suggest me some valuable inputs so that I can finish this task ASAP and move on production.

  Thanks a lot in advance.

  Structure of the table is mentioned below. I will be querying the data based upon the arrived date. (ie: startdate>arrived date and enddate< arrived date)

  CREATE TABLE [ERP].[Transaction](
  [RowID] [bigint] IDENTITY(1,1) NOT NULL,
  [TapTicketNo] [varchar](100) NULL,
  [CrucibleNo] [int] NULL,
  [Destination] [varchar](100) NULL,
  [Shift] [nvarchar](20) NULL,
  [CastId] [varchar](100) NULL,
  [FullWeight] [float] NULL,
  [PreSkimWeight] [float] NULL,
  [SkimmedWeight] [float] NULL,
  [NetWeight] [float] NULL,
  [EstimatedWeight] [float] NULL,
  [Recipe] [varchar](100) NULL,
  [Date] [datetime] NULL,
  [FullWeightDate]  [datetime] NULL,
  [EmptyWeightDate]  [datetime] NULL,
  [TotalWeight] [float] NULL,
  [MixingSpeed] [int] NULL,
  [BayNo] [int] NULL,
  [EstimatedNa] [float] NULL,
  [ActualNa] [float] NULL,
  [AIF3Qty] [float] NULL,
  [FirstSkimTemperature] [float] NULL,
  [SecondSkimTemperature] [float] NULL,
  [IsPartial] [nvarchar](100) NULL,
  [FurnaceID] [int] NULL,
  [ArrivedDate] [datetime] NOT NULL,
  [TotalWeightOfSkimOnlyMetal] [float] NULL,      
      [TotalWeightOfNonSkimOnlyMetal] [float] NULL,      
      [TotalWeightOfNullRecipeMetal] [float] NULL,      
      [NoOfSkimOnlyMetal] [int] NULL,      
      [NoOfNonSkimOnlyMetal] [int] NULL,      
      [NoOfNullRecipeMetal] [int] NULL,
  
   CONSTRAINT [PK_Hotmetal.GetSSRSDetailsReport] PRIMARY KEY CLUSTERED 
  (
  [RowID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO

  -pep

  
  

  • Edited by pepcoder Sunday, December 09, 2012 6:17 AM
  • Moved by Sethu SrinivasanMicrosoft Employee, Moderator Thursday, December 13, 2012 9:23 PM agent jobs (From:SQL Server Setup & Upgrade)
  •  
  • Reply
  • Quote