Solved

check for a file existense master..xp_cmdshell 'dir

Posted on 2014-11-05
8
228 Views
Last Modified: 2014-11-05
Hi there,
I'm wondering whats the syntax to execute the following sql statement?
declare @i as integer
 exec @i = master..xp_cmdshell 'dir \"E:\Adept Technologies\Documents\Building\B07-100001\B07-100001_Expired*.doc\" /b'
 if @i <> null
 begin
 print 'File does exist'
 end
 else
 begin
 print 'file does not exist'
  end

Open in new window

What I'm trying to do is to see if a file exist in a certain directory, the things that I have encounter if the directory has a space the statement doesn't return a true value.
I need create the directory path dynamically, what I mean is to search on a directory that looks like this
E:\Adept Technologies\Documents\Building\ + NumberKey +'/' + 'Filename' How can I achieved this?
Thanks!
0
Comment
Question by:COHFL
  • 5
  • 3
8 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 40424712
You can try something like in the code below and all needed is to check you test condition to decide if file exists or not:

declare @sqlstr nvarchar(max)
declare @i table (col1 varchar(max) null)
declare @NumberKey sysname
set @NumberKey = 'B07-100001'

--set @sqlstr = N'exec master..xp_cmdshell ''dir \"E:\Adept Technologies\Documents\Building\B07-100001\B07-100001_Expired*.doc\" /b'''

--with dynamic path
set @sqlstr = N'exec master..xp_cmdshell ''dir \"E:\Adept Technologies\Documents\Building\'+@NumberKey+'\'+@NumberKey+'_Expired*.doc\" /b'''

print (@sqlstr);

insert @i execute sp_executesql @sqlstr

select * from @I
0
 

Author Comment

by:COHFL
ID: 40424720
Icohan,
when I execute the suggested syntax I get this:

Col1
The filename, directory name, or volume label syntax is incorrect.
NULL
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40424734
That means the file does not exists right?

I put the
print (@sqlstr);
command as well so you should be able to take that DIR command and run it ON that SQL Server to see what you get. If you get the same message...you get the idea.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:COHFL
ID: 40424739
nope the file does exist
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40424742
K - you had an extra \ in front of the DIR - please try below:

declare @sqlstr nvarchar(max)
declare @i table (col1 varchar(max) null)
declare @NumberKey sysname
set @NumberKey = 'B07-100001'

--set @sqlstr = N'exec master..xp_cmdshell ''dir \"E:\Adept Technologies\Documents\Building\B07-100001\B07-100001_Expired*.doc\" /b'''

--with dynamic path
set @sqlstr = N'exec master..xp_cmdshell ''dir "E:\Adept Technologies\Documents\Building\'+@NumberKey+'\'+@NumberKey+'_Expired*.doc\*.*" /b'''

print (@sqlstr);

insert @i execute sp_executesql @sqlstr

select * from @i
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40424743
so if the file does not exists the SQL code obviously returns the truth - right? Or does what you need in other words.

To test the opposite when the file exists just create some empty file with that Filename pattern on that SQL Server in that path and run the script again.
0
 

Author Comment

by:COHFL
ID: 40424791
ok, I just have to modify the dynamic part  a bit to match what I need.
it looks like this now:
 --with dynamic path
 set @sqlstr = N'exec master..xp_cmdshell ''dir "E:\Adept Technologies\Documents\Building\'+@NumberKey+'\'+@NumberKey+'_Expired*.doc" /b'''

the only thing I need to return is true or false not the file name or anything else. Right now it returns two rows:
col1
B07-100001_Expired150_20071022.doc
NULL
0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 40424793
So your complete SQL code should be something like:

declare @sqlstr nvarchar(max)
declare @i table (col1 varchar(max) null)
declare @NumberKey sysname
set @NumberKey = 'B07-100001'

--set @sqlstr = N'exec master..xp_cmdshell ''dir \"E:\Adept Technologies\Documents\Building\B07-100001\B07-100001_Expired*.doc\" /b'''

--with dynamic path
set @sqlstr = N'exec master..xp_cmdshell ''dir "E:\Adept Technologies\Documents\Building\'+@NumberKey+'\'+@NumberKey+'_Expired*.doc" /b'''

--print (@sqlstr);

insert @i execute sp_executesql @sqlstr

if exists (select * from @i where col1 like '%The system cannot find the path specified.%')
print 'File does not exist'
else
print 'File does exist'
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question