File Operations Using OLE Automation in SQL Server

Views: 2.182
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: Function that returns whether a file exists (1) or not (0).
Use: SELECT CLR.dbo.fncArquivo_Existe_FSO ('C: \ Teste.txt')

Source code:

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.
Utilization: SELECT * FROM CLR.dbo.fncLer_Arquivo_FSO ('C: \ Teste.txt')

Source code:

stpApaga_File_File: Stored Procedure that physically erases a file.
Use: EXEC CLR.dbo.stpApaga_Arquivo_FSO 'C: \ Teste.txt'

Source code:

stpCopia_File_File: Stored Procedure that physically copies a file from one directory to another.
Use: EXEC CLR.dbo.stpCopia_Arquivo_FSO 'C: \ Teste.txt', 'C: \ Files \ Novo_Arquivo.log', 1 - Will overwrite if it already exists (3rd parameter)

Source code:

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

Source code:

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

Source code:

stpMove_File_File: Stored Procedure that physically moves a file from one directory to another.
Use: EXEC CLR.dbo.stpMove_Arquivo_FSO 'C: \ Teste.txt', 'C: \ Files \ Novo_Arquivo.log', 1 - It will be overwritten if it already exists (3rd 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:


How to use:

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