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.

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

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.

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

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

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
How to Remove Quotes of QUOTENAME? – Interview Question of the Week #235
Next Post
How to Skip Top N Rows in SQL Server? – Interview Question of the Week #237

Related Posts

9 Comments. Leave new

  • Write to a table variable which will retain its values after the transaction rolls back, then insert the table variable rows into a real table you can query at your leisure.
    No need to bring the file system into it.

    Reply
    • Yeah that was my immediate thought. This seems an overly complicated work around for something already easily doable.

      Reply
  • Would it seem reasonable to use this approach for logging various tasks?

    Reply
  • In the query you said “Today is a wonderful day” but the file reads “Today is wonderful day”… The article “a” disappeared.. Why?

    Reply
  • Hi Pinal,

    I just encountered a very strange/interesting “feature” on this page. If I double click on the code mentioned in First Step, Second Step or Third Step, they turn all in black fonts (not in SQL style text code color) and I can delete the texts, although, of course, they are not saved! so if I refresh the page again, I can see the original post. But still worth checking the page code behind it in case someone finds it how to even modify it!

    Reply
    • Thanks Priyanka, I know what you are talking about very interesting. I have already put the fix for the same behind the scene.

      Reply
  • Why in my SQL Server 2016 I can’t find sp_configure or sp_OAMethod Procedure?
    Thank You

    Reply
  • can we write to excel file?

    Reply

Leave a Reply

Menu