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.

Procedure Writing Text to Text File in SQL Server

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.

324 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!