script executing in distribution database

• PritamSalvi

  Valued Member

  Points: 72

  More actions

  February 6, 2013 at 2:41 am

  #288711

  Hi,

  We havent set the replication still how distribution database get created.

  I have found below script is executing in distrubution database.

  (@param varchar(23),@param1 varchar(23))

  select name,convert(char(38),h.job_id),convert(float,run_date),convert(float,run_time),

  convert(float,run_status),convert(float,run_duration) from msdb..sysjobhistory h,

  msdb..sysjobs j where h.job_id=j.job_id and run_status in (0,1,3) and step_id=0

  and run_duration > 235959 and (convert(datetime,dateadd(hour,substring(REPLICATE('0',10-len(ltrim(rtrim(str(run_duration)))))+

  ltrim(rtrim(str(run_duration))),1,6)%24, dateadd(day,substring(REPLICATE('0',10-len(ltrim(rtrim(str(run_duration)))))+

  ltrim(rtrim(str(run_duration))),1,6)/24, (convert(datetime,str(run_date)+'

  '+substring(REPLICATE('0',6-len(rtrim(run_time))) +rtrim(run_time),1,2)+':

  '+substring(REPLICATE('0',6-len(rtrim(run_time)))+rtrim(run_time),3,2) +':

  '+substring(REPLICATE('0',6-len(rtrim(run_time)))+rtrim(run_time),5,2),112)))

  +CAST('00:'+substring(REPLICATE('0',10-len(ltrim(rtrim(str(run_duration)))))+

  ltrim(rtrim(str(run_duration))),7,2)+':'+ substring(REPLICATE('0',10-len(ltrim(rtrim(str(run_duration)))))+

  ltrim(rtrim(str(run_duration))),9,2) AS VARCHAR(20))),121))>= convert(datetime,@param,121)

  UNION select name,convert(char(38),h.job_id),run_date,run_time,run_status,run_duration

  from msdb..sysjobhistory h, msdb..sysjobs j where h.job_id=j.job_id and run_status in (0,1,3)

  and step_id=0 and case when run_duration > 235959 then convert(datetime,'1990-01-01 00:00:00.001',121)

  else (convert(datetime,convert(datetime,str(run_date)+' '+substring(REPLICATE('0',6-len(rtrim(run_time)))

  +rtrim(run_time),1,2)+':'+substring(REPLICATE('0',6-len(rtrim(run_time)))+rtrim(run_time),3,2)+':

  '+substring(REPLICATE('0',6-len(rtrim(run_time)))+rtrim(run_time),5,2),112)+CAST(substring(REPLICATE('0',6-len(rtrim(run_duration)))+

  rtrim(run_duration),1,2)+':'+substring(REPLICATE('0',6-len(rtrim(run_duration)))+rtrim(run_duration),3,2)+':

  '+substring(REPLICATE('0',6-len(rtrim(run_duration)))+rtrim(run_duration),5,2) AS VARCHAR(20)),121)) end >= convert(datetime,@param1,121)

  Can any body suggest me?