TIP: How to do a FileExists in SQLServer2000 1

[hbaake]

Hi!

I noted some discussion about how to determine if a file exists (using a undocumented stored procedure that was available in SQLServer 7, but not in 2000).
Below is a way to handle this, using the windows Scripting object (yes... only valid on a windows platform)

Anyway, here it is; have fun...
====================================================
CREATE PROCEDURE utlFileExists
@sFullFileName varchar(1000)
AS

--
-- Use the windows scripting filesystem
-- object to determine if a file exists...
-- (note: there's some relaxed error handling in here)
-- Hugo Baake
--
-- Return 1 if a file exists, otherwise 0
--
declare @oFS as int
declare @Res as int

exec @Res = sp_OACreate 'Scripting.FileSystemObject', @oFS OUT
if @Res <> 0
begin
RAISERROR ('utlFileExists: error instantiating Scripting.FileSystemObject',1,16)
return
end
exec @Res = sp_OAMethod @oFS, 'FileExists', NULL, @sFullFileName
exec sp_OADestroy @oFS
return @Res
======================================================

 

[hbaake]

Oops! even this simple and still an error... sorry; here's the update:

CREATE PROCEDURE utlFileExists
@sFullFileName varchar(1000)
AS

--
-- Use the windows scripting filesystem
-- object to determine if a file exists...
-- (note: there's some relaxed error handling in here)
-- Hugo Baake
--
-- Return 1 if a file exists, otherwise 0
--
declare @oFS as int
declare @Res as int
declare @iExist as int

exec @Res = sp_OACreate 'Scripting.FileSystemObject', @oFS OUT
if @Res <> 0
begin
RAISERROR ('utlFileExists: error instantiating Scripting.FileSystemObject',1,16)
return
end
exec @Res = sp_OAMethod @oFS, 'FileExists', @iExist output, @sFullFileName
exec sp_OADestroy @oFS

return @iExist

 

[tlbroadbent]

Thanks for the TIP. There is another method of checking for a file. You can use the undocumented extended stored procedure xp_fileexist.

Example: As you can see, this is very simple
[tt]
-- Declare variable to capture result
declare @exists int

-- xp_fileexist takes two parameters
-- 1) the file name
-- 2) The output parameter to return
-- 1 if the file exists and 0 if not
exec xp_fileexist 'c:\autoexec.bat', @exists output

-- Print the result
print @exists[/tt]

If you don't provide an output parameter SQL Server returns a result set like the following.
[tt]
File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
0 0 1

(1 row(s) affected)[/tt] Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[MadhanaGopal]

Thanx for this nice tip.I read one article from swynk.com
we check the file existing thru sp_MSexists_file which is in master db.
Instead of using Extended SP this one is better ,I think.

------------------------------------------------------------
This stored procedure can be used to determine whether the particular
file exists in the particular directory or not.

Syntax

sp_MSexists_file full_path, filename


where

full_path - is the full path to the file. full_path is nvarchar(512).
filename - is the file name. filename is nvarchar(255).

To check if file textcopy.exe exists in the C:\MSSQL7\BINN\ directory
(path by default), run:

DECLARE @retcode int
EXEC @retcode = sp_MSexists_file 'C:\MSSQL7\BINN\', 'textcopy.exe'
IF @retcode = 1
PRINT 'File Exist'
ELSE
PRINT 'File does not Exist'
---------------------------------------------------------- P.Madhana Gopal,
Principal Software Engineer,
LCube Innovative Solns. Pvt. Ltd.,
India.