determining whether a directory exists? xp_dirtree, xp_subdirs ?
-
Hi,
I pretty new to SQL but need to write a support script for our support team. Basically it check loads of values and does a bit of checking.
The one i am stuck on is checking the existance of a network directory. i can list the subdirectories, but that doesnt help.
I was thinking about displaying the parents directory listing and checking the output from the sp (xp_subdirs) but im not sure how to check the output.
the code im trying to get work is
declare @dir varchar(100)
select@dir = 'EXEC master..xp_subdirs ''C:\'''
if exists(
select *
from @dir
where subdirectory = 'windows'
)
print 'windows exists'
else
print 'not exists'
anyone help me on this?
many thanks,
Ben
-
The problem is that you are attempting to use the VARCHAR variable as a table. You can either A. Change to a table variable (see BOL) or B. Change the IF EXISTS to look for Windows in the variable by using something like PATINDEX or CHARINDEX (See BOL).
Hopefully this will help
Good Hunting!AJ Ahrens
webmaster@kritter.net -
The problem is that you are attempting to use the VARCHAR variable as a table. You can either A. Change to a table variable (see BOL) or B. Change the IF EXISTS to look for Windows in the variable by using something like PATINDEX or CHARINDEX (See BOL).
Hopefully this will help
Good Hunting!AJ Ahrens
webmaster@kritter.net -
CREATE TABLE #temp (FileExists int, IsDirectory int, ParentDirExists int)
INSERT INTO #temp
EXEC master..xp_fileexist 'C:\windows'
IF EXISTS(SELECT IsDirectory FROM #temp WHERE IsDirectory=1)
PRINT 'windows exists'
ELSE
PRINT 'not exists'
DROP TABLE #temp
p.s. (Added)
xp_fileexist is an undocumented extended procedure and my not be present in future release of SQL Server.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for the help guys
-
In addition, thought I would look further into this. It is a shame that MS did not create xp_direxists and documented these Extended procedures. So, I wrote my own xp_direxists which seems to work OK.
Email me if you are interested
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply