Wednesday, 20 August 2014

Writing to Text file using Stored Procedure Sql Server Database


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE WriteToFile
    @File VARCHAR(2000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @OLE    int
    DECLARE @FileID int

    EXEC sp_OACreate 'Scripting.FileSystemObject',@OLE OUT
    EXEC sp_OAMethod @OLE, 'OpenTextFile' ,@FileID OUT, @File , 8 , 1
    EXEC sp_OAMethod @FileID , 'WriteLine', Null, 'Hello'
    EXEC sp_OADestroy @FileID
    EXEC sp_OADestroy @OLE
   
END
GO


--EXEC MDM.dbo.WriteToFile 'C:\DataUpload\AccessDB\hello.txt'



sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO

sp_configure 'Ole Automation Procedures', 1 ;
GO
RECONFIGURE
GO

No comments:

Post a Comment