How to Delete a File from SQL Server using T-SQL scripts, xp_cmdShell and OLE Automation Procedures

It is sometimes required to reach the outside of SQL Server, I mean the folder structures of the operating systems and create a file, write a file, delete a file or read from file.

Since the MS SQL Server is not build for operating system tasks, it is not easy to manage such operatings on the file system by just using t-sql statements. Fortunately we have some tools for this tasks like Ole Automation Procedures and like xp_cmdshell extended stored procedure.

In this sql server tutorial or t-sql article I will try to summarize the task and how we can enable OLE Automation Procedures, or enable xp_cmdshell extended procedures as a Microsoft SQL Server administrator.
And how we can use ole automation procedures and xp_cmdshell in our t-sql codes or statements in order to manage file system operations on the OS.

I'll deal in this t-sql tutorial with deleting a file using both xp_cmdshell and OLE Automation Procedures.

How to Enable xp_cmdshell on a MS SQL Server Instance

xp_cmdshell has the power to make modifications on the outer world of SQL Server. So this power has to be controlled in the security concepts and be manageable.

In the early versions of Microsoft SQL Server the xp_cmdshell extended procedure was enabled default. This caused some security gaps for SQL Server owners.

Although some administrators do not use xp_cmdshell functionality, it was out of control and can be used in an unsecure way by a sql server developer.

Microsoft now enables SQL Server administrators to enable or disable the xp_cmdshell extenden procedure and releases the SQL Server products with xp_cmdshell is disabled fby default.
So if you think you are capable of taking the security risks and prevent those risks you can enable xp_cmdshell by using the sp_configure stored procedure.

The below t-sql code displays how xp_cmdshell can be used to delete a file named delete-me-file.txt in the root folder of C drive


xp_cmdshell 'del c:\delete-me-file.txt'

Code

Since the xp_cmdshell extended procedure has not been enabled yet, the SQL Server will return the following error and warning message:


Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. 
A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. 
For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Code

Actually the warning message is self explaining in details. We can either enable the sys.xp_cmdshell procedure by using sp_configure or by using the SQL Server Surface Area Configuration Tool.
Of course in order to make configuration changes on the sql server, you have to have the system administrator rights or permissions on the SQL Server instance.

Enable xp_cmdshell using sp_configure


exec sp_configure

go

exec sp_configure 'xp_cmdshell', 1

-- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

go

reconfigure

go

Code

When you run the exec sp_configure t-sql command, you will see a returned list of configuration values for the related SQL Server installation.

The last row of the configuration list is probably displaying values of xp_cmdshell.

config_value and run_value columns are displaying if xp_cmdshell is enabled and if this change is reflected to the running configuration values.

exec sp_configure 'xp_cmdshell', 1 sql command sets the configuration value ("config_value") to 1 which means enable.

And the last t-sql code or command reconfigure sets the running value to enabled in a way reflects the changes to the running server configurations.

How to delete a file using xp_cmdshell extended procedure

After enabling xp_cmdshell it is straight forward for deleting a file from the file system if you have the necessary permissions on the file object for the user who is running the xp_cmdshell procedure.


xp_cmdshell 'del c:\delete-me-file.txt'

Code

How to Enable Ole Automation Procedures on a MS SQL Server Instance

Ole Automation Procedures can be configured just like xp_cmdshell both from Surface Area Configuration Tool and using the sp_configure command in the t-sql batch statements.
Ole Automation Procedures are disabled for new instances by default.

If you want to deal with the FileSystem Object (FSO) and create, delete files or folders, copy files or move files from a folder to an other folder OLE Automation Procedures will be very useful.

Here is a list of OLE Automation Procedures you might probably feel the need to use while working with FSO (File System Object) from SQL Server.

sp_OACreate
sp_OADestroy
sp_OAGetProperty
sp_OASetProperty
sp_OAMethod
sp_OAGetErrorInfo
sp_OAStop

Enable Ole Automation Procedures using sp_configure

If you have one of the following error messages when you run an OLE Automation procedure command, this points to an issue where you can solve by enabling the Ole Automation Procedures on the SQL Server instance.

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1
SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.


exec sp_configure

go

exec sp_configure 'Ole Automation Procedures', 1

-- Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.

go

reconfigure

go

Code

How to delete a file using OLE Automation Procedures

After enabling OLE Automation Procedures the following sql script codes will guide us to delete a file from file system.


DECLARE @Result int

DECLARE @FSO_Token int



EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT

EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'C:\delete-me-file.txt'

EXEC @Result = sp_OADestroy @FSO_Token

Code

With the first line of sql batch code, we are creating a token of the object which we have created as an instance of Scripting.FileSystemObject. This "Scripting.FileSystemObject" programmatic identifier is used as a parameter to the sp_OACreate extended procedure. And the created token is used as a reference parameter to the following sql procedure call codes.

The sp_OAMethod extended stored procedure is used to call a method of the object whose token (created by sp_OACreate) is passed as a parameter. sp_OAMethod calls methods of objects with the help of the following parameters:

The object token created by sp_OACreate
The method name
The method's return value
Parameters that will be used by the object method


EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'C:\delete-me-file.txt'

Code

The following sp_OAMethod is passing the object token @FSO_Token, and the delete command of the FileSystemObject "DeleteFile" method name, and the name of the file which will be deleted as parameters.

The last command sp_OADestroy cleans the memory objects that is not needed any more.