SQL - Query to get server s IP address

Question

Is there a query in SQL Server 2005 I can use to get the server s IP or name

User · Answer

A simpler way to get the machine name without the  InstanceName is   SELECT SERVERPROPERTY  MachineName

User · Answer

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     Shared Memory  as the value for  net transport   and    NULL for  local net address   and    lt local machine gt   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

User · Answer

It is possible to use the host name   function select HOST NAME

User · Answer

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

User · Answer

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   Source of the SQL script

User · Answer

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

User · Answer

It s in the   SERVERNAME variable   SELECT   SERVERNAME

User · Answer

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

User · Answer

you can use command line query and execute in mssql   exec xp cmdshell  ipconfig

User · Answer

--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

User · Answer

select   servername

[sql] SQL - Query to get server's IP address

Examples related to sql

Examples related to sql-server