Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Find the Port a Connection is Using Expand / Collapse
Author
Message
Posted Tuesday, July 6, 2010 11:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 6:29 AM
Points: 1,812, Visits: 2,189
Comments posted to this topic are about the item Find the Port a Connection is Using

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Post #948303
Posted Wednesday, July 7, 2010 1:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 12, 2010 9:45 AM
Points: 2, Visits: 7
Works great in SQL 2005!
However, i have several sites still running on SQL 2000, the script is not working in there (i think the table does not exist yet). Do you have a similar script for SQL 2000? Would be great!
Post #948364
Posted Wednesday, July 7, 2010 1:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:57 AM
Points: 558, Visits: 1,481
It won't work in SQL 2000. The query uses Dynamic Management Views which were only introduced in SQL Server 2005.

Excellent article that I'm sure will come in handy at some point. Thanks for sharing.
Post #948368
Posted Wednesday, July 7, 2010 1:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 12, 2010 9:45 AM
Points: 2, Visits: 7
Thanks for you reply
I was used to use the SP_WHO function, but this does not show the ports
Any way around possible?
Post #948370
Posted Wednesday, July 7, 2010 4:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 10:37 PM
Points: 62, Visits: 50
Thanks for sharing this info.
Post #948430
Posted Wednesday, July 7, 2010 6:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 12, 2014 6:29 AM
Points: 1,812, Visits: 2,189
fluppe_be,

sp_who in SQL 2008 uses a system view in the resource database so I think you will need to write your own version of sp_who or search for one that meets your needs. There are quite a few available that use the DMVs and you would be able to easily add the port to any of them.


MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Post #948502
Posted Wednesday, July 7, 2010 7:55 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 1, 2014 8:37 AM
Points: 4,769, Visits: 208
I think it would if useful to also add the client_tcp_port from the connections DMV, because your SQL Server should only be using 1 open port?
Post #948548
Posted Wednesday, July 7, 2010 2:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 21,744, Visits: 15,435
Nice article. Thanks for sharing.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #948849
Posted Wednesday, July 7, 2010 9:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 13, 2014 7:40 PM
Points: 21, Visits: 93
Too bad these views don't exist on SQL2K
Post #948970
Posted Thursday, July 8, 2010 12:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 3,241, Visits: 5,002
Nice script. Very useful information.
Thanks.


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #949026
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse