Getting operating system content into SQL Server

By:   |   Comments (4)   |   Related: > 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:

filelistoutput

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

maintplanlogoutput

 

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, August 6, 2010 - 9:02:25 AM - grobido Back To Top (10022)
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 5, 2010 - 5:06:55 PM - Douglas Osborne Back To Top (10021)
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 5, 2010 - 4:01:10 PM - grobido Back To Top (10020)
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 5, 2010 - 3:13:43 PM - Douglas Osborne Back To Top (10019)
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















get free sql tips
agree to terms