Post reply

XP_CMDSHELL deletes only a few files

  • March 4, 2005 at 7:32 am

    #163496

    Here's my nightmare of the day. One of our vendors claims that the RI of the data is a network problem. But the same problem is occuring with at least 5-10 other companies. We are taking trace logs to prove the problem and writing them to disk.

    We are trying to maintain a 3 days of traces, and delete the rest. I've got the script down fairly well. Blows out weekends and holidays, deletes 3 days back if not Monday or Tuesday.

    The is the data that is in the table

    DEL D:\MSSQL7\LOG\TCL_TRACE\TCL_TRACE_SCRIPT_5.trc
DEL D:\MSSQL7\LOG\TCL_TRACE\TCL_TRACE_SCRIPT_2.trc
DEL D:\MSSQL7\LOG\TCL_TRACE\TCL_TRACE_SCRIPT_3.trc
DEL D:\MSSQL7\LOG\TCL_TRACE\TCL_TRACE_SCRIPT_4.trc

    This is the acual delete function

    DECLARE CmdLine CURSOR
FOR SELECT DEL_CMD FROM TRC_DEL_TABLE
    ORDER BY FileDateTime

OPEN CmdLine 
FETCH NEXT FROM CmdLine INTO @StrSQL
WHILE (@@fetch_status = 0) 
    BEGIN
        PRINT @STRSQL
        EXEC XP_CMDSHELL @STRSQL, no_output
        FETCH NEXT FROM CmdLine
    END
CLOSE CmdLine
DEALLOCATE CmdLine

    When I run it it will go through and delete maybe 1 file possibly 2. Any ideas?



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • March 4, 2005 at 9:13 am

    #545517

    I found my own error.

    In the second fetch, I missed the "INTO @StrSQL".

    It messed with my mind ! and there isn't much to mess with anymore.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • March 4, 2005 at 1:26 pm

    #545571

    been there done that


    * Noel

Viewing 3 posts - 1 through 2 (of 2 total)

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