mcrmg
asked on
sending email from SQL
Hi, I have the following code which is working fine when running by itself
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
--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'
ENDBut 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
Pawan Kumar
how you are calling it ?
Please try like this ..
>>EXECUTION
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>>EXECUTION
EXEC spEmailASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ENDexec [st_DailyEmail] 'john', 'aaa@aaa.com'
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
ENDASKER
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
What values you are passing in..
@user as varchar(50),
@email as varchar(50)
@user as varchar(50),
@email as varchar(50)
ASKER
exec [st_DailyEmail] 'john', 'aaa@aaa.com'