xp_delete_file not working - by Todd C

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 255121 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 2/1/2007 6:17:02 AM
Access Restriction Public


It appears that the xp_delete_file still does not work.

EXECUTE master.dbo.xp_delete_file 1,N'\\<server name>\<share name>\<folder name>,N'txt',N'01/28/2007 08:51:22'

above command did not delete any files
Sign in to post a comment.
Posted by ElmerMiller on 7/12/2010 at 7:01 AM
I got mine working by deleteing and re-adding the Maintenance Cleanup Task in the Mainenance Plan.
Posted by ElmerMiller on 7/12/2010 at 6:55 AM
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!!!
Posted by Microsoft on 11/1/2007 at 5:19 AM
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.

- Balaji Rathakrishnan
Microsoft SQL Server
Posted by Todd C on 4/23/2007 at 11:19 AM
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.

Can't there be just one simple T-SQL Extended stored procedure that will do this for me?
Posted by Microsoft on 2/15/2007 at 9:48 AM
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
Posted by Todd C on 2/9/2007 at 4:44 AM
Terence: I am using SP 1 across the board for all SQL 2005 instances. My SSMS shows the following version: 9.00.2047.00

Posted by Microsoft on 2/8/2007 at 2:42 PM
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?


Posted by Todd C on 2/2/2007 at 4:59 AM
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.
Posted by Microsoft on 2/1/2007 at 3:19 PM
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