join the MSSQLTips community

Today's Site Sponsor


 

SQL Compare quickly and easily compares and synchronizes SQL Server database schemas
 



SQL Server permissions and security auditing: Idera SQL secure

Accessing the Windows File System from SQL Server

Written By: Jeremy Kadlec -- 6/12/2007 -- read/post comments -- print -- Bookmark and Share

Rating: (not rated yet) Rate

Problem
Within my SQL Server T-SQL stored procedures and scripts I need to find out the files in a specific directory.  What are some approaches to do so?  I need some flexibility to capture the file names and general information for subsequent processing.  What are the native SQL Server options as well as the custom options that are available?

Solution
Depending on the exact needs dictates the command(s) that need to be issued from SQL Server 2000 or 2005.  Below outlines some options to access the Windows file system related information with both native and custom code.  In general the following commands exist:

Extended Stored Procedure - xp_cmdshell - Ability to execute any DOS command line code.
(Platform = SQL Server 2000 and SQL Server 2005)

EXEC master.dbo.xp_cmdshell 'dir c:\'
GO 

Extended Stored Procedure - xp_fixeddrives - Ability to capture the free drive space in megabytes.
(Platform = SQL Server 2000 and SQL Server 2005)

EXEC master.dbo.xp_fixeddrives
GO
 

Extended Stored Procedure - xp_subdirs - Ability to capture the sub directories, but not files.
(Platform = SQL Server 2000 and SQL Server 2005)

EXEC master.dbo.xp_subdirs 'c:\'
GO
 

Custom Code - snippet - Ability to capture the file names in a temporary table with xp_cmdshell.
(Platform = SQL Server 2000 and SQL Server 2005)

/*
----------------------------------------------------------------------------
-- Object Name: Script
-- Project: Misc
-- Business Process: Supports SQL Server 2000 and 2005
-- Purpose: Capture the files in a specific directory with xp_cmdshell
-- Database: N\A
-- Dependent Objects:
-- Called By: N\A
-- Upstream Systems: N\A
-- Downstream Systems: N\A
--
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
--
*/

SET NOCOUNT ON

-- 1 - Variable declarations
DECLARE @CMD1 varchar(5000)
DECLARE @CMD2 varchar(5000)
DECLARE @FilePath varchar(200
)

-- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list
CREATE TABLE #OriginalFileList (
Col1 varchar(1000)
NULL
)

-- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list
CREATE TABLE #ParsedFileList (
PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
DateTimeStamp datetime NOT NULL,
FileSize varchar(50) NOT NULL,
FileName1 varchar (255) NOT
NULL
)

-- 4 - Initialize the variables
SELECT @CMD1 = ''
SELECT @CMD2 = ''
SELECT @FilePath =
'C:\Progra~1\Micros~2\MSSQL.1\MSSQL\Log\'

-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.*' + char(39)

-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) +
'EXEC ' +
@CMD1

-- 7 - Execute the string to populate the #OriginalFileList table
EXEC (@CMD2)

-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList
WHERE COL1 IS
NULL

DELETE FROM #OriginalFileList
WHERE COL1 LIKE
'%Volume%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Directory%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%<DIR>%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE
'%bytes%'

-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM
#OriginalFileList

-- ********************************************************************************
-- INSERT code here to process the data from the
#ParsedFileList table
-- ********************************************************************************

-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE
#ParsedFileList

SET NOCOUNT OFF
GO

 

Next Steps

  • Keep in mind that the master.dbo.xp_cmdshell extended stored procedure will permit you to execute any Windows command just as if you were in a DOS session.  So you need to review the capabilities of this extended stored procedure from a security perspective to determine the impacts in your environment.
  • The master.dbo.xp_cmdshell extended stored procedure is available in both SQL Server 2000 and 2005.  In SQL Server 2005 the master.dbo.xp_cmdshell extended stored procedure is off by default and needs to be enabled via the Surface Area Configuration Manager.
  • For more information about securing the master.dbo.xp_cmdshell extended stored procedure, check out - <-- Security Bulletin --> How do I secure an extended stored procedure?
Readers Who Read This Tip Also Read Free Live Webcast Comment or Ask Questions About This Tip



Get Our Tips Newsletter

We keep 50,000+ SQL Server professionals informed.



Red Gate Software - SQL Backup

Need to create smaller, more reliable backups? Ensure your backups are optimized for robustness and speed with Red Gate SQL Backup Pro. Compress your backups by up to 95% and minimize disruptions to your backups caused by flaky networks with new network resilience. 'Network resilience puts SQL Backup Pro 6 at the top of the list of backup tools. It’s the cherry on top, and I definitely recommend using SQL Backup over SQL Server 2008 native backups.' William Durkin, Development DBA. Download now.

Download now!

More SQL Server Tools
SQL compliance manager

SQL defrag manager

SQL Backup

SQL Data Generator

SQL safe backup


Sponsor Information
Free SQL Server performance monitoring dashboard – Idera SQL check

Increase your SQL speed and accuracy with code completion from SQL Prompt.

You don't know, what you don't know about SQL Server... Customized Consulting and Training

Win a FREE trip to SQL PASS! All expenses paid!

Free Web Cast - 5 Common High-Availability Mistakes by Michael Campbell - August 11, 2010



Copyright (c) 2006-2010 Edgewood Solutions, LLC All rights reserved
privacy statement | disclaimer | copyright | advertise | write for mssqltips | feedback | about
Some names and products listed are the registered trademarks of their respective owners.


CareerQandA.com | MSSharePointTips.com | MSSQLTips.com