asked
saving jobs output to a text file
I created a job on SQL that executes every day at 4 am , the option OUTPUT FILE under the ADVANCED TAB in creating a new job process enables me to save the job to a required text file , i have two options , 1 is to append on the same file , 2 is to overwrite the same file , the problem is that both dont work for me , the problem is a need each days execution to be on a seprated text file for example when executing it on first of december the file name prodoced will be myfile0112 , and on secound december the file name will be myfile0212 and so on every day the job executes it creates a new text file on c with name of the file + date , i need it automized just as the job is , and please i need it as simple is it could , am using SQL2008 By the way Thanks
The file name part should be
set @file_name='file_'+replace(convert(varchar(5),getdate(),104),'.','')
Use this in the script
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 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
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][1] [1]:
http://mumblestiltskin.blogspot.com/2011/03/sql-server-job-step-output-file-using_08.html
