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