Logging custom values in txt file from SQL [duplicate]

sql-server

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

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]

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:

sqlcmd -i ExecSQLProc.sql >> CumulativeResults.txt
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.

Use bcp command to copy data into any format. You just need to mention the format in which you want. Like .text

bcp 'select * from table' queryout c:\sql\bcp.txt -c -T

Example and explaination in below link :- https://www.mssqltips.com/sqlservertip/4353/export-sql-server-records-into-individual-text-files/

Logging custom values in txt file from SQL [duplicate]

Related

Recent Posts