Archive for the ‘SQL Server Port #’ Category

How to query and display the SQL Server port #?

Yes. You can query and display the SQL Server port # through TSQL. You can do so by querying the Windows Registry using the xp_regread stored proc. The following code is how it can be done:

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT

SELECT SERVERPROPERTY('SERVERNAME') AS [Instance Name]
,  @tcp_port AS [SQL Server Port #]

If you wish to send yourself an html report or you will be adding this section to your SQL Server Health Check Report, you can use the following code:

-- Query and display and SQL Server port #
 /*NOTE: Replace the recipients email addresses in line #17 */

set nocount on
-- declare the required variables 
DECLARE @Recipients VARCHAR(123) -- recipients for this alert
, @instancename varchar(55)
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @MailSubject VARCHAR(123)
, @Subject VARCHAR(123)
, @TableHTML VARCHAR(MAX)

select @instancename = CONVERT(varchar, SERVERPROPERTY('SERVERNAME'));


SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT

-- check if database mail is enabled
SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
FROM sys.configurations
WHERE name = 'Database Mail XPs'

IF @IsDBMailEnabled = 0
RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)

ELSE
BEGIN
	SELECT @TableHTML = 
	'<html>
	<div style="font-family:Verdana; font-size:14pt;"><div style="font-weight:bold; width:auto; float:left;">'+@instancename+'</div><div style=" width:auto; float:left; padding-left:5px; "> SQL Server Port #:</div><br></div></div><br>
	<table style="border: 1px solid #000000; border-collapse:collapse; table-layout:fixed; font-family:Arial; font-size:11px;" cellpadding=0 cellspacing=0 border=0>' +
	'<tr style="border: 1px solid #000000; font-family:Verdana; font-size:12px; background-color:#3B8D24; color:white; height:30px;">
		<th style="border: 1px solid #000000;" align=center>Instance Name</th>
		<th style="border: 1px solid #000000;" align=center>Port #</th>
	</tr>'
		
	SELECT @TableHTML = @TableHTML +
		'<tr>' +
		'<td style="border: 1px solid #000000;">' + @instancename + '</td>'+
		'<td style="border: 1px solid #000000;">' + @tcp_port  + '</td></tr>'

	SELECT @TableHTML = @TableHTML +'</table></html>'
			
	-- Send email notification
	SELECT @MailSubject = @instancename + ' SQL Server Port #'
		
	-- Find the database mail profile name
	create table #dbmail_profile
	(
	profileid int null,
	profilename varchar(125) null,
	accountid int null,
	accountname varchar(125) null,
	sequencenumber int null
	)
	insert #dbmail_profile
	EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

	select @MailProfile = profilename
	from #dbmail_profile
	where sequencenumber = 1

	drop table #dbmail_profile

	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = @MailProfile,
		@recipients = @Recipients,
		@subject = @MailSubject,
		@body_format = 'HTML',
		@body = @TableHTML;
END
set nocount off	

This is an example of what it looks like:
SQL Server Port #