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

Include variable in Email Body Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 2:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 3:54 PM
Points: 211, Visits: 520
I have a SP that adds a new server to central management studio. It kinds looks like this:

ALTER PROCEDURE [dbo].[XXXXAddServerToCMS_Test] 

@NewServerGroupID int,
@NewServerName nvarchar(50),
@@NewServerConnectionInfo nvarchar(50),
@NewServerDescription nvarchar(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.



Today is the tomorrow you worried about yesterday
Post #1501349
Posted Friday, October 4, 2013 1:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 3,013, Visits: 3,211
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
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
Post #1501440
Posted Wednesday, October 16, 2013 2:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 3:54 PM
Points: 211, Visits: 520
@Stuart

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



Today is the tomorrow you worried about yesterday
Post #1505411
Posted Thursday, October 17, 2013 1:13 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 3,013, Visits: 3,211
No problem - glad to see you are sorted.

-------------------------------
Posting Data Etiquette - Jeff Moden
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
Post #1505500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse