xp_delete_file not working by Todd C

I got mine working by deleteing and re-adding the Maintenance Cleanup Task in the Mainenance Plan.

I also have this problem with the Maintenance Cleanup Task. Mine IS on SQL 2005 SP3 (9.00.4053.00) and I AM trying to delete actual database backup files (not txt files). Is there a hotfix for this BUG? I have other servers where this does work and I can't find anything different between the working and non-working servers? STRANGE!!!

Hi Todd,
Using CLR procedures, you can accomplish file operations through SQL Server. Please consider using this as per your needs. We believe it is best for SQL Server to provide the necessary infrastructure for any non-database "external access" operations and let users write their own utilities using that infrastructure.

Thanks,
- Balaji Rathakrishnan
Microsoft SQL Server

OK. I now have SP 2 running on my machine. I use BIDS to create a SSIS package with one simple task in it: The "Maintenance Cleanup Task". I want it to delete all the .txt files left over from daily execution of other SSIS packages.

Upon sucessful completion, the files still exist.
Please note: THESE ARE NOT DATABASE BACKUP FILES. THEY ARE LOG FILES FROM SSIS PACKAGE EXECUTIONS, GENERATED BY SSIS.

Can't there be just one simple T-SQL Extended stored procedure that will do this for me?

Hi Todd C,

Thanks for the info. I think your basic issue is fixed in SP2. The SSIS Maitenance cleanup task should be fixed.

The stored procedure was never intended to be called by an end user and the chances that you could ever be successful in having it do what you intend are almost zero. I took a quick peek at the implementation we are using and it takes some very specialzed arguments for a very specific task.

If you detemine that you do need to access the file system directly from a stored proc, then I would imagine you would need to write that yourself in .net. Or perhaps there is already a vendor that provides this.

We don't document this XP or promote its usage by anyone for very good reasons. Most of all, they can 'go away' from release to release.

If you determine that SP2 does not fix your issue, please reopen this bug.

Thank you for posting this issue for the benefit of all the community members.

Terrence Nevins
SQL Server Program Manager

Terence: I am using SP 1 across the board for all SQL 2005 instances. My SSMS shows the following version: 9.00.2047.00

Todd

Hi Todd C,

I looked into this very carefully, including going to the source behind xp_delete_file. Thanks to your detailed explanation I had some context to use in my examination.

Can you tell me which service pack of SQL Server Management Studio you are using?

Thanks,

Terrence

Terrence:
This started from my trying to use SSIS Maintenance Cleanup Task to delete some SSIS log (.txt) files on a server that are over 30 days old. That task did not work, so I looked at the SQL it generates and found the script above. It had C:\ instead of the full share path, but that should not matter. If I execute the script from SSMS, it STILL does not work!

My frustration is this: I have seen comments from Microsoft that the xp_delete_file stored procedure is "undocumented", yet it is used EXTENSIVELY by the SSIS Maintenace Cleanup task. Either make it work as expected and document it fully so the general user population can know how to apply it, or find some other way to accomplish this simple operation in T-SQL, and document that.

Hi Todd C,

I'm not sure that I fully understand the repro. Are you expecting to delete files on another server?

Terrence Nevins
SQL Server Program Manager