May 20, 2009 at 11:22 pm
Hi all,
I have sql job which ouputs the result of the job step to a text file.
But i also need to create another file for the same step with a simple "success" or a "failure" based on the step completion status.
It is possible to this?
Thanks
May 21, 2009 at 5:23 am
I don't think its possible using the Job setup wizard, however in your command area you could tweak the code, and use xp_cmdshell to output the file - you'll need to enable advanced options first then run sp_configure/RECONFIGURE if xp_cmdshell isn't enabled yet.
Something like this (very simple example)
DECLARE @SIZE INT
SELECT @SIZE = size from sys.sysdatabase_files
WHERE [Name] = 'DBNam_LOG';
IF (@SIZE > 10000)
BEGIN
EXEC xp_cmdShell 'echo failure > C:\Temp\Job.Log';
-- you need this to make the overall job fail
RAISERROR('Log File too large', 25, -1) WITH LOG;
END
ELSE
EXEC xp_cmdShell 'echo success > C:\Temp\Job.Log';
May 21, 2009 at 1:01 pm
Thank you for reply,
How about also to query sysjobhistory in MSDB to get output for the last time when the job run and insert into a file?
Something like:
select * from dbo.sysjobhistory
where step_name = 'IndexOptimize'
AND step_name '(Job outcome)'
order by run_date desc
May 21, 2009 at 1:31 pm
Again a similar method could be employed. I'm guessing you're only interested in the most recent result.
DECLARE @RESULT varchar(4000);
select @RESULT = TOP(1) Message from dbo.sysjobhistory
where step_name = 'IndexOptimize'
AND step_name '(Job outcome)'
order by run_date desc
EXEC xp_cmdshell 'echo Result is ' + @RESULT + ' > C:\Temp\Job.Log';
But if you have a lot of file manipulation to do, it may be better do use CMD batch files under windows task scheduler and run the SQL using OSQL.EXE - you could capture multiple output rows to a file.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply