October 3, 2013 at 2:45 pm
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]
October 4, 2013 at 1:05 am
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
October 17, 2013 at 1:13 am
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