Getting operating system content into SQL Server

By:   |   Updated: 2007-02-06   |   Comments (4)   |   Related: More > Monitoring

Problem
Every once in awhile you may have the need to access some data from the Windows operating system or from the contents of a file.  The need may be to find out what files exist in a directory, to read the contents of a log file into SQL Server or for some other purpose.  Whatever the need, what is a simple way of pulling this data into SQL Server?

Solution
One way of handling this is to use the xp_cmdshell extended stored procedure to access the file system and bring the contents into SQL Server.  By using a combination of both OS level commands and SQL Server commands you can pull this data into SQL Server for processing.

Here is a simple command that reads the file names from a Windows directory.  The following is being performed:

  1. Create a temporary table called #fileList
  2. Insert the contents from the command "dir C:\Backup\" into table #fileList. The /B parameter gives you just the file name without all of the other output you normally get from a DIR command.
  3. Select the contents from #fileList
  4. Drop temporary table #fileList
CREATE TABLE #fileList(line varchar(2000))
INSERT
INTO #fileList
   
EXEC xp_cmdshell 'dir C:\Backup\ /B'
SELECT
line FROM #fileList ORDER BY 1
DROP
TABLE
#fileList

When this gets run we get the following output:

As you can see we also get a NULL entry when running this command. We can simply modify the SELECT query to include a WHERE command to exclude this entry such as the following:

CREATE TABLE #fileList(line varchar(2000))
INSERT
INTO #fileList
     EXEC xp_cmdshell 'dir C:\Backup\ /B'
SELECT
line FROM #fileList WHERE line IS NOT NULL ORDER BY 1
DROP
TABLE #fileList

Another option could be to use the TYPE command to get the contents of a log file.  Here is an example where data is read into a temporary table with the contents from a Maintenance Plan log file.

CREATE TABLE #logFile(line varchar(2000))
INSERT
INTO #logFile
    EXEC xp_cmdshell 'type "C:\Backup\MaintenancePlan_20061218120033.txt" '
SELECT
line FROM #logFile
DROP
TABLE
#logFile

 

As you can see combining the functionality of the operating system commands, SQL Server commands and temporary tables you can now begin to use some of the information that is external to SQL Server.  This is just one way of accessing the data. 

Besides using temporary tables, user tables could be used as well so you have a long term history of the data you are reading in from the operating system.  There are also some other extended stored procedures that expose some of the information from the operating system, but not everything. 

In SQL Server 2005 xp_cmdshell is disabled by default, so you will need to enable this in order to use this command.  Here is a tip that refers to enabling xp_cmdshell for SQL Server 2005.

Next Steps

  • Look into ways of how this could be useful for you environment
  • Look into other creative ways of using OS level commands to further manipulate the data
  • Look at creating batch files that expose the data you need and have SQL Server load the results into tables
  • Look at the other extended stored procedures to see if they provide the data you need
  • If you are using xp_cmdshell make sure you limit the access of this command to only users you trust.


Last Updated: 2007-02-06


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, August 06, 2010 - 9:02:25 AM - grobido Back To Top
You could try this approach.  Not sure how often you need to load the data.

See if you can pipe the output from the DOS command into a text file and then pull the contents of the text file into SQL Server. This would be a two step process, but this may allow you to get the results into SQL Server without having the timeout issue.


Thursday, August 05, 2010 - 5:06:55 PM - Douglas Osborne Back To Top
A huge drive - we have 100,000's of PDFs - so when I go looking for them with this technique - nothing shows up.

This actually happens in Windows XP natively too - the only way to see it is to go to DOS - that seems to allow for the drive delay.

Doug


Thursday, August 05, 2010 - 4:01:10 PM - grobido Back To Top
Doug, what is the exact command that you are running?  I have not run into this issue.  Also, how many rows are returned from the DIR command?


Thursday, August 05, 2010 - 3:13:43 PM - Douglas Osborne Back To Top
How do you handle the situation where actually doing the DIR takes an abnormal amount of time? As a result, it appears that the command is telling you the file does not exist - but in reality the operation is still in process?

 

Thanks,

Doug



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools