Include variable in Email Body

  • I have a SP that adds a new server to central management studio. It kinds looks like this:

    ALTER PROCEDURE [dbo].[XXXXAddServerToCMS_Test]

    @NewServerGroupIDint,

    @NewServerNamenvarchar(50),

    @@NewServerConnectionInfonvarchar(50),

    @NewServerDescriptionnvarchar(120)

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert

    INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers_internal

    (Server_Group_ID, name, Server_name, description, Server_Type)

    VALUES(@NewServerGroupID, @NewServerName, @@NewServerConnectionInfo, @NewServerDescription,0)

    I'm trying to add a email alert to the same like this:

    -- Email Confirmation

    EXEC sp_send_dbmail default,

    @recipients='myemail@mydomain.us',

    @subject='CMS on 007 Update',

    @body='This is an email confirmation that the following Server/s are now a part of Central Management Server. ' @NewServerConnectionInfo '

    Thanks,

    SQL SERVER'

    I tried both single quotes and double it fails identifying the local variable.

    Please suggest an alternative to add the new servername (which is being supplied as input parameter to the SP ) to my email body.

    Please do not suggest creating another new SP for the same.

    [font="Verdana"]
    Today is the tomorrow you worried about yesterday:-)
    [/font]

  • Hi,

    I've had this problem too. The way that worked for me was to create another variable, populate this with you variable then use that for the email e.g.

    declare @Message varchar(500)

    set @Message = 'This is an email confirmation that the following Server/s are now a part of Central Management Server. ' + @NewServerConnectionInfo +

    'Thanks,

    SQL SERVER'

    EXEC sp_send_dbmail default,

    @recipients='myemail@mydomain.us',

    @subject='CMS on 007 Update',

    @body=@Message

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • @stuart

    That worked perfectly. Thanks much. Sorry to it took me a while to actually put it in practice.

    [font="Verdana"]
    Today is the tomorrow you worried about yesterday:-)
    [/font]

  • No problem - glad to see you are sorted.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

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

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