SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Gregory Ferdinandsen / SQL Server Port Number Script / Latest Posts

RE: SQL Server Port Number Script

vvkp — Wed, 23 Jun 2010 10:20:40 GMT

Good work...quick question...!If I want to change the port, how can I do that by using TSQL? A kind of reverse process to the work you did.Appreciate your time.

RE: SQL Server Port Number Script

SQLRNNR — Wed, 26 May 2010 11:08:23 GMT

Thanks for the updated script.

RE: SQL Server Port Number Script

vlad-548036 — Wed, 26 May 2010 10:43:37 GMT

Excellent! :-) Great job!

RE: SQL Server Port Number Script

wmt — Wed, 26 May 2010 09:41:26 GMT

[quote][b]vlad-548036 (5/26/2010)[/b][hr]Exactly, I was just wondering whether you know where ?[/quote]I meant to post this earlier. This works for me across my servers\instances.It's just a slightly amended version of the original.declare @Server as varchar(128) declare @KeyToInterogate as varchar(200)declare @Version as varchar (512) declare @PortNumber as varchar(8) set @Server = @@ServerName set @Version = left(@@Version, 38) set @KeyToInterogate = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'if charindex('\',@@ServerName) > 0begin set @KeyToInterogate = 'SOFTWARE\Microsoft\Microsoft SQL Server\' set @KeyToInterogate = @KeyToInterogate + substring(@@ServerName,charindex('\',@@ServerName) + 1,len(@@ServerName) - charindex('\',@@ServerName)) set @KeyToInterogate = @KeyToInterogate + '\MSSQLServer\SuperSocketNetLib\Tcp'endexec xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @KeyToInterogate, @value_name = 'TcpPort', @value = @PortNumber output If @PortNumber <> '1433' begin print '*****server: ' + @server + ' is running on port ' + cast(@PortNumber as varchar) + '. Server is: ' + @Version end else print 'server: ' + @server + ' is running on port ' + cast(@PortNumber as varchar) + '. Server is: ' + @Version

RE: SQL Server Port Number Script

vlad-548036 — Wed, 26 May 2010 08:36:51 GMT

Exactly, I was just wondering whether you know where ?

RE: SQL Server Port Number Script

wmt — Wed, 26 May 2010 04:55:13 GMT

[quote][b]vlad-548036 (5/10/2010)[/b][hr]RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.' :doze:this is on named instance of sql 2008 on 64-bit 2008 server[/quote]This script only works on the default instance, as the registry entry for named instances are stored elsewhere.

RE: SQL Server Port Number Script

vlad-548036 — Mon, 10 May 2010 13:26:16 GMT

RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.' :doze:this is on named instance of sql 2008 on 64-bit 2008 server

RE: SQL Server Port Number Script

GregoryF — Tue, 04 May 2010 18:29:36 GMT

The multi-server query is a great addition to SSMS, I remember just a couple of years ago when you would have had to RDP into 70 servers to get this type of information (or run a script like this on 70 servers). Now with the multi server query you can look for Builtin\Administrators on all production servers, change the sa password in one swoop, determine if tempDB is on a seperate drive, etc...Now if Microsoft will just give us something like Oracle Data Guard and or Oracle RAC...

RE: SQL Server Port Number Script

SQLRNNR — Tue, 04 May 2010 18:15:28 GMT

That is a nice script indeed. Thanks.

RE: SQL Server Port Number Script

GregoryF — Tue, 04 May 2010 18:07:39 GMT

It will iterate through server's if you are using a multi-server query that is available in SSMS2008, otherwise it will show just the server that you are querying against.

RE: SQL Server Port Number Script

rVadim — Tue, 04 May 2010 15:46:54 GMT

Good script - Thank you.But it only runs for the server I’m currently connected to. Did I misunderstand your sentence about iterating thru servers?--Vadim.

SQL Server Port Number Script

GregoryF — Thu, 29 Apr 2010 03:45:35 GMT

Comments posted to this topic are about the item [B]SQL Server Port Number Script[/B]