troubleshooting Question

Retrive File Information from FTP Site with MSSQL

Avatar of thwinward
thwinwardFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
4 Comments2 Solutions1147 ViewsLast Modified:

I have been able to use the query below to get the file structure of a drive on my PC but I need to be able to get this from an FTP Server.  I will be using this to determine specific actions based on the files being added to this directory.   I don't need the files i just need to see the data that i get in the first query shown.

This Works ....

CREATE TABLE #DirectoryList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir /OD C:\'
DECLARE @Command varchar(1024) =  @Path+'*.*'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE  Line IS NULL
 
SELECT * FROM   #DirectoryList
GO
DROP TABLE #DirectoryList
GO


Need this to work:

CREATE TABLE #DirectoryList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir /OD ftp://ftp1.test.com/export/'
DECLARE @Command varchar(1024) =  @Path+'*.*'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE  Line IS NULL
 
SELECT * FROM   #DirectoryList
GO
DROP TABLE #DirectoryList
GO
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros