Machine Name change not being recognized by SQL

When running this:
master.dbo.sp_add_log_shipping_primary_database

I get the following error:
Msg 14262, Level 16, State 1, Procedure sp_add_jobserver, Line 88
The specified @server_name ('oldname') does not exist.

Note that the machine name for server 'oldname' was changed to 'newname'
and rebooted. As expected, @@servername and serverproperty('servername')
return 'newname'

However, somehow oldserver is getting added to the job and, of course, it is
coming back as not existing.

What has to be done to the SQL instance to get it to recognize a machine
name
change?

I am using SQL 2005 (default instance) on Windows 2003 Server, all updates
applied.

Thanks.

On Thu, 4 Feb 2010 15:29:14 -0500, "Neil W" wrote:

>When running this:
> master.dbo.sp_add_log_shipping_primary_database
>
>I get the following error:
>Msg 14262, Level 16, State 1, Procedure sp_add_jobserver, Line 88
>The specified @server_name ('oldname') does not exist.
>
>Note that the machine name for server 'oldname' was changed to 'newname'
>and rebooted. As expected, @@servername and serverproperty('servername')
>return 'newname'
>
>However, somehow oldserver is getting added to the job and, of course, it is
>coming back as not existing.
>
>What has to be done to the SQL instance to get it to recognize a machine
>name
>change?
>
>I am using SQL 2005 (default instance) on Windows 2003 Server, all updates
>applied.
>
>Thanks.
>
>
>
Hi

I assume SELECT @@SERVERNAME returns the old name?

SELECT HOST_NAME(), @@SERVERNAME

Than try:

DECLARE @INSTANCENAME sysname
SET @INSTANCENAME = NULLIF(RIGHT ( @@SERVERNAME, CASE WHEN
CHARINDEX('\',@@SERVERNAME ) > 0 THEN LEN(@@SERVERNAME) -
CHARINDEX('\',@@SERVERNAME ) ELSE 0 END ),'')
--SELECT @INSTANCENAME

IF LEFT ( @@SERVERNAME, CASE WHEN CHARINDEX('\',@@SERVERNAME ) > 0
THEN CHARINDEX('\',@@SERVERNAME ) -1 ELSE LEN(@@SERVERNAME) END ) <>
HOST_NAME()
BEGIN
DECLARE @cmd nvarchar(4000)
SET @cmd = N'EXEC sp_dropserver ''' + @@SERVERNAME + N''';
EXEC sp_addserver ''' + RTRIM(HOST_NAME()) + ISNULL(N'\' +
@INSTANCENAME,'') + ''', local'
-- PRINT @cmd
EXEC ( @cmd )
END

John