saving jobs output to a text file

I have a SQL job one every server which runs at midnight every day. It loops through each sql job in msdb.dbo.sysjobs and executes the following to change the output file name.

	EXECUTE msdb.dbo.sp_update_jobstep            
	        @job_id           = @job_id,            
	        @step_id          = @step_id,            
	        @output_file_name = @NewOutputFileName,            
	        @flags            = 2 -- append to output file            

You will just need to write some code to come up with what you want @NewOutputFileName to be.

This is pretty much what I use to build the new filename each day

DECLARE @today_yyyymmdd char(8)            
DECLARE @LogPath varchar(255)            
SET @today_yyyymmdd = CONVERT( varchar, GETDATE(), 112 )            
SET @LogPath = 'C:\SomePathOnTheServer\'            
            
SET @NewOutputFileName = @LogPath + 'SQLJob' + RTRIM(sysjobs.name) + @today_yyyymmdd + '.txt'            

(It helps when you don't have spaces in your job name. Otherwise you may need a little additional code to remove spaces.)

Brannon

Dear madhivanan,

Thank you for caring to answer me , but am not able to understand your answer , for example where will this file be located , where to but the file name path? can u please provide one small script that includes writing thr result on a text file with anyname unders the c:\ directory for example , please give me more details .

Thank you

Ahmed

Try this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/bcp-export-data-to-text-file.aspx            

An alternative answer would be to have some external process rename the file (including the date/time in the name) after it is created.

I used the answer from bweigel to build up a solution to this problem. I have blogged it here SQL Server job step output file using job names and tokens