Recently, I came across a forum post from a user trying to determine if a directory existed in the file system. The user tried several ways to get a valid response from the system and came up short. I have done something similar in the past (years ago though) and thought I would write a quick blog to help him out. Below is a script that can be used to test if a directory exists.
Script 1: Determine if Directory Exists
DECLARE @FullDirectoryPathStatement VARCHAR(255)
--Test... This one does NOT exist on my system
SET @FullDirectoryPathStatement = 'DIR "C:\Program Files\Microsoft SQL Server\BrianKMcDonald\BigMacFilletOFishQuarterPounderFrenchFries" /B'
--Test... This one does exist on my system
SET @FullDirectoryPathStatement = 'DIR "C:\Program Files\Microsoft SQL Server" /B'
CREATE TABLE #DirectoryExists (IsValid VARCHAR(MAX))
INSERT INTO #DirectoryExists
EXEC xp_cmdshell @FullDirectoryPathStatement
DECLARE @Exists SMALLINT = 0
SELECT @Exists =
(SELECT COUNT(IsValid) FROM #DirectoryExists
WHERE IsValid <> 'File Not Found' AND IsValid IS NOT NULL)
IF @Exists = 0
SELECT 'DIRECTORY DOES NOT EXIST!'
SELECT 'DIRECTORY EXISTS!'
DROP TABLE #DirectoryExists
If you are testing this script, you have SQL Server installed and do not change anything, you should get the “DIRECTORY EXISTS!” as a result. However, try commenting out the second test and you should get the “DIRECTORY DOES NOT EXIST!” unless that is you have a folder BrianKMcDonald\BigMacFilletOFishQuarterPounderFrenchFries in your Program Files directory. J
Also note, if the xp_cmdshell setting is turned off, you will get an error like below.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
You or a system admin will need to enable this before getting this to work. I hope that you have enjoyed this quick blog. If you did, please rate it! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald.
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works