sending email from SQL

Hi, I have the following code which is working fine when running by itself

--IF @@ROWCOUNT > 0 
BEGIN


    EXEC msdb.dbo.sp_send_dbmail
	 @body_format='HTML',
      @profile_name = 'PDN',

	  @recipients = 'aaa@hotmail.com',
	  @blind_copy_recipients = "bbb@bbb.com",

      @subject = 'Deal Tracker notification',
      @body = 'test'

END

Open in new window


But when I put it in another sp, it does not work. There is no error, it is just that email does not get sent out.  any ideas?  thanks
mcrmgAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Changed this line -

@blind_copy_recipients = "bbb@bbb.com"

to

@blind_copy_recipients = 'bbb@bbb.com'
0
 
Pawan KumarDatabase ExpertCommented:
how you are calling it ?
0
 
Pawan KumarDatabase ExpertCommented:
Please try like this ..

CREATE PROC spEmail
AS
BEGIN

	EXEC msdb.dbo.sp_send_dbmail
	@body_format='HTML',
	@profile_name = 'PDN',

	@recipients = 'aaa@hotmail.com',
	@blind_copy_recipients = 'bbb@bbb.com',
	@subject = 'Deal Tracker notification',
	@body = 'test'

END

Open in new window



>>EXECUTION
EXEC spEmail

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
mcrmgAuthor Commented:
this is my sp

USE [pdn]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[st_DailyEmail]
@user as varchar(50),
@email as varchar(50)
AS	
 
DECLARE @MAIL_BODY VARCHAR(8000)
DECLARE @MAIL_BODY1 VARCHAR(8000)
 
--/* HEADER */
SET @MAIL_BODY = 'HTML code here'
 
 SET @MAIL_BODY1 = ''
 SELECT         @MAIL_BODY1 = @MAIL_BODY1 + 
			'code here'
FROM            tables
where convert(varchar(10),[ActivityDate],101) = convert(varchar(10),getdate(),101) and
Assigned = @user


IF @@ROWCOUNT > 0 
BEGIN
SET @MAIL_BODY = @MAIL_BODY + @MAIL_BODY1 + '</table>'
 --USE [msdb]
    EXEC msdb.dbo.sp_send_dbmail
	 @body_format='HTML',
      @profile_name = 'PDN',
      @recipients = @email,
	  @blind_copy_recipients = 'bbb@bbb.com;ccc@ccc.com',
      @subject = 'notification',
      @body = @MAIL_BODY

END

Open in new window


exec [st_DailyEmail] 'john', 'aaa@aaa.com'

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
updated code ..Please try this -

USE [pdn]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[st_DailyEmail]
@user as varchar(50),
@email as varchar(50)
AS	
 
DECLARE @MAIL_BODY VARCHAR(8000) = ''
DECLARE @MAIL_BODY1 VARCHAR(8000) = ''
 
--/* HEADER */
SET @MAIL_BODY = 'HTML code here'
 
SET @MAIL_BODY1 = ''
SELECT @MAIL_BODY1 = @MAIL_BODY1 +  'code here' FROM tables
where convert(varchar(10),[ActivityDate],101) = convert(varchar(10),getdate(),101) and Assigned = @user

--IF @@ROWCOUNT > 0 
BEGIN
	  SET @MAIL_BODY = @MAIL_BODY + @MAIL_BODY1 + '</table>'
      EXEC msdb.dbo.sp_send_dbmail
	  @body_format='HTML',
      @profile_name = 'PDN',
      @recipients = @email,
	  @blind_copy_recipients = 'bbb@bbb.com;ccc@ccc.com',
      @subject = 'notification',
      @body = @MAIL_BODY
END

Open in new window

0
 
mcrmgAuthor Commented:
okay, in order to be easier to debug, I simplified the code. But for some reason, it is not working. Any ideas? (sorry)  thanks

USE [pdn]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[st_DailyEmail]
@user as varchar(50),
@email as varchar(50)
AS	
 
BEGIN
	  
      EXEC msdb.dbo.sp_send_dbmail
	  @body_format='HTML',
      @profile_name = 'PDN',
      @recipients = @email,
	  @blind_copy_recipients = 'bbb@bbb.com;ccc@ccc.com',
      @subject = 'notification',
      @body = 'this is a test'
END

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
What values you are passing in..
@user as varchar(50),
@email as varchar(50)
0
 
mcrmgAuthor Commented:
exec [st_DailyEmail] 'john', 'aaa@aaa.com'

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.