Problem with xp_delete_file

Last Post 06 Aug 2007 07:51 AM by gilou28. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
gilou28
New Member
New Member

--
06 Aug 2007 07:32 AM
Version: 9.0.3159

The following statement runs fine, but does do the job :-( (old .txt files are NOT deleted)

------------------------------------------------------------------------
DECLARE @expiration_log datetime
DECLARE @today datetime

SET @today = getdate()
SET @expiration_log = dateadd(day,-8,@today)

EXECUTE master.dbo.xp_delete_file 0,'D:\SQLData\Instances\MAINTENANCE PLAN LOG','txt',@expiration_log,0
------------------------------------------------------------------------

Any clues ?

gilou28
New Member
New Member

--
06 Aug 2007 07:51 AM
Thanks for the link, but I did read that discussion before I posted here.

What is weird is that it works fine with .bak files, but not with .txt ???????

I also tried a simplier directory name like : 'D:\SQLData\Instances\MAINTENANCE', upper case and lower case the "txt", with or whitout the actual "dot".... no luck

This is run with an account that is local admin (just to be sure), still not working...

humm......
gilou28
New Member
New Member

--
06 Aug 2007 09:07 AM
Found the problem, this syntax works:

EXECUTE master.dbo.xp_delete_file 1,'D:\SQLData\Instances\MAINTENANCE','TXT',@expiration_log,1


See the first parameter ? I don't know what it does, but changing the 0 for a 1 made the thing work! The only thing is that I do not seem to be able to delete files that are earlier than 4 days.... king of weird, but it might have something to do with only weekdays counting ... lol That's a stretch, but it does explain my results.

Hourra ! for undocumented features that are officially used!

wvb
New Member
New Member

--
15 May 2008 04:35 PM
Hi gilou28,

the first parameter in
EXECUTE master.dbo.xp_delete_file 1,'D:\SQLData\Instances\MAINTENANCE','TXT',@expiration_log,1

is FileTypeSelected its value can be (0) FileBackup and (1) FileReport

Cheers
SQLUSA
New Member
New Member

--
15 May 2008 11:11 PM
quote:

Originally posted by: gilou28

Hourra ! for undocumented features that are officially used!




xp_delete is used in database Maintainance Plans internally. Occasionally it shows up in error messages.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
You are not authorized to post a reply.

Acceptable Use Policy