Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mdelgado
Posting Yak Master
141 Posts |
 Posted - 2002-09-17 : 10:26:52
|
| Hello all...I'm trying to figure out a way to grab the name of a file, store it in a Global Variable and use it to create and name a new file.I'm thinking of adding the filesystem as a linked server but I can't seem to get it to work.Can someone pleae point me in the right direction? thanks. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-17 : 10:37:48
|
| It would have to utilize the LDAP/Active Directory data provider to set it up as a linked server, but why do that at all?You can use xp_cmdshell to run a "dir" command to get the contents of the directory, and dump the results into a table:CREATE PROCEDURE LoadAllFiles @path varchar(255)='C:\', @pattern varchar(50)='*.txt' ASSET NOCOUNT ONDECLARE @file varchar(255)CREATE TABLE #filenames (filename varchar(255))INSERT INTO #filenames (filename) EXECUTE ('master..xp_cmdshell ''dir/b ' + @path + @pattern + '''')SELECT * FROM #filenamesYou could also create a SQL Server job with an ActiveX step that uses the Scripting.FileSystemObject to find and manipulate files, but if all you want is a directory listing the above should work fine. |
 |
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2002-09-17 : 10:42:51
|
| Looks good.Thank you.... |
|
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2002-09-17 : 10:55:16
|
| I hate to be picky, but is there anyway to modify this to just show me the Filename?Also, can this be modifited to go to a network Drive (i.e. \\Robv\c:\sql) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-17 : 11:00:28
|
Just run an UPDATE to remove the extension:UPDATE #filenamesSET filename=SubString(filename, 1, CharIndex('.', filename) -1)Although if you have Readme.doc and Readme.txt files in the same directory you will pretty much destroy the usability of the file list  Don't use a colon (:) in UNC paths, use a $ instead:\\Robv\c$\sql |
|
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2002-09-17 : 11:03:26
|
| yep, got rid of the colon and all is well now.This is a nice little tool, thanks again... |
|
|
|
|
|
|
|
|
Query Filesystem..