SQL Server Port Number Script

Question

SQL Server Port Number Script

GregoryF

SSCertifiable

Points: 6296
More actions
April 29, 2010 at 3:45 am

#224008

Comments posted to this topic are about the item SQL Server Port Number Script
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

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.

--Vadim R.

GregoryF SSCertifiable Points: 6296 More actions · Answer 2

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.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

That is a nice script indeed. Thanks.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GregoryF SSCertifiable Points: 6296 More actions · Answer 4

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

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

vlad-548036 Hall of Fame Points: 3511 More actions · Answer 5

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

wmt SSChasing Mays Points: 602 More actions · Answer 6

vlad-548036 (5/10/2010)
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

This script only works on the default instance, as the registry entry for named instances are stored elsewhere.

vlad-548036 Hall of Fame Points: 3511 More actions · Answer 7

Exactly, I was just wondering whether you know where ?

wmt SSChasing Mays Points: 602 More actions · Answer 8

vlad-548036 (5/26/2010)
Exactly, I was just wondering whether you know where ?

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) > 0

begin

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'

end

exec 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

vlad-548036 Hall of Fame Points: 3511 More actions · Answer 9

vlad-548036

Hall of Fame

Points: 3511

May 26, 2010 at 10:43 am

#1172889

Excellent! 🙂 Great job!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Thanks for the updated script.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

vvkp SSC Enthusiast Points: 124 More actions · Answer 11

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.