How to Store Output of a SQL Server Stored Procedure in a .Txt File

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

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]

How to output stored procedure query results to a text file

Using BCP is quite straight forward, there's no need to try and store the result of a query.

A basic BCP statement you might use within your stored procedure would look like:

declare @bcp nvarchar(max)

set @bcp='bcp "SELECT T0.[DocNum] FROM DBNAME.DBO.OINV T0 WHERE T0.[DocDate] = CAST(CURRENT_TIMESTAMP AS DATE) AND T0.CardName like ''%Name%'' ORDER BY T0.CardName" queryout "c:\file.txt" -c -t, -Sservername -T'

exec master.dbo.xp_cmdshell @bcp, no_output /*optional, remove no_output for debugging */

In the BCP syntax above, replace servername with the DNS name or IP of theSQL Server; -T assumes trusted connection, you can also use -U and -P for specifying username & password.

You can use this directly in a procedure and run it using xp_cmdshell, or run it directly from an Agent Job using CmdExec option.

For debugging you can run it directly in an SSMS query window.

Export multiple stored procedures to text files

Use the Generate Scripts tool in SSMS:

1. Right Click Database in Object Explorer.
2. Tasks -> Generate Scripts.
3. If given the "tutorial" click Next.
4. Select "Select specific database objects" and tick "Stored Procedures". Click Next.
5. Choose export method. Likely here you want "Save as script file" with "one script file per object" selected. Ensure you choose the export location.
6. Click Next and Finish buttons as required.

Storing output of Stored Procedure in file after calling it from Powershell

Do you have the SQL Powershell module installed (sqlps)? If so, then you can use this and pipe the output from the Verbose stream (which contains printed messages from SQL), to your file.

Invoke-Sqlcmd -Query 'DBCC CHECKDB' `
    -ServerInstance '(local)' `
    -Database 'tempdb' `
    -Verbose 4>&1 |
        Out-File c:\temp\test.txt

If that isn't an option, then I think I have spotted the problem in your original code - you wire up the InfoMessage event, but you then proceed to create a brand new SqlConnection. This new SqlConnection doesn't have the event handler on it, and so won't respond to any of the printed messages.

Try replacing

$SqlConnection.add_InfoMessage($handler); 
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;
$SqlConnection = new-Object System.Data.SqlClient.SqlConnection("Server=XXX;DataBase=master;Integrated Security=SSPI")

with

$SqlConnection = new-Object System.Data.SqlClient.SqlConnection("Server=XXX;DataBase=master;Integrated Security=SSPI")
$SqlConnection.add_InfoMessage($handler); 
$SqlConnection.FireInfoMessageEventOnUserErrors = $true;

How to create a file with a stored procedure in T-SQL

You can create text files from SQL Server using BCP utility as follows

declare @cmd varchar(1000)
declare @filepath varchar(500) = 'c:\temp\samplefile.dd'
SET @cmd = 'bcp "select 1 as test" queryout "' + @filepath + '" -c -UTF8 -T -Slocalhost'
EXEC master..xp_cmdshell @cmd

We can even remove the SELECT part from @cmd

declare @cmd varchar(1000)
declare @filepath varchar(500) = 'c:\temp\samplefile.dd'
SET @cmd = 'bcp "" queryout "' + @filepath + '" -c -UTF8 -T -Slocalhost'
EXEC master..xp_cmdshell @cmd

You can check for a more complex usage on SQL tutorial SQL Server BCP Utility to generate script file for stored procedures

I hope it helps

How to save MSSQL query output to .txt or .json file?

As per the original question, which was tagged as MySQL: Your syntax is wrong. As per MySQL Documentation, FROM clause comes after INTO OUTFILE clause. Following should work in MySQL:

SELECT * INTO OUTFILE '/tmp/orders.txt'
FROM A

In order to get Comma-separated values in the text file, you can do the following instead (in MySQL):

SELECT * INTO OUTFILE '/tmp/orders.txt' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM A;

For MS SQL Server: There is no INSERT INTO OUTFILE clause available. Instead, a different solution is proposed using SQL Server Management Studio at: https://stackoverflow.com/a/6354143/2469308

---

Related Topics