blocks|key|1739003|text|select+@@servername|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1739004|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select @@servername
</code></pre>

blocks|key|1739036|text|它在@@SERVERNAME+变量中；|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1739037|SELECT+@@SERVERNAME;|code-block|syntax|javascript|1739038|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms187944.aspx^0|2|D|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

It's in the <a href="http://msdn.microsoft.com/en-us/library/ms187944.aspx" rel="noreferrer">@@SERVERNAME </a>variable;

<pre><code>SELECT @@SERVERNAME;
</code></pre>

blocks|key|1739084|text|您可以通过以下方式获取thehostname[instancename]：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739085|SELECT+@@SERVERNAME;|code-block|syntax|javascript|1739086|要在使用hostname\instance+name格式时仅获取主机名，请执行以下操作：|1739087|SELECT+LEFT(ltrim(rtrim(@@ServerName)),+Charindex('\',+ltrim(rtrim(@@ServerName)))+-1)|1739088|或者，正如@GilM指出的那样：|1739089|SELECT+SERVERPROPERTY('MachineName')|1739090|您可以使用以下命令获取实际的IP地址：|1739091|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+'%25IP+ADDRESS%25'
++++++++++if+(isnull+(@ipLine,'***')+!=+'***')
++++++++++begin+
++++++++++++++++set+@pos+=+CharIndex+(':',@ipLine,1);
++++++++++++++++set+@ip+=+rtrim(ltrim(substring+(@ipLine+,+
+++++++++++++++@pos+%2B+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|1739092|Source+of+the+SQL+script。|offset|length|1739093|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlmag.com/Articles/ArticleID/48303/48303.html?Ad=1^0|0|0|0|0|0|0|0|0|0|O|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|16|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1C|8|@]|9|@$U|1D|V|1E|1|1F]]|A|$]]|$1|W|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

You can get the[hostname]\[instancename] by:

<pre><code>SELECT @@SERVERNAME;
</code></pre>

To get only the hostname when you have hostname\instance name format:

<pre><code>SELECT LEFT(ltrim(rtrim(@@ServerName)), Charindex('\', ltrim(rtrim(@@ServerName))) -1)
</code></pre>

Alternatively as @GilM pointed out:

<pre><code>SELECT SERVERPROPERTY('MachineName')
</code></pre>

You can get the actual IP address using this:

<pre><code>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
</code></pre>

<a href="http://www.sqlmag.com/Articles/ArticleID/48303/48303.html?Ad=1" rel="noreferrer">Source of the SQL script</a>.

blocks|key|1739114|text|获取不带\InstanceName的计算机名称的一种更简单的方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739115|SELECT+SERVERPROPERTY('MachineName')|code-block|syntax|javascript|1739116|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

A simpler way to get the machine name without the \InstanceName is:

<pre><code>SELECT SERVERPROPERTY('MachineName')
</code></pre>

blocks|key|1739163|text|服务器可能有多个正在侦听的IP地址。如果您的连接被授予VIEW+SERVER+STATE+server权限，则可以运行此查询以获取已连接到SQL+Server的地址：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739164|SELECT+dec.local_net_address
FROM+sys.dm_exec_connections+AS+dec
WHERE+dec.session_id+=+@@SPID;|code-block|syntax|javascript|1739165|该解决方案不需要您通过xp_cmdshell向操作系统提供外壳，这是一种应该在生产服务器上禁用(或至少是严格保护)的技术。它可能要求您将VIEW+SERVER+STATE授予适当的登录，但这比运行xp_cmdshell的安全风险小得多。|1739166|GilM提到的服务器名称的技术是首选的技术：|1739167|SELECT+SERVERPROPERTY(N'MachineName');|1739168|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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:

<pre><code>SELECT dec.local_net_address
FROM sys.dm_exec_connections AS dec
WHERE dec.session_id = @@SPID;
</code></pre>

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:

<pre><code>SELECT SERVERPROPERTY(N'MachineName');
</code></pre>

blocks|key|1739218|text|我知道这是一个古老的帖子，但当您想要从共享内存连接(例如，从服务器本地SSMS中运行的脚本)检索IP地址和TCP端口时，这个解决方案可能会很有用。关键是使用OPENROWSET打开到SQL+Server的辅助连接，在连接字符串中指定'tcp:‘。代码的其余部分只是构建动态SQL，以绕过OPENROWSET不能将变量作为其参数的限制。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739219|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:'+%2B+@@SERVERNAME+%2B+';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''
+++++++++++++++++++++++++++++++++,+'''+%2B+@connectionstring+%2B+'''
+++++++++++++++++++++++++++++++++,+''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|code-block|syntax|javascript|1739220|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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
</code></pre>

blocks|key|1524235|text|您可以在mssql中使用命令行查询和执行：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1524236|exec+xp_cmdshell+'ipconfig'|code-block|syntax|javascript|1524237|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can use command line query and execute in mssql:

<pre><code>exec xp_cmdshell 'ipconfig'
</code></pre>

blocks|key|1739315|text|--试试这个脚本，它能满足我的需要。重新格式化以读取它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739316|SELECT++
SERVERPROPERTY('ComputerNamePhysicalNetBios')++as+'Is_Current_Owner'
++++,SERVERPROPERTY('MachineName')++as+'MachineName'
++++,case+when+@@ServiceName+=+
++++Right+(@@Servername,len(@@ServiceName))+then+@@Servername+
++++++else+@@servername+%2B'+\+'+%2B+@@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;|code-block|syntax|javascript|1739317|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

--Try this script it works to my needs. Reformat to read it.

<pre><code>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;
</code></pre>

blocks|key|1524327|text|可以使用host_name()函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1524328|select+HOST_NAME()|code-block|syntax|javascript|1524329|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

It is possible to use the host_name() function
<pre><code>select HOST_NAME()
</code></pre>

blocks|key|1739398|text|请使用以下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1739399|SELECT+CONNECTIONPROPERTY('local_net_address')+AS+[IP]|code-block|syntax|javascript|1739400|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Please use this query:
<pre class="lang-sql prettyprint-override"><code>SELECT CONNECTIONPROPERTY('local_net_address') AS [IP]

</code></pre>

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

SQL - Query to get server's IP address

SQL Server

服务器

在SQL Server2005中是否有一个查询可以用来获取服务器的IP或名称？

问SQL -查询以获取服务器的IP地址
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL -查询以获取服务器的IP地址EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL -查询以获取服务器的IP地址
EN