declare @srvr nvarchar(128), @retval int; set @srvr = 'my_linked_srvr'; begin try exec @retval = sys.sp_testlinkedserver @srvr; end try begin catch set @retval = sign(@@error); end catch; if @retval <> 0 raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
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 --连接服务器 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
我在SQL2000下试了楼上的函数,没好使 还有使用过这段代码的么? 或者其他的解决办法
how to test connectivity for a linked server http://www.sqlservercentral.com/scripts/Miscellaneous/31978/USE [DIMSCONSOLIDATEDData] GO /****** Object: UserDefinedFunction [dbo].[General_FXN_Alias] Script Date: 08/07/2007 08:44:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE FUNCTION [dbo].[General_FXN_Alias] ( @DistrictID int, @Table varchar(50) = 'sysobjects' ) RETURNS varchar(90) AS BEGIN -- Declare the return variable here
-- Add the T-SQL statements to compute the return value here
RETURN (SELECT Alias from General_District where districtID = @districtID) + '.' + @TableENDandUSE [DIMSCONSOLIDATEDData] GO /****** Object: Table [dbo].[General_Error_Log] Script Date: 08/07/2007 09:21:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[General_Error_Log]( [ErrorID] [bigint] IDENTITY(1,1) NOT NULL, [ErrorMessage] [varchar](255) NULL, [DateTime] [datetime] NOT NULL, [Alias] [varchar](50) NOT NULL, [Sproc] [varchar](50) NOT NULL, [ErrorCode] [int] NULL ) ON [PRIMARY]GO SET ANSI_PADDING OFF --============================= USE [DIMSCONSOLIDATEDData]GO/****** Object: StoredProcedure [dbo].[General_TestLinkedServer] Script Date: 08/07/2007 09:15:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE [dbo].[General_TestLinkedServer] @DistrictID INTAS BEGIN DECLARE @TestResult BIT, @Alias VARCHAR(50), @ServerName SYSNAME SET @Alias = dbo.General_FXN_Alias(@DistrictID, '') SET @servername = dbo.General_FXN_ResolveServerName(@Alias) BEGIN TRY EXEC @TestResult = sys.sp_testlinkedserver @servername RETURN '1' END TRY BEGIN CATCH--write to windows system log or write to an error table INSERT INTO General_Error_Log ( ErrorMessage, [DateTime], Alias, Sproc, ErrorCode ) VALUES ( 'An error occured in the connection to ' + ISNULL(@servername, 'unknown server') + ' at ' + CONVERT(VARCHAR, GETDATE()) + ' on ' + @@servername + '. The error code returned was ' + ISNULL(CONVERT(VARCHAR, @@Error), 'the server does not exist in catalogue') + '.', GETDATE(), ISNULL(@Alias, 'unknown server'), 'General_TestLinkedServer', @@Error ) END CATCH RETURN '0' ENDUSE [DIMSCONSOLIDATEDData]GO/****** Object: UserDefinedFunction [dbo].[General_FXN_Alias] Script Date: 08/07/2007 08:44:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date:-- Description:-- =============================================CREATE FUNCTION [dbo].[General_FXN_Alias]( @DistrictID int, @Table varchar(50) = 'sysobjects')RETURNS varchar(90)ASBEGIN -- Declare the return variable here -- Add the T-SQL statements to compute the return value here RETURN (SELECT Alias from General_District where districtID = @districtID) + '.' + @TableENDandUSE [DIMSCONSOLIDATEDData]GO/****** Object: Table [dbo].[General_Error_Log] Script Date: 08/07/2007 09:21:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[General_Error_Log]( [ErrorID] [bigint] IDENTITY(1,1) NOT NULL, [ErrorMessage] [varchar](255) NULL, [DateTime] [datetime] NOT NULL, [Alias] [varchar](50) NOT NULL, [Sproc] [varchar](50) NOT NULL, [ErrorCode] [int] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFF
SqlConnection conn=new Sqlconnection(连接字串);
if(conn.State=ConnectionState.Closed())
{
....
}
set @srvr = 'my_linked_srvr';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
@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 --连接服务器
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
还有使用过这段代码的么?
或者其他的解决办法
http://www.sqlservercentral.com/scripts/Miscellaneous/31978/USE [DIMSCONSOLIDATEDData]
GO
/****** Object: UserDefinedFunction [dbo].[General_FXN_Alias] Script Date: 08/07/2007 08:44:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION [dbo].[General_FXN_Alias]
(
@DistrictID int,
@Table varchar(50) = 'sysobjects'
)
RETURNS varchar(90)
AS
BEGIN
-- Declare the return variable here
-- Add the T-SQL statements to compute the return value here
RETURN (SELECT Alias from General_District where districtID = @districtID) + '.' + @TableENDandUSE [DIMSCONSOLIDATEDData]
GO
/****** Object: Table [dbo].[General_Error_Log] Script Date: 08/07/2007 09:21:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[General_Error_Log](
[ErrorID] [bigint] IDENTITY(1,1) NOT NULL,
[ErrorMessage] [varchar](255) NULL,
[DateTime] [datetime] NOT NULL,
[Alias] [varchar](50) NOT NULL,
[Sproc] [varchar](50) NOT NULL,
[ErrorCode] [int] NULL
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
--=============================
USE [DIMSCONSOLIDATEDData]GO/****** Object: StoredProcedure [dbo].[General_TestLinkedServer] Script Date: 08/07/2007 09:15:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================CREATE PROCEDURE [dbo].[General_TestLinkedServer] @DistrictID INTAS BEGIN DECLARE @TestResult BIT, @Alias VARCHAR(50), @ServerName SYSNAME SET @Alias = dbo.General_FXN_Alias(@DistrictID, '') SET @servername = dbo.General_FXN_ResolveServerName(@Alias) BEGIN TRY EXEC @TestResult = sys.sp_testlinkedserver @servername RETURN '1' END TRY BEGIN CATCH--write to windows system log or write to an error table INSERT INTO General_Error_Log ( ErrorMessage, [DateTime], Alias, Sproc, ErrorCode ) VALUES ( 'An error occured in the connection to ' + ISNULL(@servername, 'unknown server') + ' at ' + CONVERT(VARCHAR, GETDATE()) + ' on ' + @@servername + '. The error code returned was ' + ISNULL(CONVERT(VARCHAR, @@Error), 'the server does not exist in catalogue') + '.', GETDATE(), ISNULL(@Alias, 'unknown server'), 'General_TestLinkedServer', @@Error ) END CATCH RETURN '0' ENDUSE [DIMSCONSOLIDATEDData]GO/****** Object: UserDefinedFunction [dbo].[General_FXN_Alias] Script Date: 08/07/2007 08:44:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date:-- Description:-- =============================================CREATE FUNCTION [dbo].[General_FXN_Alias]( @DistrictID int, @Table varchar(50) = 'sysobjects')RETURNS varchar(90)ASBEGIN -- Declare the return variable here -- Add the T-SQL statements to compute the return value here RETURN (SELECT Alias from General_District where districtID = @districtID) + '.' + @TableENDandUSE [DIMSCONSOLIDATEDData]GO/****** Object: Table [dbo].[General_Error_Log] Script Date: 08/07/2007 09:21:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[General_Error_Log]( [ErrorID] [bigint] IDENTITY(1,1) NOT NULL, [ErrorMessage] [varchar](255) NULL, [DateTime] [datetime] NOT NULL, [Alias] [varchar](50) NOT NULL, [Sproc] [varchar](50) NOT NULL, [ErrorCode] [int] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFF