Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Determine if Directory Exists using xp_cmdshell

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

 

Comments

Posted by Michael Tillett on 12 October 2010

Hello Brian,

Yes, this sort of functionality is or was lacking out of the box, not entirely sure about 2005/8.  I too have used this solution but refined it to just the following, removing the need for a table:

DECLARE @rc INT

EXEC @rc = xp_cmdshell 'DIR D:\BrianKMcDonald\BigMacFilletOFishQuarterPounderFrenchFriesAndBBQSauce > null'

SELECT @rc

IF @rc = 1 PRINT 'ERROR!!!' ELSE PRINT 'Good to go.'

Posted by kyle.gerbrandt on 20 October 2010

Thanks, I needed that. (There's an error on "DECLARE @Exists SMALLINT = 0".)

Mike's is good too, it just needs double quotes around the path in case there's a space in the path.

Posted by Brian K. McDonald on 21 October 2010

Sorry Kyle. I wrote the script in 2008 and you can declare and set your variables on the same line. This may be the reason you discovered the error. I should have set it via an assignment on a different line.

DECLARE @Exists SMALLINT

SET @Exists = 0

It should work then. :) I am glad it was what you needed though.

Thanks to Mike as well.

Posted by Derek Wharton on 25 October 2010

It seemed I had to use:

SELECT

  @Exists = ( SELECT

                 COUNT(IsValid)

              FROM

                 #DirectoryExists

              WHERE

                 IsValid <> 'The system cannot find the file specified.'

                 AND IsValid IS NOT NULL )

Posted by Andy Hughes on 16 November 2010

Brian

We had the same challenges recently, but with the added complication of needing to dynamically create folders that didn't exist added to the fact that xp_cmdshell is disabled for security reasons.

I found some code that solved the problem.  Unfortunately, I didn't keep the original reference so I only have our modified version and am unable to give credit to the original author.  I think it may have originated on Simple-Talk.

I've included it here anyway.  It utilises 2 undocumented xps introduced in SQL2005 and was used to create server/db subfiolder structure for scripted backups

-- 1 - Variable declaration

DECLARE

@DBName sysname,

@ServerName sysname,

@BaseBackupPath VARCHAR(100),

@FullPath VARCHAR(200)

DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

-- 2 - Initialize variables

SET @ServerName = 'Foo'

SET @DBName = 'Bar'

SET @BaseBackupPath = 'C:\Backups'

-- 3 - @DataPath values

INSERT INTO @DirTree(subdirectory, depth)

EXEC master.sys.xp_dirtree @BaseBackupPath

-- 4 - Create the backup directory using a concatenation of Base, Server and Database names

IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)

AND NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @ServerName)

BEGIN

SELECT @FullPath = @BaseBackupPath + '\' + @ServerName + '\' + @DBName

EXEC master.dbo.xp_create_subdir @FullPath

END

SET NOCOUNT OFF

Leave a Comment

Please register or log in to leave a comment.