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!'

ELSE

            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

Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald