Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

List Files In Directory Using SQL Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 2:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 5:45 AM
Points: 31, Visits: 335
Well spotted! The SUBSTRING error is occurring because I'm willing to bet that the filepath your using has a space in it somewhere - xp_cmdshell doesn't support spaces (as far as I'm aware) so you have to use the short filename instead. This is easy enough to find by using the "dir /x" command in a DOS prompt. For example, say I have a path of:

c:\ftp\Customer1\DSV Files\

The short filename would be:

c:\ftp\Customer1\DSVFIL~1

I'll have to have a play at getting the file date into another column. If you remove the /b parameter (this is the "bare format" parameter) from the dir command you'll get it (along with a lot of other stuff you won't want!), but the CTE will error.
Post #1498196
Posted Monday, March 24, 2014 10:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 35,549, Visits: 32,145
I know... old thread.

Just want to correct that last post. xp_CmdShell support names with spaces in them just like DOS does... you have to put double-quotes around the entire path.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1554274
Posted Tuesday, March 25, 2014 3:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 5:45 AM
Points: 31, Visits: 335
Cheers Jeff! Glad I qualified that with an "AFAIA" now
Post #1554325
Posted Tuesday, March 25, 2014 6:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 35,549, Visits: 32,145
Heh... no worries. I just love working with xp_CmdShell and wanted to make sure that people knew spaces in a name isn't an obstacle.

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1554409
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse