Post reply

xp_delete_file Invalid Parameter

  • April 23, 2008 at 4:31 am

    #186828

    Hi,

    I have been using the following code which has been working fine.

    declare @dt datetime

    select @dt=getdate()-3 --No days to delete

    EXECUTE master.dbo.xp_delete_file 0,N'E:\trans\',N'*bak*',@dt

    However I now have a few more databases (68), which all dump to their own folder. After doing some reading I found that if you modify the code it will check all of the subdiretories. I have done this and now get this error

    New code

    declare @dt datetime

    select @dt=getdate()-1 --No days to delete

    EXECUTE master.dbo.xp_delete_file 0,N'E:\trans\',N'*bak*',@dt,1

    1 -- Including first-level subfolders

    Error

    Msg 22049, Level 15, State 0, Line 0

    Error executing extended stored procedure: Invalid Parameter

    Thanks in advance

  • June 19, 2008 at 6:50 pm

    #831463

    Apparently that is supported in SP2 of mssql 2005..

  • November 11, 2008 at 6:43 am

    #896909

    paul.sanders (6/19/2008)

    Apparently that is supported in SP2 of mssql 2005..

    It is supported? Because I have the following version of SQL:

    Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    And when I try to execute the following code:

    declare @dt datetime

    select @dt=getdate()-28

    EXECUTE master.dbo.xp_delete_file 0,N'\\Devserver2\d$\Prod_DBs_ToArchive',N'BAK',@Dt, 1

    I still get the following error 'Msg 22049, Level 15, State 0, Line 0

    Error executing extended stored procedure: Invalid Parameter'

    What I find odd is that I am able to execute the command in the production environment, but not in the development environment (they are both the same version of SQL)

    Has anyone else experienced this and if so do you know how to resolve the error?

    Many Thanks,

    Dan

  • November 11, 2008 at 7:00 am

    #896924

    Build 9.00.1399.x of SQL Server 2005 is the RTM build (no service pack). You need to upgrade to build number 9.00.3042 (I recommend at least 9.00.3050) which will put you at SP2+.

    To determine your build number and service pack you can run the following query:

    SELECT SERVERPROPERTY('productlevel'), SERVERPROPERTY('productversion')

  • November 11, 2008 at 7:02 am

    #896925

    Hi Paul,

    When I got rid of the 1 at the end of the statement it worked for me.

    Try

    declare @dt datetime

    select @dt=getdate()-28

    EXECUTE master.dbo.xp_delete_file 0,N'\\Devserver2\d$\Prod_DBs_ToArchive',N'BAK',@Dt

    Thanks,

    Anjel

  • November 11, 2008 at 7:16 am

    #896936

    The 1 stands for the option to delete files in subfolders and was introduced in SP1. Since you're on RTM it doesn't work using subfolders but as long as your files are in the folder itself it will work. Anyhow as said before you should upgrade at least to SP2.

    [font="Verdana"]Markus Bohse[/font]

  • November 11, 2008 at 9:33 am

    #897022

    Many thanks Paul & Markus.

    I have now noticed that like you say the SP2 on the devserver2 is RTM and was mislead by the fact it said (Service Pack 2) at the end of the @@Version command.

    Will update and see how I get on!

    Thanks again guys

  • March 23, 2009 at 1:20 am

    #964261

    Does anyone know where xp_delete_file is documented?

  • March 23, 2009 at 4:35 am

    #964307

    Graham.Okely (3/23/2009)

    Does anyone know where xp_delete_file is documented?

    There is no official doumentation from MS.

    See here http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx for some unofficial info.

    [font="Verdana"]Markus Bohse[/font]

  • May 28, 2010 at 8:39 am

    #1173960

    MarkusB (3/23/2009)

    Graham.Okely (3/23/2009)

    Does anyone know where xp_delete_file is documented?

    There is no official doumentation from MS.

    See here http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx for some unofficial info.

    The official line from MS seems to be that it's undocumented by design. Nice.

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124708&wa=wsignin1.0

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • October 22, 2010 at 12:23 pm

    #1241117

    is there a way to get xp_delete_file to go beyond the first level sub-directory? I just want to go to second level.

    thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply