易语言执行SQL语句问题
数据库连接1.执行SQL（#常量1）--就是执行失败调试输出假常量表里面的常量1长文本
常量1=
--建立域名解析存储过程
CREATE PROCEDURE pr_ymjx (@database AS NVARCHAR(20))
AS
BEGIN
--建立临时表存储IP
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'lsip' AND xtype = 'U')
DROP TABLE lsip
CREATE TABLE lsip(ip NVARCHAR(200))
--声明变量
DECLARE@iptb TABLE(SPID nvarchar(10),
主机名 nvarchar(20),
IP地址 nvarchar(20),
进程PID nvarchar(20),
备注 nvarchar(200),
MAC地址 nvarchar(20))--临时ip表
DECLARE@str AS NVARCHAR(300), --查询字符串
@flag AS NVARCHAR(10),--判断是否可以ping通
@ip AS NVARCHAR(20), --返回的ip地址
@spid AS NVARCHAR(20),--spid进程
@hostname AS NVARCHAR(20),--主机名
@hostprocess AS NVARCHAR(20), --系统进程
@net_address AS NVARCHAR(20), --MAC地址
@menuAS NVARCHAR(200) --软件POS登记备注
--声明游标
DECLARE cur_ymjx CURSOR
FOR
--查询正在使用软件的对应进程信息
SELECT a.spid,RTRIM(LTRIM(a.hostname)), --主机名
LTRIM(RTRIM(a.hostprocess)), --进程PID
LTRIM(RTRIM(a.net_address)) --MAC地址
FROMmaster.dbo.sysprocesses a,
master.dbo.sysdatabases b
WHERE a.dbid = b.dbid and
a.loginame in
('hb','hbv7','iss','isszmv9','isscy4','issws','isszmbalv3',
'starnet','issnatwst','hbposV8','hbfsv9','starishop','issbakev9',
'issfoodv6','issyytv3') and
b.name = @database
OPEN cur_ymjx
FETCH NEXT FROM cur_ymjx INTO @spid,@hostname,@hostprocess,@net_address
WHILE @@FETCH_STATUS = 0
BEGIN
--执行ping主机名，并插入到临时IP表#lsip
SELECT @str = 'ping ' + @hostname + ' -a-n 1 -l 1'
INSERT INTO lsip EXEC master.dbo.xp_cmdshell @str
--删除空信息
DELETE FROM lsip WHERE ip IS NULL OR ip = ''
--判断是否可以ping通
SELECT TOP 1 @flag = CHARINDEX(':',ip) FROM lsip ORDER BY ip DESC
IF @flag = '0' --不能ping通处理
SELECT @ip = '前台或外网连接'
ELSE
--截取IP地址
SELECT TOP 1 @ip = SUBSTRING(ip,12,CHARINDEX(':',ip)-12)
FROM lsip ORDER BY ip DESC
--主机名为空处理
IF @hostname = ''
SELECT @hostname = '未知'
DELETE FROM lsip
--逐行插入最终所需信息
INSERT @iptb SELECT @spid,@hostname,@ip,@hostprocess,
(select top 1 posdesc from t_sys_pos_status where hostname = @hostname),
@net_address
FETCH NEXT FROM cur_ymjx INTO @spid,@hostname,@hostprocess,@net_address
END
CLOSE cur_ymjx
DEALLOCATE cur_ymjx
--删除临时表
DROP TABLE lsip
--查询最终信息记录
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'tb_ipcx' AND xtype = 'U')
DROP TABLE tb_ipcx
SELECT * into tb_ipcx FROM @iptb
--修正空主机名
UPDATE tb_ipcx
SET主机名 = b.主机名 , IP地址 = b.IP地址
FROM (SELECT * FROM tb_ipcx WHERE LEFT(IP地址,7) = '前台或外网连接') a,
(SELECT * FROM tb_ipcx WHERE LEFT(IP地址,7) <> '前台或外网连接') b
WHERE a.MAC地址 = b.MAC地址
END
幼幼帝国论坛，幼幼王朝，呦吧，拯救帝国，我愛幼幼，愛又閣 幼幼论坛最新地址
幼幼帝国论坛uudiguo.com （推荐）
?最私密的内容都在幼幼帝国论坛!?
纯粹个人分享.欢迎转发
页:
[1]