导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

这个通过 ID返回SQL用户IP的存储过程是不是有错误啊~!

tianhuo_soft 2008-01-12 11:49:00

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

上面是我在上网超的 怎么调用都不成功 没有返回值啊~!
...全文
77 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
-狙击手- 2008-01-13
declare @ip varchar(15),@sql varchar(1000)

--得到ip地址
create table #ip(a varchar(200))
set @sql='ping '+host_name()+' -a -n 1 -l 1'
insert into #ip exec master..xp_cmdshell @sql

select @ip=left(a,patindex('%:%',a)-1) from(
select a=substring(a,patindex('Ping statistics for %:%',a)+20,20)
from #ip where a like 'Ping statistics for %:%') a

--显示结果
select 用户计算机名=host_name(),ip地址=@ip

drop table #ip

/*

用户计算机名 ip地址
-------------------------------------------------------------------------------------------------------------------------------- ---------------
FLYSTONE 192.168.0.76
*/
回复
tianhuo_soft 2008-01-13
这个只能返回MAC地址 不能返回IP地址啊
回复
caperbird 2008-01-12
MARK
回复
-狙击手- 2008-01-12
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)
select @macdisplay
DROP TABLE #temp
SET NOCOUNT OFF
GO



exec SP_SPIDtoIP 53


drop proc SP_SPIDtoIP
回复
xiaoku 2008-01-12
hehe ...

我今天用了下,说我权限不够...
回复
xp1056 2008-01-12
正在学习使用sql的系统存储过程,借鉴下
回复
云中客 2008-01-12

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)
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 + '%'
DROP TABLE #temp
SET NOCOUNT OFF
--增加此行做为显示
Select @MACDisplay
GO


回复
baoshan 2008-01-12
你只是给变量赋了值,但最后并没有显示,怎么会有值呢,在DROP TABLE #temp 后加上一句 select @ip试一试
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告