如何将SQL Server存储过程的输出存储在.txt文件中

CREATE PROCEDURE [dbo].[usp_printresulttofile]
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 */
EXEC [dbo].[usp_printresulttofile]

ALTER PROCEDURE [dbo].[usp_printresulttofile]
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;
    GO

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
CREATE OR ALTER PROCEDURE [dbo].[usp_printresulttofile]
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

如何将SQL Server存储过程的输出存储在.txt文件中

2 个回答