File Operations Using OLE Automation in SQL Server

Views: 2.097
Reading Time: 6 minutes

Hello guys,
Good night!

Remember the post I made about Enabling OLE Automation via T-SQL on SQL Server where did I say I would post about some cool functions exemplifying the use of OLE Automation in SQL Server? So this is the post 🙂

In order not to put too many Stored Procedures and Functions here in the post and make it too long, I created a compressed file with the source code of the scripts that are available here and I will just explain the use of each of them.

fncFile_Exists_File
fncFile_Exists_File: Function that returns whether a file exists (1) or not (0).
Usage: SELECT CLR.dbo.fncFile_Exists_File ('C: \ Test.txt')

Source code:

fncLer_FSO_file
fncLer_FSO_file: Function that reads the contents of a file and returns a table where each row of the file is a row in the table.
Usage: SELECT * FROM CLR.dbo.fncLer_FSO_File ('C: \ Test.txt')

Source code:

stpApaga_File_File
stpApaga_File_File: Stored Procedure that physically erases a file.
Usage: EXEC CLR.dbo.stpFFS_FilePage 'C: \ Test.txt'

Source code:

stpCopia_File_File
stpCopia_File_File: Stored Procedure that physically copies a file from one directory to another.
Usage: EXEC CLR.dbo.stpCopy_Files_Copy 'C: \ Test.txt', 'C: \ Files \ New_File.log', 1 - Will overwrite if it already exists (3th parameter)

Source code:

stpWrite_File_File
stpWrite_File_File: Script that writes the contents of a variable or text to a file on disk.
Usage: EXEC CLR.dbo.stpWrite_File ('C: \ Test.txt', 'File Write Test')

Source code:

stpInfo_File_Info
stpInfo_File_Info: Stored Procedure that returns file information, such as type, size, creation date, and modification.
Usage: EXEC CLR.dbo.stpFSOFileInformation ('C: \ Test.txt')

Source code:

stpMove_File_File
stpMove_File_File: Stored Procedure that physically moves a file from one directory to another.
Usage: EXEC CLR.dbo.stpMove_File_File 'C: \ Test.txt', 'C: \ Files \ New_File.log', 1 - Will overwrite if it already exists (3th parameter)

Source code:

See how easy and simple it is? Now you can play with files without disrupting having to create functions and stored procedures using C # and compiling the database CLR (If you want to use CLR for file manipulation instead of OLE Automation, WHAT I RECOMMEND, access the post How to list, read, write, copy and move files with CLR (C #)).

As a toast, I'll pass you one more stored procedure, which has the function of starting from a file path, separating the directory and the file into two distinct variables:

stpServerFileName:

How to use:

That's all, folks!
See you in the next post.