How to Write in Text File from T-SQL in SQL Server? – Interview Question of the Week #236

Question: How to Write in Text File from T-SQL in SQL Server?

Answer: The question above was actually asked in an interview and also was not asked in the same word. The actual conversation was on a very different topic.

Recently I was hired by one of the prominent banks in the USA to help them tune their SQL Server. During the Comprehensive Database Performance Health Check, we encountered a very specific stored procedure which was running inside a critical banking transaction. Inside the code, we found SQL TRANSACTION which was ROLLED BACK when the process was unsuccessful. Now we had to log the performance of the query and hence we wanted to record the timestamp of a certain part of the query which we believed was taking time.

One of the options was to insert the timestamp inside a separate table, however, as we were using transaction it was not possible at all. When the transaction was rolled back, the insert details were also reversed back.

What actually we needed a persistent log to measure the performance of our query and it should not be impacted by SQL Server transaction. It was very clear that we needed to write somewhere outside SQL Server.

Here is the original question which I was asked –

“Is there a way we can write data via T-SQL which is not affected by SQL Server Transactions?”

Of course yes, we can do that with the help of Ole Automation Procedures. Please note that you should only enable Ole Automation Procedures if you really need it otherwise, it is best to keep it disabled. Once you enable Ole Automation Procedures, you can access OS via SQL Server.

First Step – Enable Ole Automation Procedures

-- Step 1: Enable Ole Automation Procedures
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Second Step – Write Text File

-- Step 2: Write Text File
DECLARE @OLE INT
DECLARE @FileID INT
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'D:\data\sqltotext.text', 8, 1
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, 'Today is wonderful day'
EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE
GO

Third Step – Disable Ole Automation Procedures

-- Step 3: Disable Ole Automation Procedures
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO

In our example, we are creating the file in the D:\Data Folder. Once you go there, you can see the file in the folder and also find our text in it.

Let me know if you ever faced such a situation with the transaction.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)