use master
go
/*
        Usage of the stored procedures:

        declare @retcode int , @filehandle int
        execute @filehandle = sp_oafileop;1 'C:\temp\test.txt' , '** 1st line'
        if @filehandle < 0 goto ErrorHandler
        execute @retcode    = sp_oafileop;2 @filehandle , '** 2nd line'
        if @filehandle < 0 goto ErrorHandler
        execute @retcode    = sp_oafileop;3 @filehandle , '** Last line'
        if @filehandle < 0 goto ErrorHandler
*/
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_OAfileop'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_OAfileop
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_OAfileop'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_OAfileop >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_OAfileop >>>'
END
go
CREATE PROCEDURE sp_OAfileop;1 (
        @FileName varchar(255),
        @Line varchar(8000) = ''
)
-- WITH ENCRYPTION
AS
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    10 March 2001                                           */
/*      Description:    These stored procedures provide wrappers for creating   */
/*                      files, inserting lines & deleting the files. These use  */
/*                      the Scripting.FileSystemObject primitives via the T-SQL */
/*                      OLE automation system stored procedures.                */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
DECLARE @RetCode int , @FileSystem int , @FileHandle int
EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT
IF @@ERROR|@RetCode > 0 Or @FileSystem < 0 GOTO ErrorHandler

EXECUTE @RetCode = sp_OAMethod @FileSystem , 'CreateTextFile' , @FileHandle OUTPUT , @FileName
IF @@ERROR|@RetCode > 0 Or @FileHandle < 0 GOTO ErrorHandler

IF @Line > '' EXECUTE @RetCode = sp_OAMethod @FileHandle , 'WriteLine' , NULL , @Line
IF @@ERROR|@RetCode > 0 GOTO ErrorHandler
RETURN( @FileHandle )

ErrorHandler:
        EXECUTE sp_DisplayOAErrorInfo @FileSystem , @RetCode
        RETURN( -1 )
GO
CREATE PROCEDURE sp_OAfileop;2 (
        @FileHandle int,
        @Line varchar(8000) = NULL
)
-- WITH ENCRYPTION
AS
DECLARE @RetCode int
IF @FileHandle > 0 And @Line > ''
BEGIN
        EXECUTE @RetCode = sp_OAMethod @FileHandle , 'WriteLine' , NULL , @Line
        IF @@ERROR|@RetCode > 0 GOTO ErrorHandler
END
RETURN( 0 )

ErrorHandler:
        EXECUTE sp_DisplayOAErrorInfo @FileHandle , @RetCode
        RETURN( -1 )
GO
CREATE PROCEDURE sp_OAfileop;3 (
        @FileHandle int,
        @Line varchar(8000) = NULL
)
-- WITH ENCRYPTION
AS
DECLARE @RetCode int
IF @FileHandle > 0
BEGIN
        IF @Line > ''
                EXECUTE @RetCode = sp_OAMethod @FileHandle , 'WriteLine' , NULL , @Line
        IF @@ERROR|@RetCode > 0 GOTO ErrorHandler

        EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close'
        IF @@ERROR|@RetCode > 0 GOTO ErrorHandler

        EXECUTE sp_OADestroy @FileHandle
END
RETURN( 0 )

ErrorHandler:
        EXECUTE sp_DisplayOAErrorInfo @FileHandle , @RetCode
        RETURN( -1 )
GO

This page was last updated on May 01, 2006 04:28 PM.