sql - 为什么电子邮件附件中的查询结果小于最大文件大小时会被截断？

Question

我正在使用动态数据透视查询来创建一个表，该表可能有很多行，并通过 msdb.dbo.sp_send_dbmail 将此查询结果发送出去。它在 MSSMS 中给出了正确的结果，但在通过电子邮件发送时，附件被截断。附件的文件大小为 65 KB，最大文件大小设置为 10 MB。

SET QUOTED_IDENTIFIER ON
DECLARE @cols nVARCHAR(MAX),
@DynamicPivotQuery nVARCHAR(max);
select @cols = STUFF((SELECT DISTINCT ','+ QUOTENAME(concatgrpQ) from test.dbo.unpivotedTable FOR XML PATH(''), TYPE).value('.', 'nVARCHAR(MAX)'),1,1,'')

SET @DynamicPivotQuery = 
N'
select *
into test.dbo.pivotedTable 
from 
(
  select *
  from test.dbo.unpivotedTable
) a
pivot
(
  max(Answer)
  for concatgrpQ in ('+@cols+')
) as p'

exec sp_executesql @DynamicPivotQuery
--select * from test.dbo.pivotedTable;
declare @now as NVARCHAR(MAX)
set @now=convert(varchar, getdate(), 1)
declare @stringsubject as nVARCHAR(MAX)
set @stringsubject='Evaluation Export ' +@now+' -Automated DB email'
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Gmail',  
    @recipients = '[e-mail]',  
    @body= 'open file as semi-colon delimited',
    @query = "select * from test.dbo.pivotedTable",  
    @subject = @stringsubject,
    @attach_query_result_as_file = 1,
    @query_no_truncate = 1,
    @query_result_separator =';',
    @query_result_width=32767,
    @query_attachment_filename ='EvalExport.txt'

CREATE TABLE gngiadb.dbo.strcolnames (ColumnNames nvarchar(max))
declare @stringcolumnnames nvarchar(max)
set @stringcolumnnames= '"str1", "str2,"str3",str4", "str5"' +REPLACE(REPLACE(@cols, ']','"'),'[', '"');
INSERT INTO test.dbo.strcolnames
select @stringcolumnnames as 'ColumnNames'
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Gmail',  
    @recipients = '[e-mail]',  
    @query= "select * from test.dbo.strcolnames",
    @body= @stringcolumnnames,
    @subject = 'column names comma-delimited- Automated DB email',
    @attach_query_result_as_file = 1,
    @query_result_header = 1,
    @query_no_truncate = 1,
    @query_result_width=32767,
    @query_attachment_filename ='ColumnNames.txt'

实际通过电子邮件发送的附件被截断为最大 65 KB。

标签： sqlsql-serversql-server-2014sp-send-dbmail