x

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
more ▼

asked Dec 03 '09 at 08:15 AM in Default

ahmed gravatar image

ahmed
11 2 2 2

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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

more ▼

answered Dec 22 '09 at 07:05 PM

bweigel gravatar image

bweigel
91 1 1 3

(comments are locked)
10|1200 characters needed characters left

The file name part should be

set @file_name='file_'+replace(convert(varchar(5),getdate(),104),'.','')            

Use this in the script

more ▼

answered Dec 03 '09 at 11:33 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 08 '09 at 06:33 AM

ahmed gravatar image

ahmed
11 2 2 2

(comments are locked)
10|1200 characters needed characters left

Try this

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

answered Dec 08 '09 at 06:42 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 08 '09 at 09:45 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x86
x16

asked: Dec 03 '09 at 08:15 AM

Seen: 5944 times

Last Updated: May 19 '11 at 01:33 AM