Post reply

Delete .dat file from SSMS by SQL statements

  • July 26, 2010 at 8:09 am

    #224621

    hi All,

    I am going to check the .dat file in local Folders.

    I want to delete if it exists.......

    DECLARE @Path varchar(128) ,

    @FileName varchar(128)

    SET @Path = 'C:\'

    SET @FileName = 'FILE_NAME.dat'

    DECLARE @i int

    DECLARE @File varchar(1000)

    SET @File = @Path + @FileName

    EXEC master..xp_fileexist @File, @i out

    IF @i = 1

    BEGIN

    PRINT 'file exists & deleting'

    Delete @File

    END

    PRINT 'Creating File'

    BCP to .cmd file

    How to do it........

    Can i do it by Xp_cmdShell 'Del @File'

    Thanks,

    Sasidhar Pulivarthi

  • July 26, 2010 at 9:06 am

    #1198251

    Yes you can. It can be something like

    exec xp_cmdShell 'del @File /Q'

    /Q= Quiet mode, do not ask if ok to delete on global wildcard

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • July 26, 2010 at 9:11 am

    #1198254

    I think you'd do

    exec xp_cmdshell 'del ' + @file + ' /Q'

    xp_cmdshell is seen a security risk. I'd set a proxy, and limit the rights of this account to just managing the files that need to be managed. Or I'd do this in a controlled, SSIS package.

  • July 26, 2010 at 11:43 pm

    #1198540

    Thanks.....

    where the file going to store or

    What is the Default location of .dat file...

    if i run the following command

    EXEC XP_CMDShell 'bcp DB_Project_Test..pt_tEmployee out tEmployee.dat -S Test -n -T '

    Thanks,

    Sasidhar Pulivarthi

  • July 27, 2010 at 8:51 am

    #1198748

    You see see, or change it, here: http://www.mssqltips.com/tip.asp?tip=1583

Viewing 5 posts - 1 through 4 (of 4 total)

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