Post reply

Need help on HTML formatted email from Stored Procedure

  • April 28, 2010 at 12:27 am

    #220933

    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.

  • April 28, 2010 at 3:42 am

    #1158730

    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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • April 28, 2010 at 4:46 am

    #1158760

    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"

  • April 28, 2010 at 4:56 am

    #1158763

    Hi,

    you can use HTML tags to format your @MSG variable.We send html-formatted mails for other purposes.

    Regards,
    MShenel

  • April 28, 2010 at 12:17 pm

    #1159202

    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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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