Creating a sp_send_dbmail - 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

General SQL Server Forums

New to SQL Server Programming

Creating a sp_send_dbmail

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-03-30 : 09:45:00

I have created this store procedure, but I am getting an error. Not sure what I am doing wrong. This is the warning I am getting. Can someone help me please? Thanks.

Msg 116, Level 16, State 1, Procedure p_Gender_Test, Line 44
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

create PROCEDURE [dbo].[p_Gender_Test]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @CNT INT
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET @CNT = (Select Count(*)
FROM impact.dbo.pcs
WHERE (pcs_sex = '' or pcs_sex = Null or pcs_sex is Null) and
pcs_degree NOT IN ('HOSP','ANC'))

IF @CNT > 0
BEGIN
DECLARE @TableHTML nvarchar(max)
SET @tableHTML =
N'<H2><font face="Digital, Arial, Helvetica, sans-serif" color ="NAVY">***WARNING - Gender***<BR> (IMMEDIATE INTERVENTION REQUIRED IN PROD)<br></font></H2>' +
N'<table border="1"><font face="Digital, Arial, Helvetica, sans-serif" size = "2">' +
N'<tr><th>Pcs_id1</th><th>pcs_sex</th><th>pcs_lname</th><th>pcs_fname</th><th>pcs_minit</th>' +
N'<th>pcs_chgby</th><th>pcs_degree</th><th>pcs_ctl</th></th></tr><center>' +

(Select
td = pcs_id1,'',-- as PCS#,
td = pcs_sex,'',-- as Gender,
td = pcs_lname,'',-- as Last,
td = pcs_fname,'',-- as First,
td = pcs_minit,'',-- as Middle,
td = pcs_chgby,'',
td = pcs_degree,'',-- as Degree,
td = PCS_CTL-- as Credentialed

FROM impact.dbo.pcs
WHERE (pcs_sex is Null)and pcs_degree NOT IN ('HOSP','ANC'))+

N'</center></table></font>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'JoeSmith@xer.com',
@subject = '***WARNING*** GENDER DETECTED***',@body_format = 'HTML', @body = @tablehtml, @IMPORTANCE = 'HIGH'

END
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-03-30 : 21:42:21

The problem is where you try to SET the value of @tableHTML. If you want to concatenate the results of a select statement to a string then the result set can only have one column and one row.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-31 : 19:29:22

if you want return entire resultset as a string use FOR XML PATH()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MarkLandau
Starting Member

6 Posts

Posted - 2012-06-14 : 09:58:22

I am setting up a sp_SEND_DBMail proc. I can not get the process to send the email. It only goes to the MailItems table in msdb.
My code:

-- Configure server email (Step1)
exec sp_configure 'show advanced options', 1;

RECONFIGURE;

exec sp_configure 'Database Mail XPs', 1;

RECONFIGURE;

DECLARE @mailitem_ID int = NULL,
@profile_ID int = NULL,
@profile_name sysname = 'AFOMessage',
@recipients VARCHAR(MAX) = 'mlandau@hbecorp.com',
-- @copy_recipients VARCHAR(MAX) = ' ',
--@blind_copy_recipients VARCHAR(MAX) = ' ',
@subject NVARCHAR(255) = 'testing sp_send_dbmail',
@from_address VARCHAR(max) = 'HBEConstruction@hbecorp.com',
@reply_to VARCHAR(MAX) = NULL,
@body NVARCHAR(MAX) = 'this is the body of the message.';
-- @body_format VARCHAR(20) = 'TEXT',
-- @importance VARCHAR(6) = 'NORMAL'

/****** Start SysMail ******/
USE msdb ;

EXECUTE dbo.sysmail_start_sp ;

EXEC msdb.dbo.sp_send_dbmail @mailitem_ID, @profile_ID, @profile_name, @recipients
, @subject, @from_address, @reply_to, @body ;

EXECUTE dbo.sysmail_stop_sp ;

-- Configure server email
EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'Database Mail XPs', 0;

RECONFIGURE;

Would any one be able to send me in the right direction?

xx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources