SQL Server Performance Forum – Threads Archive

osql issue

hello We are running osql to trap the failed jobs in all the production servers in one log file . We are on SQL 2000 SP3a. it does the good job except for the
part it produces the unnecessary output like one in the below even there is no failed jobs Here is where i need help from you guyz if there is no failed jobs on a server i do not need to print anything on the log file
e-g osql -SBOSTPNYD2 -E -ic:jobcheckerjobchecker.sql >>c:jobcheckerjobchecker.log =============================
——PRODUCTION SERVERS—–
=============================
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> ——————————————————————————
————————————————–
BOSEPNYD2
Job Name Step Name
Message Run Date
—————————— ——————————
—————————————- ———– 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> ——————————————————————————
————————————————– its reasonable to print for BOSTPNYD3 because it had failed jobs like below BOSTPNYD3

Job Name Step Name
Message Run Date
—————————— ——————————
—————————————- ———–
Import Orders from Relizon Import orders/conf file
Executed as user: GLOBALUSSQLAdmin. Mai 20051127
Import Orders from Relizon (Job outcome)
The job failed. The Job was invoked by 20051127
any help on this would be greatly appreciated
THANKS

---

If I’ve understood you correctly what you want to remove is the numbering? If so use the -n switch to remove numbering. If you also want to remove the headers (i.e. the dashed lines) use -h-1. e.g. osql -SBOSTPNYD2 -E -ic:jobcheckerjobchecker.sql >>c:jobcheckerjobchecker.log -n -h-1 Hope that helps.

Karl Grambow www.sqldbcontrol.com

---

Thanks Carl for your valuable input I am running the sql to pull the failed jobs select substring (j.name,1,30) ‘Job Name’,
substring (h.step_name,1,30) ‘Step Name’,
substring (h.message,1,40) ‘Message’,
h.run_date ‘Run Date’
from msdb.dbo.sysjobhistory h,msdb.dbo.sysjobs j
where h.job_id = j.job_id
and message not like ‘%succeeded%’
and (run_date >= @yesterday and run_date <= @today) how can i modify this sql so that it can return the output only if there is a failed job, here i wanted to see the column headers etc. If no failed jobs i do NOT want to see any output including column header or on the log file. above is the sql i am firing when i call the jobchecker batch script. thanks

---

What you could do us check to see whether there are any records by using an exists clause, and if there are then run the select statement.<br /><br />Incidentally, you’d be better off looking at the sysjobservers table to find out if the job failed.<br /><br />Also, you might want to watch out for the clauses you’re using for datetime searches, because you’ve got to allow for some jobs running several times a day.<br /><br />I wrote a massive job monitoring system that started off much like yours seems to be starting off and it grew and grew and grew. Lots of fun.<br /><br />By the way, don’t believe what BOL tells you about the last_run_time column in sysjobservers. It’s not seconds. in this column, 120001 = 12:00:01 <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />if exists(select from sysjobservers where last_run_outcome &lt;&gt; 0 and msdb.dbo.intToDateTime (last_run_date, last_run_time) &gt; @yesterday)<br /><br />begin<br /><br />–run your statement here — or a modified version that checks sysjobservers.<br /><br />end<br /></font id="code"></pre id="code"><br /><br />The function IntToDateTime is one I created because as you’ll noticed, when you query system tables the date and time are stored seperately as integers.<br /><br />Here’s the function definition:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE FUNCTION IntToDateTime<br /> (@IntDate int, @IntTime int) <br /><br />RETURNS datetime<br /><br /> AS <br /><br />–Converts two integers, a date and time, to a datetime format. This naturally assumes<br />–that the time integer lies within the valid time range.<br />–The range for IntTime is from 100 to 235959<br />–The range for IntDate is from 19000101 to 99991231<br /><br /><br />–Usage: SELECT master.dbo.IntToDateTime(20011213,233100)<br /><br />–12 December 2001. [email protected]<br /><br />BEGIN <br /><br />DECLARE @Time varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DECLARE @Date varchar(15)<br />DECLARE @Output datetime<br /><br />IF @IntTime &lt; 0 OR @IntTime &gt; 235959 RETURN(null)<br /><br />IF @IntDate &lt; 19000101 OR @IntDate &gt; 99991231 RETURN(null)<br /><br /><br />SET @Date = CONVERT(varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@IntDate)<br /><br /> SELECT @Time=<br /> CASE <br />WHEN @IntTime=0 Then ‘000000’<br />WHEN @IntTime &lt; 100 THEN ‘00000’ + SUBSTRING(CONVERT(varchar(6),@IntTime),1,1)<br />WHEN @IntTime &lt; 100 THEN ‘0000’ + SUBSTRING(CONVERT(varchar(6),@IntTime),1,2)<br />WHEN @IntTime &lt; 1000 THEN ‘000’ + SUBSTRING(CONVERT(varchar(6),@IntTime),1,3)<br />WHEN @IntTime &lt; 10000 THEN ’00’ + SUBSTRING(CONVERT(varchar(6),@IntTime),1,4)<br />WHEN @IntTime &lt; 100000 THEN ‘0’ + SUBSTRING(CONVERT(varchar(6),@IntTime),1,5)<br />ELSE SUBSTRING(CONVERT(varchar(6),@IntTime),1,6) <br /> END<br /><br />SET @Time = SUBSTRING(@Time,1,2) + ‘:’ + SUBSTRING(@Time,3,2) + ‘:’ + SUBSTRING(@Time,5,2)<br /><br />SET @Date = SUBSTRING(@Date,1,4) + ‘-‘ + SUBSTRING(@Date,5,2) + ‘-‘ + SUBSTRING(@Date,7,2)<br /><br />SET @Output = CONVERT(datetime,@Date + ‘ ‘ + @Time)<br /><br />RETURN @Output<br />END<br /><br /></font id="code"></pre id="code"><br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by raagi2000</i><br /><br />Thanks Carl for your valuable input I am running the sql to pull the failed jobs<br /><br />select substring (j.name,1,30) ‘Job Name’,<br />substring (h.step_name,1,30) ‘Step Name’, <br />substring (h.message,1,40) ‘Message’,<br /> h.run_date ‘Run Date’ <br />from msdb.dbo.sysjobhistory h,msdb.dbo.sysjobs j<br />where h.job_id = j.job_id <br />and message not like ‘%succeeded%'<br />and (run_date &gt;= @yesterday and run_date &lt;= @today)<br /><br />how can i modify this sql so that it can return the output only if there is a failed job, here i wanted to see the column headers etc. If no failed jobs i do NOT want to see any output including column header or on the log file. above is the sql i am firing when i call the jobchecker batch script.<br /><br />thanks<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com

---

]]>