Using File System Object from T-SQL

[Alan Z. Scharf]

1. Is there a way to use the File System Object from a T-SQL stored
procedure?

I want to read in the filenames from a folder into a table of filenames.

2. I have this working in Access VBA with an Access table using the
abbreviated code below, but need to switch to SQLServer.

2. A search on Google indicates that sp_OA can be used to do this, but I
haven't found any specific examples for the File System Object. Nor do I
find them in BOL..
Does anyone know of any examples?

Thanks.

Alan

FILE SYSTEM OBJECT CODE IN ACCESS
--------------------------------------------------
' File System Object variables
Dim fso As Scripting.FileSystemObject ' File System Object
Dim fldr As Scripting.Folder ' Folder
Dim file As Scripting.file ' File
dim strFileName as String

' Initialize file system objects
Set fso = New Scripting.FileSystemObject
Set fldr = fso.GetFolder(strFolder)

' Refresh list of document files
For Each file In fldr.Files
strFileName = file.Name
INSERT INTO tblDocumentNames (DocumentName)
VALUES(strFileName)
Next

' Close file objects
Set file = Nothing
Set fldr = Nothing
Set fso = Nothing
___________________________
Alan Z. Scharf
GrapeVine Systems
New York City

[Adam Machanic]

Refer to the thread posted today, with the subject "sp_oaMethod".

"Alan Z. Scharf" <asc...@grapevines.com> wrote in message
news:ums5$zjnDH...@TK2MSFTNGP12.phx.gbl...

[Alan Z. Scharf]

Adam,

Thanks for your reply.

The sproc code below indicates how far I got so far.

Two questions:

1. How do I implement a WHILE equivalent to the VBA ForEach loop below to go
through filenames in the fso folder?

2. How do I imlement the folder as an input param to the sproc?

A. If I specify an input param of @Folder NVARCHAR(200) and then try

EXEC sp_OAMethod @fso, 'GetFolder(@Folder)', @fldr OUTPUT --
I get NULL output for @fldr

B. If I try EXEC sp_OAMethod @fso, 'GetFolder(' + @Folder + ')', @fldr
OUTPUT -- I get compile error at '+'

Thanks for any help you can give.

Alan

SPROC CODE
-------------------
/*
Purpose: To get filenames from disk
*/

ALTER PROCEDURE dbo.sproc_GetFilesTest

AS
SET NOCOUNT ON
BEGIN
DECLARE @fso INTEGER -- File system object
DECLARE @fldr INTEGER -- File system folder
DECLARE @file INTEGER -- File system file
DECLARE @filename NVARCHAR(200) -- File name

-- Create file system object
EXEC sp_OACreate 'scripting.filesystemobject', @fso OUTPUT

-- Create folder object from input parameter
EXEC sp_OAMethod @fso, 'GetFolder("c:\permal\documents")', @fldr OUTPUT

-- Test
SELECT @fso -- Produces integer on execution
SELECT @fldr -- Produces integer on execution

/* HOW DO I DO THIS?
-- Get list of document filenames

For Each file In fldr.Files

@FileName = @file.Name
INSERT INTO tblDocuments(DocumentName)
VALUES(@FileName)
Next
*/

END

[Adam Machanic]

I haven't figured out how to get it to return file names.... The files
collection doesn't appear to be accessible.

As for passing the param, you could try:

EXEC sp_OAMethod @fso, 'GetFolder', @fldr OUTPUT, @Folder

"Alan Z. Scharf" <asc...@grapevines.com> wrote in message

news:emVcqlln...@TK2MSFTNGP11.phx.gbl...

> Adam,
>
> Thanks for your reply.
>
> The sproc code below indicates how far I got so far.
>
> Two questions:
>
> 1. How do I implement a WHILE equivalent to the VBA ForEach loop below to
go
> through filenames in the fso folder?
>
> 2. How do I imlement the folder as an input param to the sproc?
>
> A. If I specify an input param of @Folder NVARCHAR(200) and then try
>
> EXEC sp_OAMethod @fso, 'GetFolder(@Folder)', @fldr

[Adam Machanic]

Actually, to pass param:

DECLARE @foldercommand varchar(50)
SET @foldercommand = 'GetFolder("' + @foldername + '")'

EXEC sp_OAMethod @fso, @foldercommand, @fldr OUTPUT

... However, I still have not figured out how to get the 'files' collection
to return anything but a count property, unless you specifically ask for an
item by name (e.g. item("<valid item name>") works, but item(0) doesn't)

"Alan Z. Scharf" <asc...@grapevines.com> wrote in message

news:emVcqlln...@TK2MSFTNGP11.phx.gbl...

> Adam,
>
> Thanks for your reply.
>
> The sproc code below indicates how far I got so far.
>
> Two questions:
>
> 1. How do I implement a WHILE equivalent to the VBA ForEach loop below to
go
> through filenames in the fso folder?
>
> 2. How do I imlement the folder as an input param to the sproc?
>
> A. If I specify an input param of @Folder NVARCHAR(200) and then try
>
> EXEC sp_OAMethod @fso, 'GetFolder(@Folder)', @fldr

[Alan Z. Scharf]

Adam,

Thanks again for your help. I learned a lot.

There's probably some way to loop through the filenames, although it
certainly isn't obvious. I've done a lot of searching and have not found an
example of using fso with sp_OA.

Regards,

Alan

"Adam Machanic" <amac...@air-worldwide.nospamallowed.com> wrote in message
news:#bXS8pmn...@tk2msftngp13.phx.gbl...

[Alan Z. Scharf]

Adam,

Thanks once again!

Alan

"Adam Machanic" <amac...@air-worldwide.nospamallowed.com> wrote in message

news:uGgYAxmn...@TK2MSFTNGP10.phx.gbl...

[Billy Yao [MSFT]]

Hey Alan,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

From your conservation with Adam, I understand that you would like to know whether there is a
way to retrieve the filenames from a folder into a SQL Server table.

I think Adam has pointed out a source of your problem but let me see if I can deal with this
issue correctly and gracefully.

You used automation to handle the filenames, but sp_oamethod did have some limitations on
the system filenames retrieving and it is not recommended that the file system objects were
handled like that.

Base on my experience, the best way is to use SQL-DMO method to retrieve the system
filenames. The inner extended stored procedure XP_DIRTREE may be the graceful way to
browse the system files. This powerful xp_dirtree can retrieve not only local system files but
the remote ones as well. Meanwhile, you can use use INSERT INTO ...­ EXEC to insert the
result sets directly to your table.

The detailed Syntax of XP_DIRTREE is shown as below:

XP_DIRTREE 'param1','param2','param3'

where,
param1 = The Root of the tree to be traversed.
param2 = The max depth level of the tree
param3 = 1 to show files , 0 to show only dirs.

The sample is alike: Insert into YourTableName exec master..xp_dirtree 'C:\',2,1
#Note: the table schema should match the result set returned from xp_dirtree

If you have to traverse a tree on the remote machine use "\\machine_name\sharename"
where sharename is the root of the tree to be traversed.

Another better way is to use XP_CMDSHELL to retrieve the detailed information and then use
cursor to trim each row and get the only filenames. It's also a grace way to handle the File
System Objects when you want to get detailed information. :)

Alan, does this answer your question? Please apply my suggestion above and let me know if
it helps you resolve your problem. If there is anything more I can assist you with, please feel
free to post it in the group.

Best regards,

Billy Yao
Microsoft Online Partner Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

[Adam Machanic]

Billy,

I also just noticed xp_getfiledetails... Looping through a temp table
created by xp_dirtree, Alan could then get more detailed info (if necessary)
using xp_getfiledetails... I'm not sure if that would be any better or worse
than using xp_cmdshell or writing his own extended sproc, though.

On this same subject, is there any way to get a pointer to _NewEnum from
sp_OAMethod or sp_OAGetProperty? I realize it's probably not the best way
to do things, but I'm still curious if it can be done...

"Billy Yao [MSFT]" <v-bi...@online.microsoft.com> wrote in message
news:xajzkSv...@cpmsftngxa06.phx.gbl...

[Alan Z. Scharf]

Billy,

Thanks very much for your reply.

So far, I used xp_cmdshell to do the job in the sproc at end of message.
This works very nicely and gets rid of all the extraneous data, as well as
the time column which I didn't want.

I believe I will still need the xp_OA for FSOoperations to do another task,
i.e. renaming disk files from a form in Access calling a stored procedure..

This is elegantly simply using FSO in the Access function below.

It's possible I may be able to do this with xp_OA, since it doesn't involve
file collections. I haven't tried it yet. If you have any suggestions, I
welcome them.

Can SQL-DMO be used to rename files?

Thanks again.

Alan

' Access File Rename Function
' ---------------------------------
Public Function RenameFiles(strFolder As String, strOldFileName As String,
strNewFileName As String)
' Purpose: To rename files on disk from Access form

Dim fso As Scripting.FileSystemObject ' File System Object
Dim fldr As Scripting.Folder ' Folder

Dim file As Scripting.File ' File Item

Set fso = New Scripting.FileSystemObject
Set fldr = fso.GetFolder(strFolder)

strOldFileName = (strFolder & strOldFileName)
Set file = fso.GetFile(strOldFileName)
file.Name = strNewFileName ' This renames file on disk

' Close file objects
Set file = Nothing
Set fldr = Nothing
Set fso = Nothing

End Function

--Stored Procedure to get filenames from folder
------------------------------------------------
ALTER PROCEDURE sproc_GetFileNames
AS
BEGIN

DELETE FROM tblDocuments

INSERT tblDocuments (FileName)
EXEC master..xp_cmdshell 'dir "c:\permal\test documents" '

DELETE FROM tblDocuments WHERE FileName LIKE '%DIR%'
DELETE FROM tblDocuments WHERE FileName is null
DELETE FROM tblDocuments WHERE SUBSTRING(FileName, 3, 1) <> '/'

UPDATE tblDocuments SET FileDate = LEFT(FileName, 10)
UPDATE tblDocuments SET FileName = SUBSTRING(FileName, 30, 200)
UPDATE tblDocuments SET SizeIndex = CHARINDEX(CHAR(32), FileName)
UPDATE tblDocuments SET FileName = SUBSTRING(FileName, (SizeIndex + 1),
200)

--Test
SELECT * FROM tblDocuments
END

"Adam Machanic" <amac...@air-worldwide.nospamallowed.com> wrote in message

news:#k5dF7vn...@tk2msftngp13.phx.gbl...

[Billy Yao [MSFT]]

Hi Alan,

Thanks for your feedback!

Now I'm aware of the reason why you'd like to retrieve and store the file system objects in the
database at first. You are eager to deal with the real file system objects (such as rename file
names, delete files, create files etc) rather than store the information of these objects for OLTP
or OLAP, right? :)

However, you are recommended NOT to perform like that even though you can. SQL Server
is a RDBMS that deals with the data by means of transaction process and analysis process. It
does not deal with the file system objects elegantly and even it doesn't need to.

The only way you must do this may be for your convenient handling the files in database or for
some of your 3rd party applications. In this way, I understand that you need to perform like that
with no choice and I'm still willing to assist you out of the predicament..

As I mentioned before, The sp_OA procedures is OLE Automation like sp_OACreate,
sp_OAMethod, and so on, allows a connection through Transact-SQL commands to create
and use Component Object Model (COM) based objects. The procedures are built into SQL
Server as an extended stored procedure (XPROC), contained in Sqlole32.dll.

This is another example of how SQL Server behavior can be extended with an XPROC
implementation, but I'm not so sure whether the sp_OA can really rename the files. So maybe
you can move futher with this Automation mathod.

For more information of the code sample, see the following article in the Microsoft Knowledge
Base:
152801 INF: Examples of Sp_OA Procedure Use and SQLOLE.Transfer Object
http://support.microsoft.com/?id=152801

According to my knowledge, there are no existed stored procedures (SP) or extended stored
procedures (XPROC) to help directly modify the file system objects. Based on my experience,
however, I believe you can use your own extended stored procedure (XPROC) to gracefully
realize it. You can create a new XPROC calling DLL to easily handle the file system objects.

For your convenient to creating an Extended Stored Procedure, I recommend you reference
the following article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/vccore/html/vcconusingwizardtocreateextendedstoredprocedure.asp

Alan, does this answer your question? Please feel free to let me know if this help solves your
problem. If there is anything more I can still assist you with, please feel free to post it in the
group.

Good Luck!

[Alan Z. Scharf]

Billy,

Thanks again for your detailed message.

I'm not really concrned with transactions. This is just a document
management database that holds the filenames in afolder. The table of
filenames can be refreshed at any time with my sproc using xp_cmdshell. The
only data is the filenames stored in NTFS system, so nothing to be lost.

The file renaming is for convenience of user, who would otherwise have to
rename in Windows explorer. However it easier to do from an Access form,
particularly since I have built-in methods to filter the documents by
several different categories.

I will try the two articles you referred to.

Thanks again.

Regards,

Alan

[Adam Machanic]

I'm sure you can use the rename method of the FSO without a problem from
sp_oaMethod...

"Alan Z. Scharf" <asc...@grapevines.com> wrote in message

news:uWgCIf4n...@tk2msftngp13.phx.gbl...

[Alan Z. Scharf]

Thanks again Adam for all your help. I may be able to try that this
weekend.

Regards,

Alan

"Adam Machanic" <amac...@air-worldwide.nospamallowed.com> wrote in message

news:Ox2qSq7n...@TK2MSFTNGP11.phx.gbl...