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://blog.sqlauthority.com)
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.
Yeah that was my immediate thought. This seems an overly complicated work around for something already easily doable.
Would it seem reasonable to use this approach for logging various tasks?
In the query you said “Today is a wonderful day” but the file reads “Today is wonderful day”… The article “a” disappeared.. Why?
My editor seems to have fixed the grammar issue. I have reverted it back.
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!
Thanks Priyanka, I know what you are talking about very interesting. I have already put the fix for the same behind the scene.
Why in my SQL Server 2016 I can’t find sp_configure or sp_OAMethod Procedure?
can we write to excel file?
great work. how would one clear that same text file before starting to write something new to it? the above solution keeps appending.
can this code be placed into a stored procecure to be executed only when a certain condition occurs? I’m trying to do that now but am getting syntax errors. I’m not sure if it’s because all of the “GO” statements. Are those required?