SQL中怎麼查詢電腦IP

[os]

請教各位，在sql中有什麼方法或函數可以查詢到用戶電腦IP

[vivianfdlpw]

IF EXISTS (SELECT * FROM SysObjects WHERE XType = 'P' AND NAME = 'sp_PingServer'
BEGIN
DROP PROCEDURE sp_PingServer
END
GO

CREATE PROCEDURE sp_PingServer
(
@ServerName varchar(50),
@DesireEmail bit
)
AS
/*********************************
ProcName: sp_PingServer
Author: Chris Thibodeaux
Email: [e-mail]
Date: 1/15/2001

Purpose: Returns IP Address of
Destination server via T-SQL

Variables: @ServerName - (The Destination Server)
@DesireEmail - (Added for Swynk User)


**********************************/
SET NOCOUNT ON

-- Declare Local Variables
DECLARE @Sql varchar(500)
DECLARE @t1 varchar(18)
DECLARE @Err varchar(100)

-- Set Local Variables
SET @Sql = 'PING '+@ServerName+' -n 1'
SET @Err = 'Error - Pinging The Destination Server - '+UPPER(@ServerName)

-- Create Temp Table
CREATE TABLE #temp1
(t varchar(3000))

-- Insert PING Data Into Temp Table
INSERT INTO #temp1
EXEC master..xp_cmdshell @Sql

SET @t1 = (SELECT top 1 t from #temp1 order by t DESC)

IF @T1 = ('Request timed out.'
BEGIN
RAISERROR(@Err, 16, 1)
RETURN(-1)
END

IF (@@ERROR = 0)
BEGIN
-- Sends Email if An Email is Desired.
IF (@DesireEmail = 1)
BEGIN
EXEC xp_sendmail @Recipient = 'YourRecipient',
@Subject = 'YourSubject',
@query = 'SELECT TOP 1 SUBSTRING(t, 12, CHARINDEX('':'', t, 12)-12) from #temp1 ORDER BY t DESC'
END

-- Simply Returns The IP Address of the Server
SELECT TOP 1 SUBSTRING(t, 12, (CHARINDEX(':', t, 12)-12)) FROM #temp1 ORDER BY t DESC
END


-- Drop the Temp Table
DROP TABLE #temp1

RETURN(0)
GO

[vivianfdlpw]

简单一点的：

set nocount on
declare @ip varchar(255), @cmd varchar(100)
set @cmd = 'ping ' + HOST_NAME()
create table #temptb (grabfield varchar(255))
insert into #temptb exec master.dbo.xp_cmdshell @cmd
select @ip = substring(grabfield, charindex('[',grabfield)+1,
charindex(']',grabfield)-charindex('[',grabfield)-1) from #temptb where left(grabfield,7) = 'Pinging'
print @ip
drop table #temptb
set nocount off

[os]

3Q，這樣好像比較慢，不知是否有函數？

[wgsasd311]

HOST_NAME
返回工作站名称。

语法
HOST_NAME ( )

返回类型
nchar

注释
当该参数对系统函数可选时，则系统采用当前数据库、主机、服务器用户或数据库用户。内置函数后面必须跟圆括号。

系统函数可以在选择列表、WHERE 子句和任何允许使用表达式的地方使用。

示例
下面的示例创建一个表，该表在 DEFAULT 定义中使用 HOST_NAME() 来记录那些向记录订单的表中插入行的计算机工作站名称。

CREATE TABLE Orders
(OrderID INT PRIMARY KEY,
CustomerID NCHAR(5) REFERENCES Customers(CustomerID),
Workstation NCHAR(30) NOT NULL DEFAULT HOST_NAME(),
OrderDate DATETIME NOT NULL,
ShipDate DATETIME NULL,
ShipperID INT NULL REFERENCES Shippers(ShipperID))