?
Solved

Get list of files inside folder

Posted on 2008-11-13
5
Medium Priority
?
943 Views
Last Modified: 2013-11-05
I need to write a store procedure in SQL 2005 to run every night.
This procedure should go to a specific folder and obtain the name of each an all files existing inside the folder and/or subfolders (get each name, size, format, etc and add it to a table in my database).
0
Comment
Question by:melinalt
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:Ernariash
ID: 22956389
It seems that  you are going to need SSIS or CLR Stored Procedures. Please let me know if you have CLR on in your DB?
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22956419
or master.dbo.xp_cmdshell
INSERT Table EXECUTE @Return = master.dbo.xp_cmdshell  'DIR C:\Program Files\'
 
0
 
LVL 9

Accepted Solution

by:
Ernariash earned 500 total points
ID: 22956477
I will go with CLR Stored Procedures or SSIS but if all you have on is xp_cmdshell. This will give you a good start enjoy :)
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1002&lngWId=5
 
0
 
LVL 1

Author Closing Comment

by:melinalt
ID: 31516626
Thanks a lot.. It did the trick
0
 
LVL 1

Author Comment

by:melinalt
ID: 23007180
Thanks Ernariash....

Your help was great... I wrote a store procedure following your example and it worked perfectly.


Here is the entire code in case someone else needs it:


declare 
@Return int,
@Work varchar(2000),
@PCWrite varchar(2000)
 
SET @PCWrite = '\\myserver\'
SET @Work = 'DIR ' + '"' + @PCWrite + '"'
 
CREATE TABLE #Files ([ID] int IDENTITY (1, 1) NOT NULL,MyFile nvarchar(200))
 
INSERT #Files EXECUTE @Return = master.dbo.xp_cmdshell  @Work
 
'Clean up to get only the files (no directories or others)
DELETE #Files WHERE (MyFile is null) or (MyFile='') or (MyFile like '%Directory of%') or (MyFile like '%<DIR>%') or (MyFile like '%Volume%') or (MyFile like '%bytes free%') or (MyFile like '%file(s)%')
 
'List only the file name and type (without date/size)
SELECT SUBSTRING(MyFile,40,100)  as fName, (right(SUBSTRING(MyFile,40,100), (len(SUBSTRING(MyFile,40,100)) - charindex('.',SUBSTRING(MyFile,40,100))))) as fType FROM #Files order by myfile
 
drop table #Files

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question