Post reply

XP_cmdshell DIR

  • April 3, 2015 at 11:11 am

    #1788622

    Eirikur Eiriksson (4/3/2015)

    I know I'm somewhat late to the show, haven't read through the whole trail and therefore might be missing something, but my approach would be the xp_cmdshell with "dir /S /N /C" and then filter and parse the results, in my experience it's much more efficient than creating an OLE Object.

    😎

    I absolutely agree but the stigma that some DBAs have against using xp_CmdShell is frightening while there is less of a stigma about using OLE objects. Where one may not be allowed, the other might.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 3, 2015 at 11:30 am

    #1788630

    Jeff Moden (4/3/2015)

    Eirikur Eiriksson (4/3/2015)

    I know I'm somewhat late to the show, haven't read through the whole trail and therefore might be missing something, but my approach would be the xp_cmdshell with "dir /S /N /C" and then filter and parse the results, in my experience it's much more efficient than creating an OLE Object.

    😎

    I absolutely agree but the stigma that some DBAs have against using xp_CmdShell is frightening while there is less of a stigma about using OLE objects. Where one may not be allowed, the other might.

    All this because of the misconfiguration of the defaults in the past. I find managing the xp_CmdShell permissions a lot easier than the OLE opbject creation, far too easy to spoof and seed malicious objects when all you have to go with are GUID references/object names. Another aspect is the memory allocation to the OLE thingies, far from optimal (correct me if I'm wrong) as still being a percentage of the available memory:pinch:

    😎

  • April 3, 2015 at 9:58 pm

    #1788699

    IT's much simpler than all that. Both require "SA" privs to be used. Both can only be turned on or of by someone with "SA" privs. Control who has "SA" privs.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 23, 2015 at 9:50 am

    #1793043

    Awesome Post. Thanks Jeff Sir...

    Thanks.

  • April 23, 2015 at 9:55 am

    #1793044

    Can you please suggest all the files from one location to another using the temp table (#FileDetails)?

    I have a rmeote location where I want to copy all the files from local server to remote share.

    I have xp_cmdshell offers copy/move features but wanted to know if you have drafted already any other good script, can make use of it.

    Fianlly great script..

    Thanks.

  • April 23, 2015 at 4:12 pm

    #1793142

    SQL-DBA-01 (4/23/2015)

    Can you please suggest all the files from one location to another using the temp table (#FileDetails)?

    I have a rmeote location where I want to copy all the files from local server to remote share.

    I have xp_cmdshell offers copy/move features but wanted to know if you have drafted already any other good script, can make use of it.

    Fianlly great script..

    Thank you for the feedback. I'll try to provide more detail tonight but I'm on a major project and might not get to it until the weekend.

    In the meantime, do a search for "RoboCopy", which is a part of Windows just like the "Copy" and "XCopy" (another seriously useful tool) commands are.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 5, 2017 at 11:02 am

    #1962520

    I have been struggling with this problem for a few days.  So, using a combination of different scripts I have found and adding my own twist, I came up with the following. 

    It will give directory\sub-directory information, file name, size, and date modified. 

    There is no error checking and I am new to the language so there may be a better way to parse the data, but it accomplished what I needed it to do in SQL Server 2012 on Server 2008 r2


    IF OBJECT_ID('tempdb..#tempList') IS NOT NULL DROP TABLE #tempList
    IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
    IF OBJECT_ID('tempdb..#DirectoryInfo') IS NOT NULL DROP TABLE #DirectoryInfo

    CREATE TABLE #DirectoryInfo(Directory varchar(255), FileName varchar(255), Size int, FileDate datetime)
    CREATE TABLE #tempList (Files VARCHAR(500))

    SET NOCOUNT ON

    declare @filename varchar(255),
       @path  varchar(255),
       @cmd  varchar(1000),
            @sql     nvarchar(max),
            @data     varchar(255),
            @directory varchar(500),
            @filedate varchar(20),
            @size_and_file varchar(255),
            @size varchar(20),
            @file varchar(255),
            @sqltest nvarchar(max)

    SET @path = '"c:\your path\"' --  can use unc path or mapped drives  must end with \
    SET @cmd = 'dir ' + @path + '*.* /s/-c' -- *.* can be changed to find specific file or extension  /s include sub-directories  /-c remove comma from file size

    INSERT INTO #tempList
    EXEC MASTER..XP_CMDSHELL @cmd

    --Number Rows
    SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS id Into #temp FROM #templist

    --Create and set variables for loop
    Declare @i int = 1
        ,    @lastrow int = (Select max(id) last_row from #temp)

    --Parse data 1 line at a time.
    while @lastrow >= @i
    BEGIN
        set @data = (Select Top 1 Files From #temp where @i = #temp.id)

        --Check if row has directory information
        if PATINDEX('%Directory of %',@data) > 0
            BEGIN
                    set @directory =    RIGHT(@data,LEN(@data) - PATINDEX('%of %',@data) - 2)
                    
            END
        --Check if row has directory totals or other information I don't want in final output
        ELSE IF left(@data,1) = ' ' or @data is NULL or isdate(left(@data,20)) = 0 --if data does not start with a date, I don't want it.
             BEGIN
                nothing: --Do not want this line in table
             END
             --Finally write data to temp table
             ELSE
                BEGIN
                    set @filedate = LEFT(@data,20)
                    set @size_and_file = LTRIM(RIGHT(@data,(LEN(@data)-20)))
                    set @size = LEFT(@size_and_file,PATINDEX('% %',@size_and_file))
                    set @file = RIGHT(@size_and_file,LEN(@size_and_file) - PATINDEX('% %',@size_and_file))

                    set    @sql = 'Insert Into #DirectoryInfo SELECT ''' + @directory + ''' AS Directory, ''' + @file + ''' AS FileName, ' + @size + ' AS Size, ''' + @filedate + ''' AS FileDate'
                    exec sp_executesql @sqltest
                    exec sp_executesql @sql
                END
        Set @i = @i + 1
    END

    select * from #DirectoryInfo

    drop table #tempList
    drop table #temp
    drop table #DirectoryInfo

  • October 5, 2017 at 2:17 pm

    #1962556

    Jeff Moden - Thursday, April 23, 2015 4:12 PM

    SQL-DBA-01 (4/23/2015)

    Can you please suggest all the files from one location to another using the temp table (#FileDetails)?I have a rmeote location where I want to copy all the files from local server to remote share.I have xp_cmdshell offers copy/move features but wanted to know if you have drafted already any other good script, can make use of it.Fianlly great script..

    Thank you for the feedback. I'll try to provide more detail tonight but I'm on a major project and might not get to it until the weekend.In the meantime, do a search for "RoboCopy", which is a part of Windows just like the "Copy" and "XCopy" (another seriously useful tool) commands are.

    Yowch... guess I lost track of that post. :blush:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 17, 2018 at 6:04 pm

    #1980190

    SQL-DBA-01 - Thursday, April 23, 2015 9:55 AM

    Can you please suggest all the files from one location to another using the temp table (#FileDetails)?I have a rmeote location where I want to copy all the files from local server to remote share.I have xp_cmdshell offers copy/move features but wanted to know if you have drafted already any other good script, can make use of it.Fianlly great script..

    Ah... talking about a post slipping through the cracks... my apologies.

    Using XP_CmdShell to call ROBOCOPY or even the much older XCOPY would probably do the trick for anyone needing to do such a thing.  Whomever or whatever is running the command simply needs to have privs to the share and you'd use UNCs to identify the target directory/shere.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 17, 2018 at 6:05 pm

    #1980191

    Jeff Moden - Saturday, February 17, 2018 6:04 PM

    SQL-DBA-01 - Thursday, April 23, 2015 9:55 AM

    Can you please suggest all the files from one location to another using the temp table (#FileDetails)?I have a rmeote location where I want to copy all the files from local server to remote share.I have xp_cmdshell offers copy/move features but wanted to know if you have drafted already any other good script, can make use of it.Fianlly great script..

    Ah... talking about a post slipping through the cracks... my apologies.

    Using XP_CmdShell to call ROBOCOPY or even the much older XCOPY would probably do the trick for anyone needing to do such a thing.  Whomever or whatever is running the command simply needs to have privs to the share and you'd use UNCs to identify the target directory/shere.

    Heh... of course, I should also learn to read back up the chain of posts. :blush:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 46 through 54 (of 54 total)

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