SQL Server xp_regread Stored Procedure to Read Registry Key Values
xp_regread extended stored procedure can be used for reading the registry value of a registry key
Although it is very simple to use xp_regread SQL procedure, since it is one of the undocumented stored procedures which are unsupported by Microsoft, it is not recommended to used xp_regread to read the value of a registry key on productive systems.
xp_regread SQL Script to Find SQL Server Port Number
Here is the sql codes of a sample xp_regread SQL procedure statement to find TCP port number that the current SQL Server instance is configured.
SELECT @@SERVERNAME AS ServerName, @@SERVICENAME AS ServiceName
DECLARE @value VARCHAR(20)
DECLARE @key VARCHAR(100)
IF ISNULL(CHARINDEX('\', @@SERVERNAME, 0), 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
SELECT @KEY as [Key]
EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @key,
@value_name = 'TcpPort',
@value = @value OUTPUT
SELECT 'Port Number : ' + CAST(@value AS VARCHAR(5)) AS PortNumber
And the output of the above sql script which uses xp_regread sql stored procedure to find SQL Server 2008 or any other SQL Server versions TCP port number by reading registry key value pairs.
If you get RegQueryValueEx() error when you execute xp_regread sql procedure, you can read the article xp_regread - RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'