如何将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

推荐文章

踏实的登山鞋 · 通过wm_concat 函数报错：ora06502-character string buffer to small浅谈wm_concat、Listagg、xmlagg函数的使用和结果不确定性-阿里云

4 天前

小胡子的匕首 · mysql cast(as bigint ) - CSDN文库

3 天前

逼格高的课本 · 【Redis】Redis 有序集合 Zset 操作 ( 简介 | 查询操作 | 增加操作 | 删除操作 | 修改操作 )-腾讯云开发者社区-腾讯云

8 月前

怕老婆的铅笔 · 使用Python从音频文件中过滤出特定频率的音频。

9 月前

欢乐的灭火器 · BeginEdit,CancelEdit和EndEdit方法_oncellbeginedit canceledit_stevenjin的博客-CSDN博客

1 年前

礼貌的火腿肠 · Impala高性能探秘之HDFS数据访问 - 墨天轮

1 年前

帅呆的炒粉 · 在pycharm中配置opencv+anaconda_opencv在pycharm_fly_Xiaoma的博客-CSDN博客

1 年前

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

2 个回答