Sql-server – SQL Server Job failing to delete html files out of a folder

jobssql server

We have a simple job that runs every Sunday on sql server 2000. Copies files over, and then deletes any .txt, .rpt, .html and .log files.

It looks to me like it isn't finding any .txt and .rpt files and is reporting back no output generated, but those steps are passing.

Then for the del .html files, it reports back that it can't find any html files and fails.

The code is:

   COPY C:\DATA\LOGS\*.* C:\DATA\LOGS\Sunday\*.*    Succeeds 
   DEL  C:\DATA\LOGS\*.txt                          Succeeds
   DEL  C:\DATA\LOGS\*.rpt                          Succeeds 
   DEL  C:\DATA\LOGS\*.html                     FAILS 
   DEL  C:\DATA\LOGS\*.log                      Never gets here

Any ideas as to why it wouldn't like that del *.html ? Why wouldn't it just look for .html files, and when not finding any, just pass the step, and go onto the last step?

Best Answer

It looks like the problem is that, while there is always some file present (so the COPY always succeeds), there is not always a file with the specified extensions.

One quick-and-dirty solution - add the following lines between the COPY and the first DELETE:

ECHO z >> C:\DATA\LOGS\ZZZ_tmp.txt
ECHO z >> C:\DATA\LOGS\ZZZ_tmp.rpt
ECHO z >> C:\DATA\LOGS\ZZZ_tmp.html
ECHO z >> C:\DATA\LOGS\ZZZ_tmp.log

This will create one file with each required extension (or append a z to an existing file with the name).

If you want to go this way, make sure you:

  • Pick a base file name that you will never see in reality, so you don't corrupt an existing file; and
  • Document the purpose of the extra files.

Ideally, you'd want to create a solution where you don't fail the DELETE step if there's no matching files, instead of creating dummy files just to get deleted. This can probably be done with CMD.EXE; however, I'd suggest you implement a script in whatever language your department uses (or, at least in whatever language you're comfortable with). Unless you're comfortable with DOS (or whatever you call what CMD.EXE uses now), that's probably not your best solution.

Also - note the COPY will fail if there are no files in C:\DATA\LOGS\ to begin with. This could also be handled by a script, as could what to do if both source and destination directory have a file with the same name.

Related Question