CODEWITHSUNDEEP
sqlstored-proceduresfile-exists
Shaded
Shaded

Reputation: 17836

Files in SQL stored procedure

I'm currently tasked with reading some data that stored in a flat file into my database and run reports against it. The one problem I'm running into is checking to see if a file actually exists. Is there a simple function to check if the file exists?

Thanks!

Upvotes: 0

Views: 793

Answers (3)

Raj More
Raj More

Reputation: 48024

You are doing ETL in a stored procedure?!! I don't think you should, just because you can.

I recommend you use use SSIS for this. Doing ETL in Stored Proc or TSQL is not a recommended practice, in fact, it is frequently used as an example of what not to do.

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85056

I believe you can do something like this:

DECLARE @Path varchar(128) ,
 @FileName varchar(128)
 SET @Path = 'C:\'
 SET @FileName = 'FILE_NAME.EXT'

DECLARE @objFSys int
DECLARE @i int
DECLARE @File varchar(1000)

 SET @File = @Path + @FileName
 EXEC sp_OACreate 'Scripting.FileSystemObject', @objFSys out
 EXEC sp_OAMethod @objFSys, 'FileExists', @i out, @File
 IF @i = 1
  PRINT 'file exists'
 ELSE
  PRINT 'file does not exists'
 EXEC sp_OADestroy @objFSys

This article goes over this method and a couple others.

Upvotes: 0

Kyra
Kyra

Reputation: 5407

Just googling I found this at SQL DBA and this at MS SQL tips.

Upvotes: 3

Related Questions