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
- Your script should have sufficient rights to perform required access to files (not necessarily local).
- 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.
2 Comments
jeddi · Aug 8, 2018 at 07:27
Hello,
To read a file with [dbo].[ufn_ReadFileAsTable] is working fine. I am just getting ???? when reading from a Russian file
Any suggestion?
mjeddi@guardian.com
Thanks in advance
Serguei Dosyukov · Aug 8, 2018 at 08:37
I think you are running into Unicode issue and by changing to NVARCHAR in above, you should get desired result