Getting SQL Server instance name from tcp port number

Question

I have a scenario, in which I am using -q option to change the collation of SQL Server Instance. The command is as follows:

 sqlservr.exe -m -T4022 -T3659 -s"SQLexpress" -q"SQL_Latin1_General_CP1_CI_AI"

The above command works perfectly, where I have an instance name as ".\SQLExpress". But if I want to use the instance name using TCP port number, such as ".,52407", then how can I execute it above command, because I get the following error:

SQL Server

Your SQL Server installation is either corrupt or has been tampered with (Error: Instance name exceeds maximum length). Please uninstall then re-run setup to correct this problem

I was thinking to get the instance name from this port number to solve this issue. Is it possible to get the instance name from TCP port number through any query? Or is there any other way to execute the above command through TCP port number?

score 0 · Accepted Answer · answered Oct 23 '20 at 15:05

Following piece of code can handle the above situation:

If($ServerInstanceName -like "*,*")
{
Write-Host "SQL Server Instance Name containts TCP Port Number"
$SQLQuery=@"
           SET NOCOUNT ON
           Declare @key Varchar(100), @PortNumber varchar(100)
           if charindex('\',CONVERT(varchar(200), 
           SERVERPROPERTY('servername')),0<>0
           begin
             set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'
           end
           else
           begin
             set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'
           end
           EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key,@value_name='Tcpport',@value=@PortNumber OUTPUT
           SELECT CONVERT(varchar(200), SERVERPROPERTY('servername')) AS ServerName
         "@
$ServerInstanceName = (Invoke-Sqlcmd -ServerInstance 
$ServerInstanceName -Database 'master' -Query $SQLQuery).ServerName

Write-Host "Compatible ServerName (without TCP Port) to change its Collation:" $ServerInstanceName

Getting SQL Server instance name from tcp port number

1 Answers1