SP_SEND_DBMAIL存储过程问题

Question

我即将完成查询，该查询旨在通过特定软件处理订单时犯错的人每日发送电子邮件更新。数据是从CTE中的表中提取的。查询运行和电子邮件发送，如果我将@exceloutput变量设置为follwing：

SET @ExcelOutput = ('SELECT [Rep Name], [Temp Rep Number], [Error Code], [Account Number], [Report Date] FROM ##TempEmailTest')

但是，如果我添加一个Were子句，我需要添加以使逻辑正常工作，则查询会引发以下错误：

msg 22050，16级，状态1，第7行无法使用错误编号-2147467259初始化SQLCMD库。

SET @ExcelOutput = ('SELECT [Rep Name], [Temp Rep Number], [Error Code], [Account Number], [Report Date] FROM ##TempEmailTest WHERE [Temp Rep Number] >= @Loop')

这是完整的查询：

IF OBJECT_ID('tempdb..##TempEmailTest') IS NOT NULL
BEGIN 
    DROP TABLE ##TempEmailTest
END
USE MyDataBase
GO
WITH CTE AS
(SELECT 
    a.[Temp Rep Number],
    a.[Rep Name],
    a.[Error Code],
    a.[Account Number],
    a.[Report Date],
    a.[Test Email]
FROM
(SELECT 
    DENSE_RANK() OVER(ORDER BY org.[Rep Name]) AS [Temp Rep Number],
    org.[Rep Name],
    tab.[Error Code],
    tab.[Account Number],
    tab.[Report Date],
    '[e-mail]' AS [Test Email]
FROM tbl_mytable org
INNER JOIN tbl_mytable1 tab ON org.[Rep Name] = tab.[Rep Name]
WHERE tab.[Total Errors] <> 0
AND tab.[Report Date] = CONVERT(VARCHAR(8),GETDATE(),1)) AS a) 
SELECT
    [Temp Rep Number], 
    [Rep Name],
    [Error Code],
    [Account Number],
    [Report Date],
    [Test Email]
INTO ##TempEmailTest
FROM CTE
DECLARE @Loop int
SET @Loop = 1
DECLARE @LoopEnd int
SET @LoopEnd = (SELECT MAX([Temp Rep Number]) AS [Temp Row Number] FROM ##TempEmailTest) 
WHILE @Loop <= @LoopEnd
BEGIN 
DECLARE @TempRepNumber int
DECLARE @RepName nvarchar(MAX)
DECLARE @RepEmail nvarchar(MAX)
DECLARE @AccountNumber varchar(MAX)
DECLARE @ErrorCode varchar(Max)
DECLARE @ExcelOutput varchar(2048)

SELECT
    @TempRepNumber = [Temp Rep Number],
    @RepName = [Rep Name],
    @Accountnumber = [Account Number],  
    @ErrorCode = [Error Code]
FROM ##TempEmailTest
WHERE @Loop = [Temp Rep Number]
SET @RepEmail =  (SELECT 
                 Email.[Test Email]
                 FROM
                 (SELECT 
                 ROW_NUMBER() OVER(ORDER BY [Test Email]) AS [Email Row],
                 [Test Email] 
                 FROM ##TempEmailTest
                 WHERE [Temp Rep Number] = @Loop) AS Email
                 WHERE Email.[Email Row] = 1)   

SET @ExcelOutput = ('SELECT [Rep Name], [Temp Rep Number], [Error Code], [Account Number], [Report Date] FROM ##TempEmailTest WHERE [Temp Rep Number] = @Loop')
exec msdb.dbo.sp_send_dbmail
@profile_name = 'test_profile',
@recipients = @RepEmail,
@from_address = '[e-mail]',
@subject = 'test',
@body = 'test',
@query = @ExcelOutput,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'test.csv',
@query_result_separator=',', --enforce csv
@query_result_no_padding=1, --trim
@query_result_width=32767  --stop wordwrap
SET @Loop = @Loop + 1
END

Answer 1

尝试此https://www.sqlservercentral.com/forums/topic723716-338-1.aspx

SET @ExcelOutput = 'SET NOCOUNT ON SELECT [Rep Name], [Temp Rep Number], [Error Code], [Account Number], [Report Date] FROM ##TempEmailTest WHERE [Temp Rep Number]  >'  + cast(@Loop as varchar(3))