Solved

check for a file existense master..xp_cmdshell 'dir

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

Author Comment

by:COHFL
ID: 40424739
nope the file does exist
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now