Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

Delete files using Ole Automation Procedures in SQL Server

Dec 7 2011 2:38AM by Paresh Prajapati   

To delete the files using Ole Automation Procedures in SQL Server we need to first enable 'Ole Automation Procedures' using sp_configure as following,

exec sp_configure 'Ole Automation Procedures', 1

After enabling it we can delete the files with Ole Automation Procedures which using FSO (File System Object) from SQL Server and we need to pass method name 'DeleteFile' in the tsql script. You can delete all the files or specific files as well.

DECLARE @Result int
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'D:\TestFolder\*.txt'
EXEC @Result = sp_OADestroy @FSO_Token
Read More..   [49 clicks]

Published under: SQL Server Tips ·  ·  ·  · 

Paresh Prajapati
6 · 23% · 7533



  • OLE Automation is a bad idea, but has been retained for backwards compatibility only.

    An example of the dangers of OLE Automation, from Books Online topic "OLE Automation Objects in Transact-SQL", SQL Server 2008 R2

    The OLE Automation stored procedures enable Transact-SQL batches to reference SQL-DMO objects and custom OLE Automation objects, such as objects that expose the IDispatch interface. A custom in-process OLE server that is created by using Microsoft Visual Basic must have an error handler (specified with the On Error GoTo statement) for the ClassInitialize and ClassTerminate subroutines. Unhandled errors in the ClassInitialize and ClassTerminate subroutines can cause unpredictable errors, such as an access violation in an instance of the Database Engine. Error handlers for other subroutines are also recommended.

    I'm not a fan of deploying anything that can cause an access violation or that runs within the context of the SQL Server process.

    A much better option is to create a SQLCLR stored procedure (or use someone elses) to gain access to the file system.

    Here's an example: (Visual Basic SQLCLR stored procedure)

    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server
        Partial Public Class StoredProcedures
            <Microsoft.SqlServer.Server.SqlProcedure()> _
            Public Shared Function usp_DeleteFileScalar(ByVal FileName As SqlString, ByRef ErrorMessage As SqlString) As SqlInt32
                Dim intReturn As SqlInt32 = 1
                ErrorMessage = SqlString.Null
                Catch ex As Exception
                    ErrorMessage = "An Exception of Type [" & ex.GetType.ToString & "] occurred with the Message [" & ex.Message & "]"
                    intReturn = 0
                End Try
                Return intReturn
            End Function
        End Class

    Compile this and deploy it to your database server (Visual Studio does a great job of doing this), even creating the deployment files for examination. See the Build and Deploy output from Visual Studio for the location of the deployment files. Note: You'll have to mark the database as TRUSTWORTHY in order to execute this stored procedure.

    This function can then be called:

    DECLARE @fn as [nvarchar](260) = N'C:\foo\bar\myfile.file'
    DECLARE @er as [nvarchar](max) = N''
    DECLARE @return as [int]
    EXEC @return = [UTILITIES].[generic].[DeleteFileScalar] @FileName = @fn, @ErrorMessage = @er OUTPUT

    If the delete operating throws an error, @return = 0. If the delete operation succeeds, @return = 1. Any error messages will be return in @er

    commented on Dec 8 2011 8:25AM
    Marc Jellinek
    96 · 2% · 586

Your Comment

Sign Up or Login to post a comment.

"Delete files using Ole Automation Procedures in SQL Server" rated 5 out of 5 by 8 readers
Delete files using Ole Automation Procedures in SQL Server , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]