Posted 3/29/2020 — Edited 3/30/2020 to fix a couple of copy and paste errors, and to clarify a couple of items. Thanks Charlie for your suggestions.
I have worked on many projects that involved doing some query work then writing those results to a file. After copying and pasting the typical sp_OA… methods to write to a file too many times, I finally wrapped it all into one stored procedure called writeFile that looks like this.
sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
CREATE PROCEDURE writeFile
(
@fileName NVARCHAR(MAX),
@fileContents NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @OLE INT
DECLARE @FileID INT
DECLARE @outputCursor as CURSOR;
DECLARE @outputLine as NVARCHAR(MAX);
print 'about to write file';
print @fileName;
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile',
@FileID OUT, @fileName, 2, 1
DECLARE @sep char(2);
SET @sep = char(13) + char(10);
SET @outputCursor = CURSOR FOR
WITH splitter_cte AS (
SELECT CAST(CHARINDEX(@sep, @fileContents) as BIGINT) as pos,
CAST(0 as BIGINT) as lastPos
UNION ALL
SELECT CHARINDEX(@sep, @fileContents, pos + 1), pos
FROM splitter_cte
WHERE pos > 0
)
SELECT SUBSTRING(@fileContents, lastPos + 1,
case when pos = 0 then 999999999
else pos - lastPos -1 end + 1) as chunk
FROM splitter_cte
ORDER BY lastPos
OPTION (MAXRECURSION 0);
--DECLARE @loopCounter as BIGINT = 0;
OPEN @outputCursor;
FETCH NEXT FROM @outputCursor INTO @outputLine ;
WHILE @@FETCH_STATUS = 0
BEGIN
--set @loopCounter = @loopCounter + 1;
EXECUTE sp_OAMethod @FileID, 'Write', Null, @outputLine;
--PRINT concat(@loopCounter, ': ', @outputLine);
FETCH NEXT FROM @outputCursor INTO @outputLine ;
END
CLOSE @outputCursor;
DEALLOCATE @outputCursor;
EXECUTE sp_OADestroy @FileID;
END
Then you can call the procedure like this:
-- Replace C:\SQL_DATA\test.txt with your output file. The directory must exist and the account that SQL Server is running as will need permissions to write there.
EXEC writeFile @fileName = 'C:\SQL_DATA\test.txt',
@fileContents = 'this is a test
some more text
go
go
even more';
Be sure to replace my file and path C:\SQL_DATA\test.txt with your output file. The directory must exist and the account that SQL Server is running as will need permissions to write there.
You will notice the sp_configure to enable Ole Automation Procedures. This is needed to use the functions involve in this procedure. This is disabled by default on a new install of SQL Server. There are many secure environments that have requirements like this “Ole Automation Procedures feature must be disabled, unless specifically required and approved.” What that is really saying is that if you don’t need them they should be turned off.
There was a request around being able to pass a query or some SQL to execute into the procedure. I had originally created the procedure to work that way, but I wanted to make it a bit more generic. Perhaps another blog post to save a query output into a varchar max could accompany this post.
This is something that since creating this procedure, it has saved me a ton of time, and I just wanted to share it with the world. Enjoy!
-Steve Stedman
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Thanks for that, Steve (indeed, for all you do and share). Just 3 points of observation.
1) At least in the initial release of the post, I am seeing that the closing END; line is missing at the end of the SP, causing it to fail to compile.
Indeed, since you’re missing the semi-colon on the current last line (EXECUTE sp_OADestroy @FileID), one may wonder if you had still more to follow that perhaps got cut off in a copy/paste. Still, I added the END and now the SP creation did execute.
2) But then I hit this error, as others may:
Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1 [Batch Start Line 0]
sp_configure ‘Ole Automation Procedures’, 1because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.
Ah, I had never enabled that (nor advanced options), on the machine where I was testing this. I did some digging and found this recommended solution, which worked easily for me:
sp_configure ‘show advanced options’, 1
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1
GO
RECONFIGURE;
GO
You or others may want to speak up on the pros/cons of enabling that, for any who may have been interested in your SP but also had not previously enabled these options. I was ok doing it to get this working, on a dev machine of mine.
3) Even then, though the command would run, I found that the file named (C:\SQL_DATA\test.txt) was not created–and yet, no error was returned. I suspected it could be either a) a permissions issue (though I ran this from SSMS, logged in via windows auth with my main admin user) or b) a need for the named folder to exist first.
I killed two birds with one stone, creating that C:\SQL_DATA folder (in Windows Explorer, again while logged in as the same user I’d used to login to SSMS), and now the SP ran and the file was created. Sweet.
4) Finally, though, I find that it does indeed just write the quoted string to the file. How would we change it to instead allow us to name something to execute (an sp call, or sql ddl/dml), whose RESULTS (if any) are then written to that file?
Or would you feel that that can be handled other ways?
Was it that you really only wanted this to be write literally just some string to the file? perhaps when you are on a server working remotely in SSMS, such that you couldn’t “just open an editor and paste it”, as someone might wonder on first glance?
I don’t share/ask these as complaints, but as observations to help others. If you may consider tweaking the post to address them, that’s great but totally your call. I realize you may feel they are the sort of things that folks reading your blog should be able to resolve on their own. :-) I just shared/asked them since I hit them, in case others may also.
Of course the real value here is what you DID share, for what it DOES do already, and again for that, thanks.
Thanks for the feedback Charlie. I have updated the post a bit based on your suggestions. I appreciate it.
Have a great day.
-Steve Stedman
Here is the post to take the results of a query and to write it to a string: https://stevestedman.com/2020/03/saving-query-output-to-a-string/
-Steve Stedman
Steve, thanks very much for attending to my previous observations and updating the post. And thanks for the subsequent one on getting output saved to a string.
To tie the two posts together, I’ll note that one could then do the following (to write the output from that other post using the SP from this one):
EXEC writeFile @fileName = ‘C:\SQL_DATA\test.txt’,
@fileContents = @myOutputString;
That may seem obvious, or indeed if you or anyone else may propose a better way, feel free. I don’t live in the world of creating SPs, or even using more than a limited set of well-defined/well-worn ones.
I’m more of a troubleshooter who leverages tools (and helps others do so), and this is a nice one to add to the toolbelt. :-)