Procedure Writing Text to Text File in SQL Server

Hello to everyone,

In this article, I will give information about the procedure that writes to the text file in SQL Server.

In SQL Server, in some cases you may want to write to the file using SQL Server.

You can easily do this using the procedure below.

First you need to make the necessary adjustments.

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

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

Then let’s open the data folder on the C:\ drive and create the procedure after making the adjustments.

--Establishment of the procedure

CREATE PROCEDURE sp_Write_Text_File
    @FilePath VARCHAR(255),
    @Text VARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @OLE INT;
    DECLARE @FileID INT;

    EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT;

    EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @FilePath, 8, 1;

    EXECUTE sp_OAMethod @FileID, 'WriteLine', NULL, @Text;

    EXECUTE sp_OADestroy @FileID;

    EXECUTE sp_OADestroy @OLE;
END;


--Use of the procedure

EXEC dbo.sp_Write_Text_File 'C:\data\Example.txt', 'Hello';
EXEC dbo.sp_Write_Text_File 'C:\data\Example.txt', 'How are you?';

When you create and run the procedure, you will get a result similar to the one below.

As you can see, text is written to the text file with the help of the procedure.

Good luck to everyone in business and life.