Is there a query in SQL Server 2005 I can use to get the server's IP or name?
This question is related to
sql
sql-server
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
The code here Will give you the IP Address;
This will work for a remote client request to SQL 2008 and newer.
If you have Shared Memory connections allowed, then running above on the server itself will give you
<local machine>
' will be shown in 'client_net_address'.'client_net_address' is the address of the computer that the request originated from, whereas 'local_net_address' would be the SQL server (thus NULL over Shared Memory connections), and the address you would give to someone if they can't use the server's NetBios name or FQDN for some reason.
I advice strongly against using this answer. Enabling the shell out is a very bad idea on a production SQL Server.
select @@servername
Most solutions for getting the IP address via t-sql fall into these two camps:
Run ipconfig.exe
via xp_cmdshell
and parse the output
Query DMV sys.dm_exec_connections
I'm not a fan of option #1. Enabling xp_cmdshell has security drawbacks, and there's lots of parsing involved anyway. That's cumbersome. Option #2 is elegant. And it's a pure t-sql solution, which I almost always prefer. Here are two sample queries for option #2:
SELECT c.local_net_address
FROM sys.dm_exec_connections AS c
WHERE c.session_id = @@SPID;
SELECT TOP(1) c.local_net_address
FROM sys.dm_exec_connections AS c
WHERE c.local_net_address IS NOT NULL;
Sometimes, neither of the above queries works, though. Query #1 returns NULL if you're connected over Shared Memory (logged in and running SSMS on the SQL host). Query #2 may return nothing if there are no connections using a non-Shared Memory protocol. This scenario is likely when connected to a newly installed SQL instance. The solution? Force a connection over TCP/IP. To do this, create a new connection in SSMS and use the "tcp:" prefix with the server name. Then re-run either query and you'll get the IP address.
The server might have multiple IP addresses that it is listening on. If your connection has the VIEW SERVER STATE server permission granted to it, you can run this query to get the address you have connected to SQL Server:
SELECT dec.local_net_address
FROM sys.dm_exec_connections AS dec
WHERE dec.session_id = @@SPID;
This solution does not require you to shell out to the OS via xp_cmdshell, which is a technique that should be disabled (or at least strictly secured) on a production server. It may require you to grant VIEW SERVER STATE to the appropriate login, but that is a far smaller security risk than running xp_cmdshell.
The technique mentioned by GilM for the server name is the preferred one:
SELECT SERVERPROPERTY(N'MachineName');
I know this is an old post, but perhaps this solution can be usefull when you want to retrieve the IP address and TCP port from a Shared Memory connection (e.g. from a script run in SSMS locally on the server). The key is to open a secondary connection to your SQL Server using OPENROWSET, in which you specify 'tcp:' in your connection string. The rest of the code is merely building dynamic SQL to get around OPENROWSET’s limitation of not being able to take variables as its parameters.
DECLARE @ip_address varchar(15)
DECLARE @tcp_port int
DECLARE @connectionstring nvarchar(max)
DECLARE @parm_definition nvarchar(max)
DECLARE @command nvarchar(max)
SET @connectionstring = N'Server=tcp:' + @@SERVERNAME + ';Trusted_Connection=yes;'
SET @parm_definition = N'@ip_address_OUT varchar(15) OUTPUT
, @tcp_port_OUT int OUTPUT';
SET @command = N'SELECT @ip_address_OUT = a.local_net_address,
@tcp_port_OUT = a.local_tcp_port
FROM OPENROWSET(''SQLNCLI''
, ''' + @connectionstring + '''
, ''SELECT local_net_address
, local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@spid
'') as a'
EXEC SP_executeSQL @command
, @parm_definition
, @ip_address_OUT = @ip_address OUTPUT
, @tcp_port_OUT = @tcp_port OUTPUT;
SELECT @ip_address, @tcp_port
You can get the[hostname]\[instancename] by:
SELECT @@SERVERNAME;
To get only the hostname when you have hostname\instance name format:
SELECT LEFT(ltrim(rtrim(@@ServerName)), Charindex('\', ltrim(rtrim(@@ServerName))) -1)
Alternatively as @GilM pointed out:
SELECT SERVERPROPERTY('MachineName')
You can get the actual IP address using this:
create Procedure sp_get_ip_address (@ip varchar(40) out)
as
begin
Declare @ipLine varchar(200)
Declare @pos int
set nocount on
set @ip = NULL
Create table #temp (ipLine varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
select @ipLine = ipLine
from #temp
where upper (ipLine) like '%IP ADDRESS%'
if (isnull (@ipLine,'***') != '***')
begin
set @pos = CharIndex (':',@ipLine,1);
set @ip = rtrim(ltrim(substring (@ipLine ,
@pos + 1 ,
len (@ipLine) - @pos)))
end
drop table #temp
set nocount off
end
go
declare @ip varchar(40)
exec sp_get_ip_address @ip out
print @ip
--Try this script it works to my needs. Reformat to read it.
SELECT
SERVERPROPERTY('ComputerNamePhysicalNetBios') as 'Is_Current_Owner'
,SERVERPROPERTY('MachineName') as 'MachineName'
,case when @@ServiceName =
Right (@@Servername,len(@@ServiceName)) then @@Servername
else @@servername +' \ ' + @@Servicename
end as '@@Servername \ Servicename',
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
dec.local_tcp_port,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
dec.local_net_address as 'dec.local_net_address'
FROM sys.dm_exec_connections AS dec
WHERE dec.session_id = @@SPID;
you can use command line query and execute in mssql:
exec xp_cmdshell 'ipconfig'
It's in the @@SERVERNAME variable;
SELECT @@SERVERNAME;
A simpler way to get the machine name without the \InstanceName is:
SELECT SERVERPROPERTY('MachineName')
It is possible to use the host_name() function
select HOST_NAME()
Source: Stackoverflow.com