Solved

How can I use T-SQL to find a file name in a windows directory?

Posted on 2014-11-17
2
142 Views
Last Modified: 2014-11-17
In an SQL stored procedure, I need to query a folder in the windows file system and determine whether a specific file name exists in that folder.  Can I do that using T-SQL?
0
Comment
Question by:thoecherl
2 Comments
 
LVL 14

Expert Comment

by:quizwedge
ID: 40448099
You'll need to use xp_cmdshell which is disabled by default. I'm assuming there are security risks with that, so make sure you understand them before implementing it.

Check out http://sqljourney.wordpress.com/2010/06/08/get-list-of-files-from-a-windows-directory-to-sql-server/ for how to enable xp_cmdshell and use the windows file system.

You'll enable it by using

--allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
--Update the currently configured value for advanced options.
RECONFIGURE
GO
--Enable XP_CMDSHELL
EXEC sp_configure 'xp_cmdshell', 1
GO
--Update the currently configured value for this feature.
RECONFIGURE
GO

Open in new window


Below is code that will get the list of files in the directory.
--Create the table to store file list
CREATE TABLE myFileList (FileNumber INT IDENTITY,FileName VARCHAR(256))
--Insert file list from directory to SQL Server
DECLARE @Path varchar(256) = 'dir C:\Import\'
DECLARE @Command varchar(1024) =  @Path + ' /A-D  /B'
INSERT INTO myFileList
EXEC MASTER.dbo.xp_cmdshell @Command
--Check the list
SELECT * FROM myFileList
GO
--Clean up
DROP TABLE myFileList
GO

Open in new window


That code is from the above URL. You could change the select * from myFileList query to be the file name.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 40448100
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 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