Subscribe to this blog
Briefcase
Print
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
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