xp_cmdshell

Question

Post reply

xp_cmdshell

David-250683

Hall of Fame

Points: 3452
More actions
November 30, 2007 at 10:56 am

#377276

How can I capture the results from this example sp into a variable or table for further processing?
EXEC master..xp_cmdshell 'dir c:\*.txt'
We have clients that from time to time place data in a text file on our ftp site which we then import. I wanted to create a process that will automatically check the directory and then inform me when a certain file have been placed there.
Here's is a different method, but hoping for something less cumbersome:
DECLARE @return bit
-- I search the ftp directory for files with a certain extension and have those names printed to a local file.
EXEC master..xp_cmdshell 'print \\gfiorfolk\*.txt > c:\Test.txt', NO_OUTPUT
-- Then read the contents of the file for a certain string.
EXEC @return = master..xp_cmdshell 'FIND ".txt" C:\Test.txt', NO_OUTPUT
IF@return = 0
SELECT 'File exists'
ELSE RETURN

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

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

CREATE TABLE #DirInfo

(

SubDirectory SYSNAME,

Depth INT,

IsFile INT

)

INSERT INTO #DirInfo

(SubDirectory, Depth, IsFile)

EXEC Master.dbo.xp_DirTree 'C:\',1,1

SELECT *

FROM #DirInfo

WHERE IsFile = 1

AND SubDirectory LIKE ('%.TXT')

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

David-250683 Hall of Fame Points: 3452 More actions · Answer 2

That is absolutely perfect!!! Thanks Jeff.

I never heard of xp_DirTree and did not find it in BOL. How did you learn it? What other secret commands are there?

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Heh... there's just a pot-wad of undocumented features and sprocs in SQL Server... most would warn against using them because if they're undocumented, they could disappear on the next service pack. While that be true, there's nothing to prevent documented features from disappearing, either 😉

I have verified that xp_DirTree is available in all versions of 2k, 2k5, and now, 2k8. So, I'm thinking we're safe for a couple of more years.

I dig a lot of these things out myself just because I have an overwhelming curiosity about things. For example, if you Google xp_DirTree, you'll find lot's and lot's of articles and examples on it... just not many with that nice third parameter that I found which is what trully makes the xSproc useful. I think I've only seen one other person who's discovered it and documented it on the Web.

By the way... when you do Google for xp_DirTree, you find lot's of the other undocumented features that are available...

...sometimes, BOL just isn't enough... gotta search, gotta play.

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