通过电子邮件显示 SQL 动态透视结果

Question

我正在尝试通过电子邮件发送动态透视结果，但收到错误下图。有什么办法吗？如何解决此问题？我正在使用 SQL 2008 .我试图单独声明@qry但它也没有奏效。

错误信息

DECLARE  @bodyMsg nvarchar(max)
SELECT @bodyMsg = 'Please check the attached file for info on calls between the time ' + convert(varchar,GETDATE()) + ' and ' + convert(varchar,DATEADD(mm, -150, getdate())) + '.'
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
IF OBJECT_ID('tempdb..#Dates') IS NOT NULL
drop table #Dates
SELECT DISTINCT CONVERT(VARCHAR,LOG_TS,106) AS [Date]
INTO #Dates
FROM dbo.CLARITY_LOG
WHERE CAST(LOG_TS as DATE) >=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
and CAST(LOG_TS as DATE) < @mydate
AND DATEPART(HOUR,LOG_TS) BETWEEN 9 AND 23
order by [Date]
DECLARE @cols VARCHAR(1000)
SELECT @cols = COALESCE (@cols + ',[' + [Date] +']','[' + [Date] + ']') FROM #Dates

DECLARE @GrandTotalCol  NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + [Date] +'],0) + ', 'ISNULL([' + [Date]+ '],0) + ')
FROM #Dates 
ORDER BY [Date]
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

 EXEC msdb.dbo.sp_send_dbmail 
 @profile_name ='kss2.mail',
 @recipients = '[e-mail]',
 @body = @bodyMsg,
 @body_format = N'HTML' ,-- varchar(20)
 @query= '
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT *, (''+ @GrandTotalCol + '') AS [Grand Total] INTO #temp_MatchesTotal 
FROM 
(
SELECT Z.CODE AS KOD,C.MESSAGE AS HATA,convert(varchar, LOG_TS, 106) AS [Date]
FROM [Brickstream_DEVMGR].[dbo].[CLARITY_LOG] AS C
INNER JOIN  [Brickstream_DEVMGR].[dbo].[CLARITY_REPORT_OBJ_REL] AS R ON  C.CLARITY_ID=R.CLARITY_ID 
INNER JOIN [Brickstream_DEVMGR].[dbo].[ZONE] AS Z ON R.ZONE_ID=Z.ZONE_ID
WHERE C.MESSAGE LIKE ''%System reboot%'' AND  
CAST(LOG_TS as DATE) >=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
AND CAST(LOG_TS as DATE) < @mydate
AND DATEPART(HOUR,C.LOG_TS) BETWEEN 9 AND 22
GROUP BY Z.CODE,C.LOG_LEVEL,C.MESSAGE,C.LOG_TS
) P
PIVOT
( 
         COUNT(HATA) 
         FOR  [Date] IN('' +@cols + '')
)
 AS pvt
ORDER BY [Grand Total] DESC
--SELECT  * FROM #temp_MatchesTotal
--DROP TABLE #temp_MatchesTotal
'

Answer 1

更改此行

SELECT *, (''+ @GrandTotalCol + '') AS [Grand Total] INTO #temp_MatchesTotal 

为此

SELECT *, ('+ @GrandTotalCol + ') AS [Grand Total] INTO #temp_MatchesTotal 

您有一个双引号，用于中断执行的动态查询sp_send_dbmail

---

DECLARE  @bodyMsg nvarchar(max)
SELECT @bodyMsg = 'Please check the attached file for info on calls between the time ' + convert(varchar,GETDATE()) + ' and ' + convert(varchar,DATEADD(mm, -150, getdate())) + '.'
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
IF OBJECT_ID('tempdb..#Dates') IS NOT NULL
    drop table #Dates
SELECT DISTINCT CONVERT(VARCHAR,LOG_TS,106) AS [Date]
INTO #Dates
FROM dbo.CLARITY_LOG
WHERE 
    CAST(LOG_TS as DATE) >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
    AND CAST(LOG_TS as DATE) < @mydate
    AND DATEPART(HOUR,LOG_TS) BETWEEN 9 AND 23
ORDER BY [Date]
DECLARE @cols VARCHAR(1000)
SELECT @cols = COALESCE (@cols + ',[' + [Date] +']','[' + [Date] + ']') FROM #Dates
DECLARE @GrandTotalCol  NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + [Date] +'],0) + ', 'ISNULL([' + [Date]+ '],0) + ')
FROM #Dates 
ORDER BY [Date]
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)
EXEC msdb.dbo.sp_send_dbmail 
    @profile_name ='kss2.mail',
    @recipients = '[e-mail]',
    @body = @bodyMsg,
    @body_format = N'HTML' ,-- varchar(20)
    @query= '
        DECLARE @mydate DATETIME
        SELECT @mydate = GETDATE()
        SELECT *, ('+ @GrandTotalCol + ') AS [Grand Total] INTO #temp_MatchesTotal 
        FROM 
        (
            SELECT 
                Z.CODE AS KOD,
                C.MESSAGE AS HATA,
                convert(varchar, LOG_TS, 106) AS [Date]
            FROM [Brickstream_DEVMGR].[dbo].[CLARITY_LOG] AS C
            INNER JOIN  [Brickstream_DEVMGR].[dbo].[CLARITY_REPORT_OBJ_REL] AS R ON  C.CLARITY_ID=R.CLARITY_ID 
            INNER JOIN [Brickstream_DEVMGR].[dbo].[ZONE] AS Z ON R.ZONE_ID=Z.ZONE_ID
            WHERE 
                C.MESSAGE LIKE ''%System reboot%'' AND  
                CAST(LOG_TS as DATE) >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AND 
                CAST(LOG_TS as DATE) < @mydate AND 
                DATEPART(HOUR,C.LOG_TS) BETWEEN 9 AND 22
            GROUP BY Z.CODE,C.LOG_LEVEL,C.MESSAGE,C.LOG_TS
        ) P
        PIVOT
        ( 
            COUNT(HATA) 
            FOR [Date] IN(' + @cols + ')
        )
        AS pvt
        ORDER BY [Grand Total] DESC
        --SELECT  * FROM #temp_MatchesTotal
        --DROP TABLE #temp_MatchesTotal
'