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?
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