Solved

check for a file existense master..xp_cmdshell 'dir

Posted on 2014-11-05
8
222 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 39

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 39

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 39

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 39

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 39

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 32
export sql results to csv 6 35
mysql date time 14 29
Incorrect definition of table mysql.proc 7 9
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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