Solved

Sening email in HTML format

Posted on 2013-12-05
7
305 Views
Last Modified: 2013-12-05
Experts,

I am using the following code to send an email and getting the following error and I am displaying two values. Any help is much appreciated.

=============================================================
Msg 116, Level 16, State 1, Procedure Abc_Email, Line 86
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
=============================================================
DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>My Data</H1>' +
    N'<table border="1">' +
    N'<tr><th>Total Values</th></tr>' +  
      N'<tr><th>Loaded Values</th></tr>' +  
    CAST ( ( SELECT td = @TotalData,
                              td = @TotalLoaded                        
                   
                     ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='myemail@abc.com',
    @subject = 'Data Details',
    @body = @tableHTML,
    @body_format = 'HTML' ;
0
Comment
Question by:Tpaul_10
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39699645
>SELECT td = @TotalData,  td = @TotalLoaded
...shouldn't this be...
'SELECT ' + @TotalData + ', ' + @TotalLoaded

Show us how these two variables are populated.
0
 

Author Comment

by:Tpaul_10
ID: 39699712
I have it like following and using those variables in the email

SELECT @TotalData = COUNT(*) FROM mydb.mytable1
SELECT @TotalLoaded  = COUNT(*) FROM mydb.mytable2

Thank You
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 300 total points
ID: 39699718
ok.

(1)  Please explain what you're intent is here, as I don't see a table name.
SELECT td = @TotalData,

(2)  I get the CAST(.. as NVARCHAR(max), but the SELECT .. COUNT(*) will return an int, so you'll have to CAST both of those as NVARCHAR(max) {or whatever char data type} in order to concatenate them with the rest of the string.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Tpaul_10
ID: 39699738
I am trying to send an email with the following details (like an HTML format), would be happy to see another way to do it as well.

Data load details
Total Values : Value of @TotalData
Loaded Values : Value of @TotalLoaded  

If I just have SELECT td = @TotalData, I am getting the email successfully and when I add second variable to the list getting the error I have specified above.

Also, if I give multiple email addresses with ; as the delimiter not working as well.

Thank you, appreciate all your help.
0
 

Author Comment

by:Tpaul_10
ID: 39699745
0
 

Author Comment

by:Tpaul_10
ID: 39700290
I've requested that this question be closed as follows:

Accepted answer: 0 points for Tpaul_10's comment #a39699738

for the following reason:

Thank you
0
 

Author Closing Comment

by:Tpaul_10
ID: 39700291
Thanks
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question