November 26, 2012 at 5:27 am
is any query to find the network IP , OS version etc by using management studio
November 26, 2012 at 5:29 am
SQL Server doesn't track those things. Therefore, no, there is no T-SQL command capable of getting that information.
However, you could use PowerShell or xp_cmdshell to get out to a command prompt and then query for that information using the usual methods.
November 26, 2012 at 5:32 am
in 2008 and above, there is a new function called connection property, and you can get a lot of that information from that.
that can give you both the client ip of the connection as well as the server ip the current connectionis using...it cannot give you other IP's the server listens to...for that you need the permissions to query one of the DMV's: sys.dm_exec_connections .
http://msdn.microsoft.com/en-us/library/bb895240.aspx
my typical sample query:
--Requires SQL 2008 +
SELECT
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell
November 26, 2012 at 5:33 am
Oh, hey! I just learned something new!
Thanks, Lowell.
November 26, 2012 at 5:47 am
i was just looking through the SERVERPROPERTY functions, and cannot seem to find anything that shows the operating system.
it is buried in @@VERSION, though, so i'd expect a serverproperty to pull that part out.
the last part of string from SELECT @@VERSION that you can re-translate into something that makes sense to you:
Win7= ...on Windows NT 5.2 (Build 3790: Service Pack 2)
and
Server 2008 = ...on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) i think.
Lowell
November 26, 2012 at 5:57 am
you can also use xp_regread to query the apprpriate registry values for your SQL server instance.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply