Sql Stop Logging: How to tell which port

I am having remote server which I cannot log through term
server or any other method so i need to depend on sql
query analyzer to do most things.
I need to determine the port that server is using ?
is there any method to do so within sql query analyzer?
Any thoughts?apok wrote:
> I am having remote server which I cannot log through term
> server or any other method so i need to depend on sql
> query analyzer to do most things.
> I need to determine the port that server is using ?
> is there any method to do so within sql query analyzer?
> Any thoughts?
If you're using TCP-IP, then the default port is 1433.
--
David Gugick
Imceda Software
www.imceda.com|||No but with mutiple instances you dont know which one is
being used so in that case how do know which port is used.
>--Original Message--
>apok wrote:
>> I am having remote server which I cannot log through
term
>> server or any other method so i need to depend on sql
>> query analyzer to do most things.
>> I need to determine the port that server is using ?
>> is there any method to do so within sql query analyzer?
>> Any thoughts?
>If you're using TCP-IP, then the default port is 1433.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||Hi
Take a look at SQLPing
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
John|||This is a multi-part message in MIME format.
--070705010300050106000600
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Assuming you haven't cycled your errorlog since the last time you
started SQL server you could parse the errorlog into a tmp table and
grab it from there. Like this:
create table #tmp
(
line varchar(4000),
cont int
)
go
insert into #tmp (line, cont)
exec master.dbo.sp_readerrorlog
go
select line from #tmp
where line like '%SQL Server listening on%'
But that wouldn't work if you'd cycled your errorlog. Another way you
could do it would be to read the reg key it's stored in. Like this:
exec master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\<instance
name>\MSSQLServer\SuperSocketNetLib\Tcp',
N'TcpPort'
For a default instance the reg key is different. Don't remember what
exactly, but it's something like this:
exec master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL
Server\MSSQL\MSSQLServer\SuperSocketNetLib\Tcp',
N'TcpPort'
However, the xp_regread xproc is undocumented and therefore likely to
changed (and therefore not recommended). In fact Microsoft changed its
behavior slightly in SQL 2000 SP4. Now, you can only read from certain
reg keys (it's a security thing), although these particular ones are OK
(by default). Also, the reg path for the TCP port is different for SQL
2005 so I wouldn't rely on that method too long.
There's a SQLDMO property (Registry2.TcpPort) for getting the port
number a SQL server is listening on. So you could write the query from
a DOS shell in VBScript or JScript or something like that.
HTH.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
apok wrote:
>No but with mutiple instances you dont know which one is
>being used so in that case how do know which port is used.
>
>
>>--Original Message--
>>apok wrote:
>>
>>I am having remote server which I cannot log through
>>
>term
>
>>server or any other method so i need to depend on sql
>>query analyzer to do most things.
>>I need to determine the port that server is using ?
>>is there any method to do so within sql query analyzer?
>>Any thoughts?
>>
>>If you're using TCP-IP, then the default port is 1433.
>>--
>>David Gugick
>>Imceda Software
>>www.imceda.com
>>.
>>
--070705010300050106000600
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Assuming you haven't cycled your errorlog since the last time you
started SQL server you could parse the errorlog into a tmp table and
grab it from there. Like this: 
 
create table #tmp 
( 
line varchar(4000), 
cont int 
) 
go 
 
insert into #tmp (line, cont) 
exec master.dbo.sp_readerrorlog 
go 
 
select line from #tmp 
where line like '%SQL Server listening on%' 
 
But that wouldn't work if you'd cycled your errorlog. Another way you
could do it would be to read the reg key it's stored in. Like this: 
 
exec master.dbo.xp_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\Microsoft SQL Server\<instance
name>\MSSQLServer\SuperSocketNetLib\Tcp', 
N'TcpPort' 
 
For a default instance the reg key is different. Don't remember what
exactly, but it's something like this: 
 
exec master.dbo.xp_regread 
N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\Microsoft SQL
Server\MSSQL\MSSQLServer\SuperSocketNetLib\Tcp', 
N'TcpPort' 
 
However, the xp_regread xproc is undocumented and therefore likely to
changed (and therefore not recommended). In fact Microsoft changed its
behavior slightly in SQL 2000 SP4. Now, you can only read from certain
reg keys (it's a security thing), although these particular ones are OK
(by default). Also, the reg path for the TCP port is different for SQL
2005 so I wouldn't rely on that method too long. 
 
There's a SQLDMO property (Registry2.TcpPort) for getting the port
number a SQL server is listening on. So you could write the query from
a DOS shell in VBScript or JScript or something like that. 
 
HTH. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
apok wrote:
<blockquote cite="mid14e901c5196f$2e60d790$a501280a@.phx.gbl" type="cite">
<pre wrap="">No but with mutiple instances you dont know which one is
being used so in that case how do know which port is used.
</pre>
<blockquote type="cite">
<pre wrap="">--Original Message--
apok wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I am having remote server which I cannot log through
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->term
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">server or any other method so i need to depend on sql
query analyzer to do most things.
I need to determine the port that server is using ?
is there any method to do so within sql query analyzer?
Any thoughts?
</pre>
</blockquote>
<pre wrap="">If you're using TCP-IP, then the default port is 1433.
--
David Gugick
Imceda Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
.
</pre>
</blockquote>
</blockquote>
</body>
</html>
--070705010300050106000600--

Friday, March 23, 2012

How to tell which port

No comments:

Post a Comment

Sql Stop Logging

Blog Archive

About Me