Getting file names from path?

Hi,
Need some help here.
Is there a way to get a list of file names from the file system using T-SQL?
Or is there some other method (DTS or Setting up a linked server) which will
accomplish this? I need to provide a list of file names from a folder where
I will provide the path statement and SQL will return the file names that
exist within that particular folder. From there I can concatonate the path
with the file names then store the full path statements with file name into
a table.
Thanks in advance.
CurtYou can use sp_OA* methods to do this. Another option is to use xp_cmdshell
with a dir command like:
EXEC master..xp_cmdshell 'DIR C:\ /B'
Anith|||Hi
To add to Anith's suggestions you could write an ActiveX script in a similar
way to using the sp_OA* commands see http://www.sqldts.com/default.aspx?292
John
"Fishman" wrote:

> Hi,
> Need some help here.
> Is there a way to get a list of file names from the file system using T-SQ
L?
> Or is there some other method (DTS or Setting up a linked server) which wi
ll
> accomplish this? I need to provide a list of file names from a folder wher
e
> I will provide the path statement and SQL will return the file names that
> exist within that particular folder. From there I can concatonate the path
> with the file names then store the full path statements with file name int
o
> a table.
> Thanks in advance.
>
> Curt
>
>|||You could use a CLR TVF like so:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;
public partial class UserDefinedFunctions
{
/// <summary>
/// Returns a table with one column (Name) representing all files within
the path that match the criteria.
/// </summary>
/// <param name="path">The path to search. Can be relative</param>
/// <param name="searchPattern">The wildcard search pattern. Use "*" or
null for all.</param>
/// <param name="allDirectories">Search all directories under
path.</param>
/// <param name="includeFullPath">Include the full path in the
results.</param>
/// <returns>Table</returns>
[SqlFunction(FillRowMethodName = "FillFileNameRow", TableDefinition =
"Name nvarchar(300)")]
public static IEnumerable GetFileNames(string path, string
searchPattern, bool allDirectories, bool includeFullPath)
{
if (searchPattern == null)
searchPattern = "*";
string[] files = Directory.GetFiles(path, searchPattern,
allDirectories ? SearchOption.AllDirectories :
SearchOption.TopDirectoryOnly);
if (includeFullPath == false)
{
for (int i = 0; i < files.Length; i++)
{
files[i] = Path.GetFileName(files[i]);
}
}
return files;
}
/// <summary>
/// Break the row into columns for sql.
/// </summary>
public static void FillFileNameRow(object obj, out string Name)
{
string fn = (string)obj;
Name = fn;
}
};
Usage
--
select * from dbo.GetFileNames('c:', null, 0, 1)
Name
c:\AUTOEXEC.BAT
c:\boot.ini
c:\cmds.txt
c:\CONFIG.SYS
c:\dc100a.doc
c:\desktop.ini
...
William Stacey [MVP]
"Fishman" <qwpit@.kcnet.com> wrote in message
news:OWdCp5aJGHA.1676@.TK2MSFTNGP09.phx.gbl...
| Hi,
|
| Need some help here.
| Is there a way to get a list of file names from the file system using
T-SQL?
| Or is there some other method (DTS or Setting up a linked server) which
will
| accomplish this? I need to provide a list of file names from a folder
where
| I will provide the path statement and SQL will return the file names that
| exist within that particular folder. From there I can concatonate the path
| with the file names then store the full path statements with file name
into
| a table.
|
| Thanks in advance.
|
|
| Curt
|
||||You can use the FileSystemObject to enumerate a list of files. This example
is within the context of Office VBA scripting, but the syxtax for using it
with a DTS package / VBScript Task would be very similar if not the same.
http://msdn.microsoft.com/library/d...mfilesystem.asp
"Fishman" <qwpit@.kcnet.com> wrote in message
news:OWdCp5aJGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Need some help here.
> Is there a way to get a list of file names from the file system using
> T-SQL?
> Or is there some other method (DTS or Setting up a linked server) which
> will
> accomplish this? I need to provide a list of file names from a folder
> where
> I will provide the path statement and SQL will return the file names that
> exist within that particular folder. From there I can concatonate the path
> with the file names then store the full path statements with file name
> into
> a table.
> Thanks in advance.
>
> Curt
>