Page 1 of 1 1
Topic Options
#155230 - 2006-01-13 01:00 PM Obtaining no. of connections in SQL Server on command prompt
AstaaLavista Offline
Starting to like KiXtart

Registered: 2005-08-11
Posts: 111
Loc: Gujarat, India.
Hi All,

In SQL server, stored procedure SP_WHO provides the no. of connections. but the stored procedure needs to be run from query analyzer.
Is there any way to obtain the no. of connections of a SQL server directly from command prompt?
Thanks in advance.

Top
#155231 - 2006-01-13 01:19 PM Re: Obtaining no. of connections in SQL Server on command prompt
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
Try "netstat -b" - this should give you open connections with the executable name in most cases.

If this works for the SQL service you can pipe the output to "FIND /C" to count the lines with the SQL executable.

Top
#155232 - 2006-01-13 03:10 PM Re: Obtaining no. of connections in SQL Server on command prompt
AstaaLavista Offline
Starting to like KiXtart

Registered: 2005-08-11
Posts: 111
Loc: Gujarat, India.
Sorry Richard, but i didnt find option -b.

Following is the list of available options.
NETSTAT [-a] [-e] [-n] [-s] [-p proto] [-r] [interval]

Top
#155233 - 2006-01-13 03:14 PM Re: Obtaining no. of connections in SQL Server on command prompt
Les Offline
KiX Master
*****

Registered: 2001-06-11
Posts: 12734
Loc: fortfrances.on.ca
Code:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\LLigetfa>netstat /?

Displays protocol statistics and current TCP/IP network connections.

NETSTAT [-a] [-b] [-e] [-n] [-o] [-p proto] [-r] [-s] [-v] [interval]

-a Displays all connections and listening ports.
-b Displays the executable involved in creating each connection or
listening port. In some cases well-known executables host
multiple independent components, and in these cases the
sequence of components involved in creating the connection
or listening port is displayed. In this case the executable
name is in [] at the bottom, on top is the component it called,
and so forth until TCP/IP was reached. Note that this option
can be time-consuming and will fail unless you have sufficient
permissions.
-e Displays Ethernet statistics. This may be combined with the -s
option.
-n Displays addresses and port numbers in numerical form.
-o Displays the owning process ID associated with each connection.
-p proto Shows connections for the protocol specified by proto; proto
may be any of: TCP, UDP, TCPv6, or UDPv6. If used with the -s
option to display per-protocol statistics, proto may be any of:
IP, IPv6, ICMP, ICMPv6, TCP, TCPv6, UDP, or UDPv6.
-r Displays the routing table.
-s Displays per-protocol statistics. By default, statistics are
shown for IP, IPv6, ICMP, ICMPv6, TCP, TCPv6, UDP, and UDPv6;
the -p option may be used to specify a subset of the default.
-v When used in conjunction with -b, will display sequence of
components involved in creating the connection or listening
port for all executables.
interval Redisplays selected statistics, pausing interval seconds
between each display. Press CTRL+C to stop redisplaying
statistics. If omitted, netstat will print the current
configuration information once.

C:\Documents and Settings\LLigetfa>

_________________________
Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.

Top
#155234 - 2006-01-13 03:17 PM Re: Obtaining no. of connections in SQL Server on command prompt
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
Astaalavista, what's the OS you are running on?
_________________________
!

download KiXnet

Top
#155235 - 2006-01-13 03:42 PM Re: Obtaining no. of connections in SQL Server on command prompt
AstaaLavista Offline
Starting to like KiXtart

Registered: 2005-08-11
Posts: 111
Loc: Gujarat, India.
i am currently working on windows 2000 server.

It gives the following:
Displays protocol statistics and current TCP/IP network connections.

NETSTAT [-a] [-e] [-n] [-s] [-p proto] [-r] [interval]

-a Displays all connections and listening ports.
-e Displays Ethernet statistics. This may be combined with the -s
option.
-n Displays addresses and port numbers in numerical form.
-p proto Shows connections for the protocol specified by proto; proto
may be TCP or UDP. If used with the -s option to display
per-protocol statistics, proto may be TCP, UDP, or IP.
-r Displays the routing table.
-s Displays per-protocol statistics. By default, statistics are
shown for TCP, UDP and IP; the -p option may be used to specify
a subset of the default.
interval Redisplays selected statistics, pausing interval seconds
between each display. Press CTRL+C to stop redisplaying
statistics. If omitted, netstat will print the current
configuration information once.

Top
#155236 - 2006-01-13 04:14 PM Re: Obtaining no. of connections in SQL Server on command prompt
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
Hmm... not available on W2K.

If the SQL server provides metrics to performance monitor you might be able to pull them out with WMI.

Unfortunately I don't have access to a SQL server to check at the moment.

Top
#155237 - 2006-01-13 07:43 PM Re: Obtaining no. of connections in SQL Server on command prompt
Howard Bullock Offline
KiX Supporter
*****

Registered: 2000-09-15
Posts: 5809
Loc: Harrisburg, PA USA
check your SQL Server online books for a command prompt utility call ISQL. You can run SP_WHO store procedure from the command line.

Quote:

isql -E -w 1024 -Q sp_who >sp_who.txt




Edited by Howard Bullock (2006-01-13 07:50 PM)
_________________________
Home page: http://www.kixhelp.com/hb/

Top
#155238 - 2006-01-16 09:40 AM Re: Obtaining no. of connections in SQL Server on command prompt
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
Is access to the stored procedure (and relevant tables) restricted to particular accounts?
Top
Page 1 of 1 1


Moderator:  Arend_, Allen, Jochen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 1636 anonymous users online.
Newest Members
Viginette, ManuvdWielNL, Sir_Barrington, batdk82, StuTheCoder
17888 Registered Users

Generated in 0.054 seconds in which 0.026 seconds were spent on a total of 12 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org