Email Notification Help using: sp_Send_dbmail, EXISTS statement, and data query

Question

Email Notification Help using: sp_Send_dbmail, EXISTS statement, and data query

rjjh78

SSCrazy

Points: 2787
More actions
June 13, 2016 at 11:42 am

#317125

Hello,
I would like to use sp_Send_dbmail to send an email notification if specific data exists. If the data does exist, I would like to include the data as part of the email Body. Below is my code, however, I get the error message, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Can someone please help me understand what I am doing wrong?
IF EXISTS (SELECT COUNT(ID) FROM #TempTable)
BEGIN
DECLARE
@iReturnCode int,
@Body_Content varchar(1000),
@Body_Format varchar(4),
@sqlvarchar(1000)
SET @sql = (SELECT * FROM #TempTable)
SET@Body_Content = 'Here I would like to display data from a table:' + '
' + @sql
EXEC@iReturnCode= msdb..sp_send_dbmail
@Profile_name= 'SQL_Server',
@Recipients= 'xx@xxx.com',
@Subject= 'Action Needed!',
@Body= @Body_Content,
@execute_query_database= 'myDB',
@Body_Format = 'HTML'
END

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

the COUNT will always exist, but it might be zero. that's not what you want to test. you want to test if any rows exist

instead just change to if exists (SELECT *

IF EXISTS (SELECT * FROM #TempTable)

BEGIN

DECLARE

@iReturnCode int,

@Body_Content varchar(1000),

@Body_Format varchar(4),

@Sqlvarchar(1000)

SET @Sql = (SELECT * FROM #TempTable)

SET@Body_Content = 'Here I would like to display data from a table:' + '

' + @sql

EXEC@iReturnCode= msdb..sp_send_dbmail

@Profile_name= 'SQL_Server',

@Recipients= 'xx@xxx.com',

@Subject= 'Action Needed!',

@Body= @Body_Content,

@execute_query_database= 'myDB',

@Body_Format = 'HTML'

END

now for the body content, do you want html? a table with columns and rows?

here's a well formed example of getting the content: note that you have to make sure your results have no nulls in them, use ISNULL and CONVERT on all your columns, unless you know the data cannot be null.

Declare @HTMLBody varchar(max),

@TableHead varchar(max),

@TableTail varchar(max)

Set NoCount On;

Set @TableTail = '</table></body></html>';

Set @TableHead = '<html><head>' +

'<style>' +

'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

'</style>' +

'</head>' +

'<body><table cellpadding=0 cellspacing=0 border=0>' +

'<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +

'<td align=center><b>Product</b></td>' +

'<td align=center><b>Provider</b></td>' +

'<td align=center><b>Data Source</b></td>' +

'<td align=center><b>Is Linked?</b></td></tr>';

Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],

name As ,

product As ,

provider As ,

data_source As ,

is_linked As

From sys.servers

Order By is_linked, name

For XML raw('tr'),ELEMENTS)

-- Replace the entity codes and row numbers

Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))

Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')

Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')

Select @HTMLBody = @TableHead + @HTMLBody + @TableTail

-- return output

Select @HTMLBody

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!