CODEWITHSUNDEEP
sqlsql-server
HuntingtonCream17
HuntingtonCream17

Reputation: 33

Job Output keeps overwriting itself

I have researched this and I am aware there are similar issues on SO. However, there does not appear to be any helpful enough to solve mine.

Firstly, I am experienced with SQL however, we have recently acquired two interns. They have been with us one week now and they struggle with reading code, so I want to keep this (its part of their task team) to be as simple as possible.

Basically, the below code is part of a larger stored procedure which is ran as a job daily in the morning. It is supposed to save in a designated folder called GoodsIN as an xlsx. However, this is overwriting itself daily instead of producing a fresh XLSX file. 

@database_name=N'master', 
@output_file_name=N'E:\goodsindata\Goodsin.xlsx', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 
1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 
0900',

If anyone could help, I would really appreciate it, as mentioned we have interns in so ideally, this would be as simple to read as possible (initially, I wanted it emailed out but that was somewhat too advanced for them) I will have them reading SQL before the end of the summer!

Thanks as always Guys,

Upvotes: 0

Views: 43

Answers (1)

justiceorjustus
justiceorjustus

Reputation: 1965

Append the date (or some other unique string) or it will overwrite the file continuously. 

@database_name=N'master', 
@output_file_name=N'E:\Rebekah\PaidClaims' + convert(varchar(10),getdate(),110) + '.xlsx', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 
1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 
0900',

This will produce: E:\Rebekah\PaidClaims07-31-2017.xlsx

Here are some other date conversion codes.

Upvotes: 1

Related Questions