Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help on HTML formatted email from Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, April 28, 2010 12:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 23, 2013 11:33 PM
Points: 39, Visits: 111
Hi, i had created a trigger to send email whenever the new data is inserted or updated which sends the email in one line, i need to send the email in HTML format from stored Procedure, please help.
Attached the image how the mail to be sent.


  Post Attachments 
SP.JPG (60 views, 21.98 KB)
Post #911637
Posted Wednesday, April 28, 2010 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 7,019, Visits: 12,909
Please provide table def and sample data as described in the first link in my signature.
It will help us to test our solutions.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #911736
Posted Wednesday, April 28, 2010 4:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 23, 2013 11:33 PM
Points: 39, Visits: 111
Hi Lutz, i am not getting the place to post on your first link, in trigger i have used
/****** Object: Trigger [dbo].[New_Author] Script Date: 04/28/2010 16:01:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[New_Author] ON [dbo].[CLINICIANS] AFTER INSERT, UPDATE AS
DECLARE @SOURCEID VARCHAR(50)
DECLARE @DATAID varchar(50)
DECLARE @DEPTID varchar(50)
DECLARE @AID varchar(50)
DECLARE @ARDESC varchar(50)
DECLARE @HID varchar(50)
SET @SOURCEID = (select sourceid from inserted)
SET @DATAID = (SELECT TRUSTID FROM inserted)
SET @AID = (SELECT AUTHORID FROM inserted)
SET @DEPTID = (SELECT DEPTID FROM inserted)
SET @AD = (SELECT AUTHORDESC FROM inserted)
SET @HID = (SELECT HOSPITALID FROM inserted)
IF (UPDATE (AUTHORID))
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'NEW EMP "' + @aid + '" RECEIVED IN THE "' +@SOURCEID+'.' + @DATAID + '" AND DESCRIPTION IS "'
+ @ARDESC + '" AND THE DEPARTMENT IS "' + @DEPTID + '"'
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'jaiprakash23@hotmail.com',
@copy_recipients='jaiprakash23@hotmail.com',
@body= @MSG,
@subject = 'New EMP Received',
@profile_name = 'Jaype'
END

getting the mail in one line as
NEW EMP "2008" RECEIVED IN THE "IT" AND THE DESCRIPTION IS "Information Technolog" AND THE DEPARTMENT IS "Sysadmin"
Post #911770
Posted Wednesday, April 28, 2010 4:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 14, 2012 2:23 AM
Points: 244, Visits: 409
Hi,
you can use HTML tags to format your @MSG variable.We send html-formatted mails for other purposes.


Regards,
MShenel
Post #911775
Posted Wednesday, April 28, 2010 12:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 7,019, Visits: 12,909
Your trigger code won't work as soon as there is more than 1 row affected.
You need to rethink your process to decide how to deal with that.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #912271
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse