File system operations in SQL Server using OLE Automation

I have been doing some cool automation stuff in SQL Server using OLE Automation objects. This is a series post on OLE automation, If you missed the first post then read it here to know what OLE Automation is and how to convert binary data into file in sql server?

Recently I have created a SQL function to get file properties or attributes, delete a file from file system and read  the file in ASCII text format depending upon the parameter we pass.

Function Name: [dbo].[ufn_fileOperation]
Type: Table valued
Parameters: @filPath = Original File path\shared path, @flg = 1 (properties) DEFAULT, 2 = (properties + Text), 3 = (properties + DELETE)

Get Properties and Text of a file (@flg = 1 and flg = 2)

OLE_AUTOMATION_03

Delete a file (@flg = 3)

OLE_AUTOMATION_04

Note: You must have admin rights or enough privilege on the files or folder to delete the files from file system.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: