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.