Dynamics GP upgrade fails – SQL server was renamed

If the computer holding your Dynamics GP SQL databases is renamed, this can lead to a number of problems including failed Dynamics GP upgrades. Internally, SQL server still thinks it’s name is ‘old-server-name’ instead of it’s new name of ‘new-server-name’ – this causes the upgrade to fail. Whenever a virtual SQL server is created from a clone or template this problem can occur because renaming the server is part of the process.

If you started to upgrade a Dynamics GP system and the upgrade fails (before selecting any companies to upgrade) with the error below, than this article is for you. Here’s the error detail:

use msdb /*IF THE JOB DOESN’T ALREADY EXIST, CREATE IT…*/
if not exists (select name from sysjobs where name = ‘Remove Posted PJOURNALs From All Companies’)
begin /*CREATE THE JOB…*/
exec sp_add_job
@job_name = ‘Remove Posted PJOURNALs From All Companies’,
@description = ‘Periodically calls procedure to delete PJOURNAL rows that are ready for removal from each company’
exec sp_add_jobstep
@job_name = ‘Remove Posted PJOURNALs From All Companies’,
@step_name = ‘Call DYNAMICS Stored Procedure’,
@command = ‘exec smCleanupCompanyPostingJournalEntries’,
@database_name = ‘DYNAMICS’
exec sp_add_jobschedule
@job_name = ‘Remove Posted PJOURNALs From All Companies’,
@name = ‘Schedule’,
@freq_type = 4, /* Daily */
@freq_interval = 1, /* Every 1 Day */
@freq_subday_type = 0x4, /* Minutes */
@freq_subday_interval = 30 /* Every 30 minutes */
exec sp_add_jobserver
@job_name = ‘Remove Posted PJOURNALs From All Companies’,
@server_name = @@SERVERNAME end

ERROR [Microsoft][SQL Server Native Client 10.0][SQL Server]The specified @server_name (‘old-server-name’) does not exist.

To verify that your SQL server has the problem described, we need to run some code in Query Analyzer. Copy-n-paste the following code into SQL Query Analyzer and click ‘execute’ :

SELECT @@SERVERNAME As [@@SERVERNAME], 
CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName

If the @@servername and RealInstance columns DO match each other, than you need to look elsewhere for a solution.

If the @@servername and RealInstance columns do NOT match each other, and RealInstance shows the current name of the server – run this code in query analyzer:

SELECT @@SERVERNAME As [@@SERVERNAME], 
CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName

DECLARE @InternalInstanceName sysname;
DECLARE @MachineInstanceName sysname;

SELECT @InternalInstanceName = @@SERVERNAME, 
 	@MachineInstanceName = CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) 
	+ COALESCE('\' + CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '');

IF @InternalInstanceName <> @MachineInstanceName
BEGIN

	-- Rename the instance
	EXEC sp_dropserver @InternalInstanceName;
	EXEC sp_addserver @MachineInstanceName, 'LOCAL';
END

The SQL server’s internal @@servername variable is now properly set to match the name of the server. You need to restart the SQL Server Service for this rename to take affect.

Terrapin Consulting can provide Dynamics GP Support for any of your difficult situations.


Dynamics GP SupportTerrapin Consulting is a premier provider of Dynamics GP Support. We can support you from anywhere in the USA & Canada. Dynamics GP HelpAt Terrapin Consulting there’ll be a veteran CPA keeping an eye on your Dynamics GP system.
www.terrcon.com / 888-368-6670 / accounting@terrcon.com