Email Results of Dynamic Pivot in SQL Server

Question

*****EDIT*****

The problem resulted from using a temp table. Therefore this post was marked as a duplicate.

I wrote a query that dynamically selects columns for a Pivot. The query works fine when I execute it like so EXEC sp_executesql @DynamicPivotQuery

The dynamic pivot looks like this (it works):

DECLARE @DynamicPivotQuery AS nvarchar(MAX)
DECLARE @PivotColumnNames AS nvarchar(MAX)
DECLARE @PivotSelectColumnNames AS nvarchar(MAX)

SELECT @PivotColumnNames = UPPER(ISNULL(@PivotColumnNames + ',','') 
                           + QUOTENAME(CSR)) FROM (SELECT DISTINCT CSR FROM #stats) AS CSRs
SELECT @PivotSelectColumnNames = 
                                 UPPER(ISNULL(@PivotSelectColumnNames + ',','')
                                 + 'ISNULL(' + QUOTENAME(CSR) + ', 0) AS '
                                 + QUOTENAME(CSR))
FROM (SELECT DISTINCT CSR FROM #stats) AS CSRs

SET @DynamicPivotQuery = 
N'SELECT Category, ' + @PivotSelectColumnNames + '
FROM #stats 
PIVOT (
    MAX([COUNT])
    FOR CSR IN (' + @PivotColumnNames + ')) as PivotTable'

I want to email these results as part of a SQL Server Agent Job.

When I execute the sp_send_dbmail like below it throws an error:

EXEC msdb.dbo.sp_send_dbmail
@profile_name='donotreply'
,@recipients ='****@****.com'
,@query= @DynamicPivotQuery
,@subject= 'Weekly Stats for New Sales, Winbacks, and TPV by Reps'
,@attach_query_result_as_file=1
,@query_attachment_filename='Customer Service Report.csv'
,@query_result_separator=',' --enforce csv
,@query_result_no_padding=1 --trim
,@query_result_width=32767  --stop wordwrap

Here is the error it throws:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517
Query execution failed: Msg 208, Level 16, State 1, Server CV-SQL2012\CVOSSQL, Line 1
Invalid object name '#stats'.

I've tried executing the email SP with a simpler query in place of @DynamicPivotQuery and it worked.

Thanks for reading

score 1 · Accepted Answer · answered Dec 22 '15 at 20:07

1

You need to execute the sendmail in the same dynamic context that creates the temp table.

That or switch to either a global temp table or a permanent table.

answered Dec 22 '15 at 20:07

Tab Alleman

31,483
7
36
52

Wow I did not think it would be that simple. I changed it to a global temp table and it worked first try. I thought something else was going on. Thanks a bunch. – SQLSuperHero Dec 22 '15 at 21:00

Email Results of Dynamic Pivot in SQL Server

1 Answers1