sql-servertcp-portsql-server-collation

Getting SQL Server instance name from tcp port number


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?


Solution

  • 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