获取该进程所在客户端的ip地址
create PROCEDURE dbo.SP_SPIDtoIP @SPID int AS -- SPID to MAC -- lj DECLARE @MAC as varchar(12) SELECT @MAC = NET_ADDRESS FROM master..sysprocesses WHERE SPID = @SPID -- MAC to IP DECLARE @MACDisplay as varchar(18) DECLARE @IP as varchar(15) CREATE TABLE #temp (OUTPUT varchar(255) null) SET NOCOUNT ON INSERT INTO #temp EXEC master..xp_cmdshell arp -a if @@error begin RAISERROR (The level for job_id:%d should be between %d and %d., 16, 1) --ROLLBACK TRANSACTION end SELECT @MACDisplay = LEFT(@MAC, 2) + - + SUBSTRING(@MAC, 3, 2) + - + SUBSTRING(@MAC, 5, 2) + - + SUBSTRING(@MAC, 7, 2) + - + SUBSTRING(@MAC, 9, 2) + - + SUBSTRING(@MAC, 11, 2)SELECT @IP = SUBSTRING(output, 3, 15) FROM #temp WHERE output LIKE % + @MACDisplay + % -- Resolve the IP --DECLARE @CMD as varchar(100) --select @CMD = master..xp_cmdshell "ping -a + @IP + " --exec (@CMD) DROP TABLE #temp SET NOCOUNT OFF GO
create PROCEDURE dbo.SP_SPIDtoIP @SPID int AS -- SPID to MAC -- lj DECLARE @MAC as varchar(12) SELECT @MAC = NET_ADDRESS FROM master..sysprocesses WHERE SPID = @SPID -- MAC to IP DECLARE @MACDisplay as varchar(18) DECLARE @IP as varchar(15) CREATE TABLE #temp (OUTPUT varchar(255) null) SET NOCOUNT ON INSERT INTO #temp EXEC master..xp_cmdshell arp -a if @@error begin RAISERROR (The level for job_id:%d should be between %d and %d., 16, 1) --ROLLBACK TRANSACTION end SELECT @MACDisplay = LEFT(@MAC, 2) + - + SUBSTRING(@MAC, 3, 2) + - + SUBSTRING(@MAC, 5, 2) + - + SUBSTRING(@MAC, 7, 2) + - + SUBSTRING(@MAC, 9, 2) + - + SUBSTRING(@MAC, 11, 2)SELECT @IP = SUBSTRING(output, 3, 15) FROM #temp WHERE output LIKE % + @MACDisplay + % -- Resolve the IP --DECLARE @CMD as varchar(100) --select @CMD = master..xp_cmdshell "ping -a + @IP + " --exec (@CMD) DROP TABLE #temp SET NOCOUNT OFF GO
