File management from inside SQL code

Writing a log, dumping data, looking for file to be used in Bulk insert…

There are many situations when you would want to have access to files from inside your SQL code on Microsoft SQL Server.

Did you know that you actually can do this? No? Check below for code snippets to perform various operations on files. It is presented in form of the functions but you are actually not limited to that

Prerequisites and assumptions

1. Your script should have sufficient rights to perform required access to files (not necessarily local).
2. Scripting.FileSystemObject should be present at your SQL Server location and accessible.

Check if file exists

CREATE FUNCTION [dbo].[ufn_IsFileExists] ( 
@FilePath VARCHAR(255) 
) 
RETURNS INT 
AS 
BEGIN 
DECLARE 
  @objFileSystem int, 
  @hr int, 
  @i int 
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem out 
IF @HR = 0 
BEGIN 
  EXEC sp_OAMethod @objFileSystem, 'FileExists', @i out, @FilePath 
  EXEC sp_OADestroy @objFileSystem 
END 
ELSE 
BEGIN 
  SET @i = -1 
END 
RETURN @i 
END

Write string into file

CREATE FUNCTION [dbo].[ufn_WriteStringToFile] ( 
  @CreateFile int, 
  @FilePath varchar(500), 
  @String varchar(4000) ) 
RETURNS varchar(200) 
AS 
BEGIN 
DECLARE 
  @objFileSystem int, 
  @objTextStream int, 
  @objErrorObject int, 
  @strErrorMsg varchar(1000), 
  @Command varchar(1000), 
  @HR int, 
  @fileAndPath varchar(80) 
SET @strErrorMsg = 'opening the File System Object' 
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT 
IF @HR = 0 
BEGIN 
  SET @objErrorObject = @objFileSystem 
  IF (@CreateFile = 1) 
  BEGIN 
SET @strErrorMsg= 'Creating file "' + @FilePath + '"' 
EXEC @hr = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @FilePath, 2, True 
  END 
  ELSE 
  BEGIN 
  SET @strErrorMsg = 'Opening file "' + @FilePath + '"' 
  EXEC @hr = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @FilePath, 8, True 
  END 
END 
IF @HR = 0 
BEGIN 
  SET @objErrorObject = @objTextStream 
  SET @strErrorMsg = 'Writing to the file "' + @FilePath + '"' 
  EXEC @hr = sp_OAMethod @objTextStream, 'WriteLine', Null, @String 
END 
IF @HR=0 
BEGIN 
  SET @objErrorObject = @objTextStream 
  SET @strErrorMsg = 'Closing the file "' + @FileAndPath + '"' 
  EXEC @hr = sp_OAMethod @objTextStream, 'Close' 
END 
IF @HR <> 0 
BEGIN 
  DECLARE 
  @Source varchar(255), 
  @Description varchar(255), 
  @Helpfile varchar(255), 
  @HelpID int 
EXEC sp_OAGetErrorInfo @objErrorObject, @Source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT 
  SET @strErrorMsg = 'Error: ' + coalesce(@strErrorMsg, 'Unknown') + ', ' + coalesce(@Description, '') 
END 
EXEC sp_OADestroy @objTextStream 
RETURN @strErrorMsg 
END

Read File As Table

CREATE FUNCTION [dbo].[ufn_ReadFileAsTable] ( 
@FilePath VARCHAR(255) 
) 
RETURNS @File TABLE ([LineNo] int identity(1,1), [Line] varchar(8000)) 
AS 
BEGIN 
DECLARE 
@objFileSystem int, 
@objTextStream int, 
@objErrorObject int, 
@strErrorMsg varchar(1000), 
  @hr int, 
  @String VARCHAR(8000), 
@YesOrNo INT 
SET @strErrorMsg = 'opening the File System Object' 
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT 
IF @HR=0 
BEGIN 
SET @objErrorObject = @objFileSystem 
SET @strErrorMsg = 'Opening file "' + @FilePath + '"' 
--Open for reading, FormatASCII 
EXEC @HR = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @FilePath, 1, False, 0 
END 
WHILE @HR = 0 
BEGIN 
  IF @HR=0 
  BEGIN 
SET @objErrorObject = @objTextStream 
SET @strErrorMsg = 'Check if there is more to read in "' + @FilePath + '"' 
EXEC @HR = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT 
IF @YesOrNo <> 0 BREAK 
  END 
  IF @HR=0 
  BEGIN 
SET @objErrorObject = @objTextStream 
SET @strErrorMsg = 'Reading from the output file "' + @FilePath + '"' 
EXEC @HR = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT 
    INSERT INTO @file(line) SELECT @String 
  END 
END 
IF @HR=0 
BEGIN 
SET @objErrorObject = @objTextStream 
SET @strErrorMsg = 'Closing the output file "' + @FilePath + '"' 
EXEC @HR = sp_OAMethod @objTextStream, 'Close' 
END 
IF @hr <> 0 
BEGIN 
  DECLARE 
@Source varchar(255), 
@Description varchar(255), 
@Helpfile varchar(255), 
@HelpID int 
EXEC sp_OAGetErrorInfo @objErrorObject, @Source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT 
SET @strErrorMsg = 'Error: ' + coalesce(@strErrorMsg, 'Unknown') + ', ' + coalesce(@Description, '') 
INSERT INTO @File(line) select @strErrorMsg 
END 
EXEC sp_OADestroy @objTextStream 
-- Fill the table variable with the rows for your result set 
RETURN 
END

Enjoy.