?
Solved

check for a file existense master..xp_cmdshell 'dir

Posted on 2014-11-05
8
Medium Priority
?
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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 2000 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

762 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