XP_cmdshell DIR

Question

XP_cmdshell DIR

Viewing 15 posts - 16 through 30 (of 54 total)

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

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 1

Yes, I'm aware it is a 2005 forum. I posted here because it was in reply to one of Jeff's posts as a result of a search I did.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

chrs-513176 (8/23/2010)
The file size is greater than 2GB. Is that the max this method can return? Unfortunately, I'm still on 2000 so CLR is not an option for me.

The code I posted has @Size INT. Try changing it to @Size BIGINT. I don't know if sp_OA* will handle it, but the answer is always "no" unless you try so give it a try.

--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)

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 3

Yeah, I did try that but to no avail. I'll work on it a little more and let you know if I find a solution. Thanks all for the help.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

chrs-513176 (8/24/2010)
Yeah, I did try that but to no avail. I'll work on it a little more and let you know if I find a solution.

Heh... no need... there's more than one way to solve this problem. Some folks don't like this method because MS could change the format of the DIR command... yep... they could... 😛

This is nasty fast...

CREATE PROCEDURE dbo.GetDirectoryFileInfo

/****************************************************************************************

PURPOSE:

This utility stored procedure returns the long filename, CreateDate, LastModifiedDate,

and the file size in Bytes from any given directory or UNC.

INPUT PARAMETERS:

The unquoted \\MachineName\Path or d:\Path where "d:" is the drive letter. Wildcards

may be used for file names and extensions. Only path information is allowed. Inclusion

of anything not interpreted as a valid path will cause an empty result set to be

returned for security reasons.

OUTPUTS:

Column name DataType Description

----------- -------- ----------------------------------------------------------

RowNum INTEGER Sequential number

FileName VARCHAR(256) Filename and extension from the DIR command

CreateDate DATETIME Date the file was created on

LastModifiedDate DATETIME Date the file was last modified

Bytes BIGINT The number of bytes the file contains

If the path is not found, is empty, the parameter passed was not an actual path, or

the permissions to access a legitimate path does not exist for MS-SQL Server, the stored

procedure will return an empty result set. This is partially for security reasons...

if a hacker gets no return, they don't know if they're on the right track or not.

REVISION HISTORY:

Rev 00 - 09 Apr 2005 - Jeff Moden - Initial creation and unit test

****************************************************************************************/

--===== Declare I/O parameters

@pPath VARCHAR(512) --The path info and wildcards to be used with a DIR command

AS

--=======================================================================================

--===== Presets

--=======================================================================================

--===== Supress the autodisplay of rowcounts for appearance and speed

SET NOCOUNT ON

--===== Declare local variables

DECLARE @Command VARCHAR (300) --Holds the dynamic DOS command for the DIR command

--===== If the temp table that holds the Directory output is not null, drop the table

IF OBJECT_ID('TempDB..#DosOutput') IS NOT NULL

DROP TABLE #DosOutput

--===== Create the temp table that holds the Directory output

CREATE TABLE #DosOutput

(

RowNum INT IDENTITY(1,1),

Data VARCHAR(300)

)

--===== If the temp table that holds the file information is not null, drop the table

IF OBJECT_ID('TempDB..#FileInfo') IS NOT NULL

DROP TABLE #FileInfo

--=======================================================================================

--===== Get the directory information and the LastModifiedDate for lines with files only.

--=======================================================================================

--===== Setup to do a "DIR" with the following switches

-- /TW = Date/Time file was last written to (LastModifiedDate)

-- /-C = List number of bytes without commas

-- Enclose the @pPath variable in quotes to all for paths with spaces.

SET @Command = 'DIR "' + @pPath + '" /TW /-C'

--===== Execute the "DIR" command and save the output in #DosOutput

-- (order preserved by the Primary Key)

INSERT INTO #DosOutput (Data)

EXEC Master.dbo.xp_CmdShell @Command

--===== Parse the Dos output into the file info table.

-- The criteria in the WHERE clause ensures only file info is returned

SELECT

IDENTITY(INT,1,1) AS RowNum,

SUBSTRING(Data,40,256) AS [FileName],

CAST(NULL AS DATETIME) AS CreateDate, --Populated on next step

CONVERT(DATETIME,SUBSTRING(Data,1,23)) AS LastModifiedDate,

CAST(SUBSTRING(Data,22,17) AS BIGINT) AS Bytes

INTO #FileInfo

FROM #DosOutput

WHERE SUBSTRING(Data,15,1) = ':' --Row has a date/time on it

AND Data NOT LIKE '%<DIR>%' --Row is not a directory listing

--=======================================================================================

--===== Update each file's info with the CreateDate

--=======================================================================================

--===== Setup to do a "DIR" with the following switches

-- /TC = Date/Time File was created (CreateDate)

-- Enclose the @pPath variable in quotes to prevent SQL Injection attacks

SET @Command = 'DIR "' + @pPath + '" /TC'

--===== Clear the #DosOutput table

TRUNCATE TABLE #DosOutput

--===== Execute the "DIR" command and save the output in #DosOutput

-- (order preservation not important here)

INSERT INTO #DosOutput (Data)

EXEC Master.dbo.xp_CmdShell @Command

--===== Parse the DOS output table for the CreateDate and add it to the

-- file info table.

UPDATE #FileInfo

SET CreateDate = CONVERT(DATETIME,SUBSTRING(do.Data,1,23))

FROM #FileInfo fi,

#DosOutput do

WHERE fi.FileName = SUBSTRING(do.Data,40,256) --Filenames match

AND SUBSTRING(do.Data,15,1) = ':' --Row has a date/time on it

AND do.Data NOT LIKE '%<DIR>%' --Row is not a directory listing

--=======================================================================================

--===== Return a result set to the calling object

--=======================================================================================

SELECT * FROM #FileInfo

--===== Exit the proc with no error reporting for security reasons

RETURN

GO

--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)

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 5

This gives me what I need. Thanks for the help Jeff!

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 6

SSIS?

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

chrs-513176 (8/26/2010)
This gives me what I need. Thanks for the help Jeff!

You bet... thanks for the feedback.

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Ness (8/26/2010)
SSIS?

To the best of my knowledge, SSIS doesn't have anything to get file sizes. You'd need to write a script of do a bit of an add in with one of the .MSI's you might find on the "Code Project".

That's part of why I don't care for SSIS especially as an ETL tool. Seems like there's almost always some non-SQL script involved.

--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)

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 9

Sorry Jeff - I was anticipating using a script task from within SSIS to get the info that you were after. It should be a relatively easy script that you can find readily via google. SSIS appears to be a headache but once you get the hang of it, it's a really usefull tool. Sorry I can not help more - I am out of the office for a while.

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Ness (8/27/2010)
Sorry Jeff - I was anticipating using a script task from within SSIS [font="Arial Black"]to get the info that you were after[/font]. It should be a relatively easy script that you can find readily via google. SSIS appears to be a headache but once you get the hang of it, it's a really usefull tool. Sorry I can not help more - I am out of the office for a while.

No problem but I'm not actually after any information. I was just showing folks a couple of different ways to do it from T-SQL without using a script, period.

As a side bar, you say that SSIS is a really usefull tool but I've managed to build some great ETL systems without it. I'm not being argumentative and I'd really like to know... what makes it so great for you?

--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)

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 11

No worries, not an issue.

I find SSIS a useful tool for various different reasons incl. graphical interface for the devt env, ease of deployment with use of the manifest file to both file structures and at a DB level, the use of package configurations to amend variables/connection strings at runtime, the various levels of security that can be applied to the package before and after deployment using either SQL roles for after and the encryption level prior, usage of breakpoints, etc....

I am by no means an old hand at SSIS and it can be more than frustrating at times but it has served me well so far.

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

ken_wenze SSC Enthusiast Points: 141 More actions · Answer 12

Jeff - This looks pretty good but how would i modify this to loop through every sub directory in a folder such as C:\MyFolder

OptimusPrime Ten Centuries Points: 1379 More actions · Answer 13

Would you be interested in the other approach I implemented? I did Jeff's codes, but at another site I decided to do it differently using a batch file that creates the list of files in several folders deep, then bulk insert the list into a table.

ken_wenze SSC Enthusiast Points: 141 More actions · Answer 14

Yes -

I do not see any other posts from you - if you have any information - please share -

Thanks,

OptimusPrime Ten Centuries Points: 1379 More actions · Answer 15

I originated this thread in 2008. Below is the alternate approach:

1) create a batch file with the following code to list files in folders and subfolders:

@echo off

cd c:\MyFolder

FOR /R %%i IN (*.*) DO @echo %%~tfzi > c:\myfolder\mylist.txt

2) bulk insert into a temp table where you can manipulate the contents and loop.

You can call the batch file using XP_CMDSHELL (assuming your environment allows it),

then bulk insert after it. Alternatively, you can also call the batch file from SQL Agent. maybe as the first step.

I know it looks so simple, but that's how it is really. If you can create the listing and you can insert into a table, T-SQL will be sufficient to do the rest.:-D

Enjoy!