April 1, 2008 at 8:17 am
Hi,
All the 3 given methods return file not exits eventhough the file is there
-- using the scripting object
declare@Path varchar(128) ,
@FileName varchar(128)
select@Path = 'C:\' ,
@FileName = 'myfile.txt'
declare@objFSys int
declare @i int
declare@File varchar(1000)
select @File = @Path + @FileName
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
if @i = 1
print 'exists'
else
print 'not exists'
exec sp_OADestroy @objFSys
-- using xp_cmdshell
declare@Path varchar(128) ,
@FileName varchar(128)
select@Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @cmd varchar(1000)
create table #a(s varchar(1000))
select @cmd = 'dir /B ' + @Path + @FileName
insert #a exec master..xp_cmdshell @cmd
if exists (select * from #a where s = @FileName)
print 'exists'
else
print 'not exists'
drop table #a
-- using xp_fileexists
declare@Path varchar(128) ,
@FileName varchar(128)
select@Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @i int
declare@File varchar(1000)
select @File = @Path + @FileName
exec master..xp_fileexist @File, @i out
if @i = 1
print 'exists'
else
print 'not exists'
Regards,
Balamurugan G
April 1, 2008 at 8:31 am
balamurugan.ganesan (4/1/2008)
Hi,All the 3 given methods return file not exits eventhough the file is there
-- using the scripting object
declare@Path varchar(128) ,
@FileName varchar(128)
select@Path = 'C:\' ,
@FileName = 'myfile.txt'
declare@objFSys int
declare @i int
declare@File varchar(1000)
select @File = @Path + @FileName
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
if @i = 1
print 'exists'
else
print 'not exists'
exec sp_OADestroy @objFSys
-- using xp_cmdshell
declare@Path varchar(128) ,
@FileName varchar(128)
select@Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @cmd varchar(1000)
create table #a(s varchar(1000))
select @cmd = 'dir /B ' + @Path + @FileName
insert #a exec master..xp_cmdshell @cmd
if exists (select * from #a where s = @FileName)
print 'exists'
else
print 'not exists'
drop table #a
-- using xp_fileexists
declare@Path varchar(128) ,
@FileName varchar(128)
select@Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @i int
declare@File varchar(1000)
select @File = @Path + @FileName
exec master..xp_fileexist @File, @i out
if @i = 1
print 'exists'
else
print 'not exists'
Regards,
Balamurugan G
Hello Bala,
I've executed the above script except the scripting object and I could get the "exists" printed out.
Please check whether you are trying to execute the same from one machine but the file exists on the other machine. This is because I connected to one machine but the file is existing on the other machine and then executed your script which printed out "not exists".
Hope this helps.
Thanks
Lucky
April 1, 2008 at 9:14 am
Remember if you run this from your SSMS, it'son the server, it's the server path, NOT your workstation path. You can't get to your workstation path from a server, unless SQL Server is installed on your workstation.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply