Denis Orozco
asked on
check for a file existense master..xp_cmdshell 'dir
Hi there,
I'm wondering whats the syntax to execute the following sql statement?
I need create the directory path dynamically, what I mean is to search on a directory that looks like this
E:\Adept Technologies\Documents\Bui lding\ + NumberKey +'/' + 'Filename' How can I achieved this?
Thanks!
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
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\Bui
Thanks!
ASKER
Icohan,
when I execute the suggested syntax I get this:
Col1
The filename, directory name, or volume label syntax is incorrect.
NULL
when I execute the suggested syntax I get this:
Col1
The filename, directory name, or volume label syntax is incorrect.
NULL
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.
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.
ASKER
nope the file does exist
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\Bui lding\B07- 100001\B07 -100001_Ex pired*.doc \" /b'''
--with dynamic path
set @sqlstr = N'exec master..xp_cmdshell ''dir "E:\Adept Technologies\Documents\Bui lding\'+@N umberKey+' \'+@Number Key+'_Expi red*.doc\* .*" /b'''
print (@sqlstr);
insert @i execute sp_executesql @sqlstr
select * from @i
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\Bui
--with dynamic path
set @sqlstr = N'exec master..xp_cmdshell ''dir "E:\Adept Technologies\Documents\Bui
print (@sqlstr);
insert @i execute sp_executesql @sqlstr
select * from @i
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.
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.
ASKER
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\Bui lding\'+@N umberKey+' \'+@Number Key+'_Expi red*.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_2007 1022.doc
NULL
it looks like this now:
--with dynamic path
set @sqlstr = N'exec master..xp_cmdshell ''dir "E:\Adept Technologies\Documents\Bui
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_2007
NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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\Bui
--with dynamic path
set @sqlstr = N'exec master..xp_cmdshell ''dir \"E:\Adept Technologies\Documents\Bui
print (@sqlstr);
insert @i execute sp_executesql @sqlstr
select * from @I