22,209
社区成员
发帖
与我相关
我的任务
分享
/*
调用sqldmo判断远程SQL是否启动
--邹建 2003.11
--调用实例
select 服务器状态=dbo.fgetsstatus('zj','sa','')
*/
IF EXISTS(
SELECT 1
FROM sysobjects
WHERE id = OBJECT_ID('fgetsstatus')
AND OBJECTPROPERTY(id, 'IsInlineFunction') = 0
)
DROP FUNCTION fgetsstatus
GO
CREATE FUNCTION fgetsstatus
(
@servername VARCHAR(50) --服务器名
, @userid VARCHAR(50)='sa' --用户名,如果为nt验证方式,则为空
, @password VARCHAR(50)='' --密码
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @re VARCHAR(20), @ire INT --返回状态
DECLARE @srvid INT --定义服务器、数据库集id
DECLARE @err INT, @src VARCHAR(255), @desc VARCHAR(255) --错误处理变量
--创建sqldmo对象
EXEC @err=sp_oacreate 'sqldmo.sqlserver', @srvid OUTPUT
IF @err<>0
GOTO lberr
EXEC @err=sp_oasetproperty @srvid, 'LoginTimeout', 5
IF @err<>0
GOTO lberr
--连接服务器
IF ISNULL(@userid, '')='' --如果是 Nt验证方式
BEGIN
EXEC @err=sp_oasetproperty @srvid, 'loginsecure', 1
IF @err<>0
GOTO lberr
EXEC @err=sp_oamethod @srvid, 'connect', NULL, @servername
END
ELSE
EXEC @err=sp_oamethod @srvid, 'connect', NULL, @servername, @userid, @password
IF @err<>0
GOTO lberr
--获取服务器状态
EXEC @err=sp_oagetproperty @srvid, 'Status', @ire OUTPUT
IF @err<>0
GOTO lberr
SET @re = CASE @ire
WHEN 0 THEN '未知'
WHEN 1 THEN '运行...'
WHEN 2 THEN '暂停'
WHEN 3 THEN '停止...'
WHEN 4 THEN '正在启动...'
WHEN 5 THEN '正在启动停止...'
WHEN 6 THEN '连接...'
WHEN 7 THEN '正在暂停...'
END
RETURN(@re)
lberr:
EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT
DECLARE @errb VARBINARY(4)
SET @errb = CAST(@err AS VARBINARY(4))
EXEC MASTER..xp_varbintohexstr @errb, @re OUT
SET @re = '错误号: '+@re
+CHAR(13)+'错误源: '+@src
+CHAR(13)+'错误描述: '+@desc
RETURN(@re)
END
GO
参考
Pinging 172.17.102.100 with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.
Ping statistics for 172.17.102.100:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),
NULL