Rename-D365ComputerName fails on the new 10.0.29 VHD

[fhoo]

Renaming the VM with Rename-D365ComputerName fails on the latest VHD (10.0.29) with the following error message:

[Rename-D365ComputerName] Something went wrong while working against the database. | Exception calling "ExecuteNonQuery" with "0" argument(s): "The server 'DVHD10029' does not exist. Use sp_helpserver to show available servers.

The reason is that the current computer name is not registered with the SQL server and hence removing that server name with sp_dropserver fails.

[fhoo]

You can circumvent the error by setting the COMPUTERNAME environment variable to 'pkrvmug8s5trcwh' before calling the cmdlet, because that is the name which is initially configured. But I think the tools should be more tolerant against such an issue 😃

$env:COMPUTERNAME = 'pkrvmug8s5trcwh'
Rename-D365ComputerName

[Splaxi]

Do you have a way, in TSQL, so we can avoid that part for failing?

[fhoo]

I think the easiest would be to put the sp_dropserver in a try / catch to avoid the statement to fail. So changing the rename-computer.sql to this:

BEGIN TRY
    EXEC sp_dropserver [@OldComputerName];
END TRY
BEGIN CATCH
    PRINT '@OldComputerName could not be dropped!'
END CATCH

EXEC sp_addserver [@NewComputerName], local;

should be ok.

If we want to remove all the registered server name despite the current computer name, we can go the route to first select all the registered servers into a table and then iterate over the result to remove all the server names like this:

declare @servers table (name nvarchar(128), network_name nvarchar(128), status nvarchar(max), id char(4), collation_name nvarchar(128), connection_timeout int, query_timeout int)

insert into @servers exec sp_helpserver

declare server_cursor cursor
for
    select name from @servers

declare @serverName nvarchar(128)

open server_cursor
fetch next from server_cursor into @serverName

while @@FETCH_STATUS = 0  
begin  
    EXEC sp_dropserver [@serverName]
    fetch next from server_cursor into @serverName  
end

close server_cursor
deallocate server_cursor

Which solution would you prefer? I'd say version one should be enough in the first step and we'll wait for the next VHD version to see if there are still wrong server names registered?

[fhoo]

I've added a pull request with the first and easiest option to fix that. So this issue can be closed when the pull request is merged.

[FH-Inway]

@fhoo or @Splaxi Can we close this issue now?

[fhoo]

Yes, this can be closed. Thanks for cleaning up :)

[FH-Inway]

Unfortunately, I think the change did not resolve the issue completely. When I run the command on a fresh local 10.0.32 VHD environment, the error "Something went wrong while working against the database occurs." (see full error below).

After playing around with it a bit, it seems the sp_dropserver command still needs to be called with the weird server name (which btw. has changed to pkrvm6u0dlm71tw). Only then the sp_addserver succeeds.

I got it working with @fhoo 's workaround by first setting the computer name environment variable.

I propose to change

https://github.com/d365collaborative/d365fo.tools/blob/4170454b237013b4df2538f89e2abc8b9a74fbb3/d365fo.tools/internal/sql/rename-computer.sql#L2

to

    EXEC sp_dropserver @@SERVERNAME;

Log of failing rename:

C:\Users\localadmin> Rename-D365ComputerName -NewName MyNewName -Verbose -ShowOriginalProgress -Debug VERBOSE: [17:12:06][Rename-D365ComputerName] Testing for elevated runtime DEBUG: 29401 | [17:12:06][Rename-D365ComputerName] Testing for elevated runtime DEBUG: 7588 | [17:12:06][Test-PathExists] Testing the path: C:\Program Files\Microsoft SQL Server Reporting Services\Shared Tools\rsconfig.exe VERBOSE: [17:12:06][Rename-D365ComputerName] Renaming computer to MyNewName DEBUG: 29421 | [17:12:06][Rename-D365ComputerName] Renaming computer to MyNewName WARNING: The changes will take effect after you restart the computer DVHDN10032. VERBOSE: [17:12:06][Rename-D365ComputerName] Renaming local server name inside SQL Server to MyNewName DEBUG: 29425 | [17:12:06][Rename-D365ComputerName] Renaming local server name inside SQL Server to MyNewName VERBOSE: [17:12:06][Test-TrustedConnection] Capabilities based on the centralized logic in the psm1 file. DEBUG: 7703 | [17:12:06][Test-TrustedConnection] Capabilities based on the centralized logic in the psm1 file. DEBUG: 3342 | [17:12:06][Get-SQLCommand] Writing the bound parameters VERBOSE: [17:12:06][Get-SQLCommand] Building the SQL connection string. DEBUG: 3361 | [17:12:06][Get-SQLCommand] Building the SQL connection string. DEBUG: 29444 | [17:12:06][Rename-D365ComputerName] Executing a script against the database. [17:12:06][Rename-D365ComputerName] Something went wrong while working against the database. | Exception calling "ExecuteNonQuery" with "0" argument(s): "The server 'MyNewName' already exists. DVHDN10032 could not be dropped!" DEBUG: 29452 | [17:12:06][Rename-D365ComputerName] Something went wrong while working against the database. | Exception calling "ExecuteNonQuery" with "0" argument(s): "The server 'MyNewName' already exists. DVHDN10032 could not be dropped!" WARNING: [17:12:06][Rename-D365ComputerName] Stopping because of errors. | Something went wrong while working against the database. C:\Users\localadmin> $error Stop-PSFFunction : Something went wrong while working against the database. At line:29453 char:9

• Stop-PSFFunction -Message "Stopping because of errors." -Exce ...

• 
  + CategoryInfo          : NotSpecified: (:) [Write-Error], Exception
  + FullyQualifiedErrorId : d365fo.tools_Rename-D365ComputerName,Stop-PSFFunction
  Exception calling "ExecuteNonQuery" with "0" argument(s): "The server 'MyNewName' already exists.
  DVHDN10032 could not be dropped!"
  At line:29448 char:9

• $null = $sqlCommand.ExecuteNonQuery()

• 
  + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
  + FullyQualifiedErrorId : SqlException

[fhoo]

@FH-Inway I have tested your proposed fix and it works perfectly. So we should include it in the next version of the tools!