# Stored procedure to write in a file

The following stored procedure allows me to write in a file stored on my SQL Server:

CREATE PROCEDURE [dbo].[spWriteToFile]
(
@PATH_TO_FILE nvarchar(MAX),
@TEXT_TO_WRITE nvarchar(MAX)
)
AS
BEGIN
DECLARE @OLE int
DECLARE @FileID int

EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @PATH_TO_FILE, 8, 1
EXECUTE sp_OAMethod @FileID, 'WriteLine', NULL, @TEXT_TO_WRITE
END


In order to grant all the needed permissions, I had to execute the following query:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO


Here is an exemple to execute the stored procedure:

-- NB: The folder "D:\test\" has to be created before
EXEC dbo.spWriteToFile N'D:\test\t1.txt', N'Hello World !'


My problem is that I have to run this stored procedure a lot and therefore I have some performance issues. Is there something I can improve in my implementation to improve performance ? Is there another way to write in a file using SQL Server that could be faster ? If not, I think I'll have to try to call it less often.

NB: I'm using SQL Server Express Edition 2014 in case it could be relevant to my issue.

• Can you elaborate on the performance issue? Is it compounding (getting worse)? – scsimon Jul 6 '17 at 13:52
• I'm using this to insert some logs in a file. For example, if I decide to update 10 articles in my db, it is possible that this procedure will be called 100 times to log what fields changed, etc. Since it is opening and closing that file everytime, I have performance issues during that update. It is not getting worse but it is already too slow imo. I don't think I can improve this implementation. I should probably use it in another way. I was thinking about adding those lines in a table and have a script that runs every X minutes to add those lines in the file instead of adding them everytime. – Aleph0 Jul 6 '17 at 16:34
• Thus the update should run faster but the lines I have to insert won't be directly added to the file. – Aleph0 Jul 6 '17 at 16:37
• Oh i see... well if this is for logging changes then i would definitely log it in the DB itself with a trigger (on update, delete, insert, etc) or use versioning. I've never seen anyone log it to a flat file like that. Also,you could back up your transaction logs – scsimon Jul 6 '17 at 16:37
• Since I'm working with Express Edition, a lot of things such as mirroring, log shipping, ... are a lot harder to implement. I have to find some workarounds to do the job but I don't always have good ideas and I end up trying things like that. – Aleph0 Jul 6 '17 at 16:44

As discussed in comments with @scsimon and @Milney, I've created the following table:

CREATE TABLE [dbo].[file_write]
(
[ID] bigint NOT NULL IDENTITY(1,1) ,
[PATH_FILE] nvarchar(500) NOT NULL ,
[TEXT_FILE] nvarchar(500) NOT NULL ,
[DATE_WRITE] datetime NOT NULL ,
PRIMARY KEY ([ID])
)


After that, i've written a stored procedure in order to insert into this table using only PATH_FILE and TEXT_FILE as parameters. This procedure replaces the one I had written in my question.

CREATE PROCEDURE [dbo].[insertFileWrite]
(
@PATH_FILE nvarchar(500),
@TEXT_FILE nvarchar(500)
)
AS
BEGIN
DECLARE @NOW datetime = CURRENT_TIMESTAMP

INSERT INTO dbo.file_write(PATH_FILE, TEXT_FILE, DATE_WRITE)
VALUES (@PATH_FILE , @TEXT_FILE , @NOW)
END


Finally, i've written a second stored procedure in order to write the lines that are in my table. This stored procedure is executed every 5 minutes by a scheduled task on the server.

CREATE PROCEDURE [dbo].[writeAllIntoFile]
AS
BEGIN
DECLARE @PATH_FILE nvarchar(500)
DECLARE @PATH_FILE_PREV nvarchar(500)
DECLARE @TEXT_FILE nvarchar(500)
DECLARE @ID bigint

DECLARE @OLE int
DECLARE @FileID int

BEGIN TRANSACTION t_writeAllIntoFile
BEGIN TRY

DECLARE c_writeFile CURSOR FOR
SELECT PATH_FILE,
TEXT_FILE,
ID
FROM dbo.file_write
ORDER BY PATH_FILE ASC,
DATE_WRITE ASC

OPEN c_writeFile
FETCH NEXT FROM c_writeFile INTO @PATH_FILE, @TEXT_FILE, @ID

SET @PATH_FILE_PREV = @PATH_FILE

EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @PATH_FILE, 8, 1

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@PATH_FILE <> @PATH_FILE_PREV)
BEGIN

EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @PATH_FILE, 8, 1
END

EXECUTE sp_OAMethod @FileID, 'WriteLine', NULL, @TEXT_FILE
DELETE FROM dbo.file_write WHERE ID = @ID

SET @PATH_FILE_PREV = @PATH_FILE
FETCH NEXT FROM c_writeFile INTO @PATH_FILE, @TEXT_FILE, @ID
END

CLOSE c_writeFile
DEALLOCATE c_writeFile

COMMIT TRANSACTION t_writeAllIntoFile
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION t_writeAllIntoFile

IF (SELECT CURSOR_STATUS('global','c_writeFile')) >= -1
BEGIN
IF (SELECT CURSOR_STATUS('global','c_writeFile')) > -1
BEGIN
CLOSE c_writeFile
END

DEALLOCATE c_writeFile
END

RETURN -999
END CATCH
END


Whis this solution, I don't have to open and close my file everytime I want to write one line into it. Since writing is now running on its side, performances have been a lot improved.