Ubuntu – How to change the hostname without a restart

For SQL Server 2000, I quite honestly wouldn't bother trying to get operators to work. SQL Mail is a royal PITA and requires Outlook or a similar mail client to be installed on the server. I would rather just setup each job to have a step called "mail on failure", which uses a token to identify the job, and then calls a stored procedure. You would only reach that step in the workflow if you set a step to go to that step on failure. Otherwise the step before it simply quits the job with success.

A couple of alternatives:

(1) I used xp_smtp_sendmail with great success when I was still straddled with SQL Server 2000. The challenge is it used to be available on sqldev.net, but Gert seems to have abandoned that site. So you may have to search around to find a copy of the DLL. It was written by a Microsoft employee but the standard "download from a trusted source only" disclaimer applies.

(2) Use CDO. The same ick factor as SQL Mail but no dependency on Outlook / MAPI. See this KB article for some info, but here is an example that sets up a stored procedure to send job mail failures and then how to include a job token in a job step to call that stored procedure.

First, a generic procedure to send the e-mail (you may want to re-use this for more than just job failures):

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
   @From       VARCHAR(320)   = 'default_admin@yourcompany.com',
   @To         VARCHAR(320)   = 'default_admin@yourcompany.com',
   @Subject    NVARCHAR(4000) = N'',
   @Body       NVARCHAR(4000) = N'',
   @SMTPServer VARCHAR(2000)  = '127.0.0.1'
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @obj INT;

  EXEC sp_OACreate 'CDO.Message', @obj OUTPUT;
  EXEC sp_OASetProperty           @obj,
    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
    '2';
  EXEC sp_OASetProperty           @obj,
    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
    @SMTPServer;
  EXEC sp_OAMethod                @obj,
    'Configuration.Fields.Update',
    NULL;
  EXEC sp_OASetProperty           @obj, 'To',       @To;
  EXEC sp_OASetProperty           @obj, 'From',     @From;
  EXEC sp_OASetProperty           @obj, 'Subject',  @Subject;
  EXEC sp_OASetProperty           @obj, 'TextBody', @Body;
  EXEC sp_OAMethod                @obj, 'Send',     NULL;
  EXEC sp_OADestroy               @obj;
END
GO

Next, a procedure to handle the e-mail specifically for job failures:

CREATE PROCEDURE dbo.JobAlertSender
   @To      VARCHAR(320) = 'default_admin@yourcompany.com',
   @job_id  VARCHAR(36)
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @Subject NVARCHAR(4000);
   SELECT @Subject = N'Job failed: ' + name
       FROM msdb.dbo.sysjobs
       WHERE job_id = @job_id;

   DECLARE @Body NVARCHAR(4000);
   SET @Body = CONVERT(CHAR(10), GETDATE(), 120) 
       + ' ' + CONVERT(CHAR(5),  GETDATE(), 108);

   EXEC dbo.sp_send_cdosysmail
        @From    = 'default_admin@yourcompany.com',
        @To      = @To,
        @Subject = @Subject,
        @Body    = @Body;
END
GO

Now you can add a step to your job that calls this procedure. You would only hit this step if a previous step failed. The step would have code like this (note that you may want to override the default "To" person depending on the job):

EXEC master.dbo.JobAlertSender
   -- @To = 'override@yourcompany.com',
   @job_id = '(JOBID)';

Here is how the job step would look:

And here is how you would set up previous steps, either using "go to the next step" or picking the failure step explicitly from the drop-down:

If you find xp_smtp_sendmail, you'd just call a slightly different procedure from dbo.JobAlertSender.

If you don't have a valid SMTP server, you'll need to install an SMTP server locally. For help with that you'll want to hit up SuperUser or ServerFault. If you need something to test locally in the meantime, nothing could be simpler than smtp4dev. You won't get the e-mails in your inbox, but you can verify the contents, headers, etc until you do have a proper SMTP server in place.

Google also told me I wrote this article in 2002 which may contain some useful information.

In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator. For instance:

exec msdb.dbo.sp_update_operator 
    @name = 'OldOperatorName', 
    @new_name = 'NewOperatorName';
go

As for SSMS: most things you do in SSMS you have to option to script out the changes you are making in the GUI. So, without hitting Ok, you can select the Script button at the top to see what T-SQL SSMS will be executing. It seems as though even with a changed name (i.e. altered text in the Name text box), the generated T-SQL doesn't contain a specified @new_name parameter for the stored procedure call.

Moral of the story? Don't rely on a UI. If you want to have full control over what's happening then you should be using T-SQL. There will be no question in your mind if you're writing out the code to do what you want. No assumptions, nothing.

Regarding changing the name, SQL Server stores the ID of the operator to create the correlation between notification -> operator, and not the operator name. So no, you are not bound to the operator name and I don't believe changing the name of the operator will break anything.