As every SQL Server DBA might be aware, the default port for SQL Server on which it listens is 1433. But this holds only for the
default instance. In a multi-instance environment, the port allocation is dynamic and is potentially different every time SQL Server is restarted. This script can be used to find the dynamic port of an instance, by scanning the registry.
This script gets the current instance name on the fly and needs NO customization. The script supports both SQL Server 7.x and SQL Server 2000.
Author: Vijay Anisetti
-- This script will get the listening port of the
-- SQL Server, useful for multiple instance servers
-- Vijay Anisetti
CREATE TABLE #GetPort
(
token varchar(100),
value varchar(20))
go
DECLARE @inst varchar(200)
DECLARE @inst1 varchar(100)
IF(charindex('',@@servername) > 0)
BEGIN
SELECT @inst = substring(@@servername,charindex('',@@servername),50)
SELECT @inst = 'SOFTWAREMicrosoftMicrosoft SQL
Server'+@inst+'MSSQLServerSuperSocketNetLibTcp'
SELECT @inst1 = 'TcpDynamicPorts'
END
ELSE
BEGIN
SELECT @inst =
'SOFTWAREMicrosoftMSSQLServerClientSuperSocketNetLibTcp'
SELECT @inst1 = 'DefaultPort'
END
INSERT #GetPort
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1
SELECT value FROM #GetPort
DROP TABLE #GetPort
Back to Database Journal Home
