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:
- Create a temporary table called #fileList
- 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.
- Select the contents from #fileList
- 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.
