Email a report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Email a report

Author

Topic

Mallen
Starting Member

27 Posts

Posted - 2010-10-19 : 17:35:09

I am trying to email a report in sql. I can get an email to run with a simple text body but when I try to add any type of select to the body it won't run.

Here is what I have that will work:
declare @body1 varchar(8000)
set @body1 =
'TS Results Complete.'
EXEC msdb.dbo.sp_send_dbmail @recipients='xxx@xxx.com',
@subject = 'TS Results',
@body = @body1,
@body_format = 'HTML';

This isn't really that useful though. What I want is a procedure something like this:

DECLARE @nonblank TABLE
(
Campaign varchar(25),
[count] int
)
INSERT INTO @nonblank
SELECT
Campaign,
count(*)
FROM Five9CSV
WHERE ISNUMERIC(Five9CSV.LRCS)=1
GROUP BY Campaign

-- Email report

declare @body1 TABLE
([Campaign] varchar(25),
[Count] int,
[Non Blank Added] int
)
INSERT INTO @body1
SELECT
f.Campaign,
count(*),
n.[count]
FROM Five9CSV f INNER JOIN @nonblank n ON f.Campaign = n.Campaign
GROUP BY f.Campaign, n.[count]

EXEC msdb.dbo.sp_send_dbmail @recipients='xxx@xxx.com',
@subject = 'TS Results',
@body = @body1,
@body_format = 'HTML';

This particular run gives me the error 'Must declare the scalar variable="@body1". in the =@body1 line. I have also tried just putting the select statement in the set @body1= area. What am I doing wrong here? Is this even possible?

Thank you

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-10-19 : 20:51:13

You can not send a table variable to a procedure. The body needs to be varchar or nvarchar.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-19 : 21:31:18

Instead of that, have Database Mail execute a query rather than you trying to stuff it into a variable. You can tell it to put the result set into an attachment or just in the body of the email. Check out the query option available with Database Mail in BOL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Mallen
Starting Member

27 Posts

Posted - 2010-10-20 : 17:18:46

Thank you, I looked up the query option and discovered another option that allowed me to include the table into the body. It worked like this:

DECLARE @nonblank TABLE
(
Campaign varchar(25),
[count] int
)
INSERT INTO @nonblank
SELECT
Campaign,
count(*)
FROM Five9CSV
WHERE ISNUMERIC(Five9CSV.LRCS)=1
GROUP BY Campaign

-- Email report

DECLARE @tableHTML NVARCHAR(MAX);

SET @tableHTML =
N'<H1>TS Results Report</H1>' +
N'<table border="1">' +
N'<tr>Campaign</th><th>Count</th><th>Non Blank Count</th>' +
CAST(( SELECT
td = f.Campaign, '',
td= count(*), '',
td= n.[count]
FROM Five9CSV f INNER JOIN @nonblank n ON f.Campaign = n.Campaign
GROUP BY f.Campaign, n.[count]
FOR XML PATH('tr'), TYPE)
AS NVARCHAR(MAX)) +
N'</table>';

EXEC msdb.dbo.sp_send_dbmail @recipients='xxx@xxx.com',
@subject = 'TS Results',
@body = @tableHTML,
@body_format = 'HTML';

I am trying to use this query option in a second email report that I am sending though becaues the table is significantly larger and am having errors.

Here is the email I am trying to send:

EXEC msdb.dbo.sp_send_dbmail
@recipients='xxx@smail.xxx.com',
@query= 'SELECT
Collector_Move_Table.Move_To,
Collector_Move_Table.Queue AS Moved_From,
Collector_Move_Table.Moved_By,
Collector_Move_Table.Status,
Collector_Move_Table.AccountID,
Collector_Move_Table.Balance,
Collector_Move_Table.Package,
Collector_Move_Table.Exception,
Collector_Move_Table.Date_Flagged
FROM Collector_Move_Table
WHERE Collector_Move_Table.Date_Flagged >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
AND Collector_Move_Table.Date_Flagged < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)',
@subject = 'Collector Move Report',
@attach_query_result_as_file = 1;

But it is giving me the error:
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 478
Query execution failed: ?Msg 208, Level 16, State 1, Server SRNET-DB01, Line 1
Invalid object name 'Collector_Move_Table'.

If I just highlight and run the SELECT statement by itself it runs with no error so I don't know why when called in a query it can't find the table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-20 : 17:31:13

Use the 3-part naming convention for the object: DatabaseName.ObjectOwnerTypicallyDbo.TableName.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources