How to store output of a SQL Server stored procedure in a .txt file

user_8534089 0
Viewed 40544

I have this stored procedure; I am printing the value of the variable in SSMS. Instead, I want to store this result in a .txt file.

NOTE: I don't want to do it using SSMS options of right clicking on the result and then saving the result as. I want it to be done using any SQL code/built-in function directly in the stored procedure itself.

CREATE PROCEDURE [dbo].[usp_printresulttofile]
AS
BEGIN
    DECLARE @var NVARCHAR(MAX) = ''
    SET @var = 'print this data in txt file'

    PRINT 'Data is : ' + @var   
    /* SQL query here to store result of Print statement in text file */
END

EXEC [dbo].[usp_printresulttofile]

Sharing the updated working SP here so that it might be useful to someone with a similar requirement Thanks @David Browne - Microsoft

ALTER PROCEDURE [dbo].[usp_printresulttofile]
AS
BEGIN
    DECLARE @fileTimeStamp varchar(200) =  convert(varchar,getDate(), 112 )+'_'+ Replace(convert(varchar,getDate(), 114 ),':','')  -- select convert(varchar, getdate(), 121)

    DECLARE @fileExtension varchar(5) = 'txt'
    DECLARE @var NVARCHAR(MAX) = ''
    SET @var = 'print this data in txt file'
    PRINT 'Data is : ' + @var   


  declare @fn varchar(500) = 'c:/log/SP_output_'+@fileTimeStamp+'.'+@fileExtension;
    declare @cmd varchar(8000) = concat('echo ', @var, ' > "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd,  no_output

    set @cmd  = concat('type "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd;

    END
    GO
sql-server
4 Answers

As the comments and other answers indicate, this is not usually a good idea. But here's how to do it anyway, assuming you're a sysadmin on SQL Server. :)

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO

CREATE OR ALTER PROCEDURE [dbo].[usp_printresulttofile]
AS
BEGIN
    DECLARE @var NVARCHAR(MAX) = ''
    SET @var = 'print this data in txt file'
    PRINT 'Data is : ' + @var   

    declare @fn varchar(200) = 'c:\temp\out.txt';

    declare @cmd varchar(8000) = concat('echo ', @var, ' > "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd,  no_output

    set @cmd  = concat('type "', @fn, '"');

    print @cmd 
    exec xp_cmdshell @cmd;


END
go
EXEC [dbo].[usp_printresulttofile]
user_72977000

I might recommend creating a batch file to accomplish this. You can simply do this:

sqlcmd -i ExecSQLProc.sql > Results.txt

Save your EXEC command in a file named ExecSQLProc.sql and then create a batch script with the line above. You can also attach this to the scheduler if needed so that you can regularly generate these scripts. Also you can create a process to output whatever your process produces into an email using only SQL server and see the results this way as well.

Generally I have found it better to use the Operating System to manipulate individual files, if you want to append results you have options:

  1. sqlcmd -i ExecSQLProc.sql >> CumulativeResults.txt
  2. Update your stored process to keep track of your data in the database that is a large container - a blob for instance, and then when you execute your script it will generate a file that has everything in it.
user_31400310
user_104069320

Just remember to be careful with the string going to the text file. If it has quotes / double quotes, etc. it will affect its ability to save. I automatically put double quotes around my outgoing string and it typically helps but if your string is more complicated (i.e. a SQL query with literal comparisons) then you likely will have more work to do.

user_8451450
Related