SQL:在SQL SERVER捕获异常-DBLINK

測試環境： SQL SERVER 2008R2 , 从SQL SERVER 2008R2 通过 DBLINK向ORACLE 11G 插入数据 ，插入失败时，在SQL SERV ER 捕获异常，并写日志表。

IF OBJECT_ID('P_TEST_TO_EBS','P')>0 
  DROP PROCEDURE P_TEST_TO_EBS;
GO   
CREATE PROCEDURE P_TEST_TO_EBS( @START_ID INT = 0, @END_ID INT =10)
AS 
BEGIN
  declare @i int = 0 ;
  declare @j int = 10;
  declare @sql nvarchar(4000) , @INSERT NVARCHAR(4000) ;
  declare @name nvarchar(50)=N'Sam';
 /*  -- ORACLE 11G 
  CREATE TABLE CUX.CUX_TEST( "ID" NUMBER, "NAME" VARCHAR2(20)) ;
  CREATE UNIQUE INDEX CUX.CUX_TEST_U01 ON CUX.CUX_TEST("ID"); 
 */
/*
DROP TABLE  T_PROCESS_LOG;

CREATE TABLE T_PROCESS_LOG(
LOG_ID BIGINT IDENTITY(1,1) PRIMARY KEY, 
ObjectName NVARCHAR(150),
ObjectType Nvarchar(150),
ErrorNumber INT ,
ErrorSeverity INT ,
ErrorState NVARCHAR(250),
ErrorProcedure NVARCHAR(500),
ErrorLine  NVARCHAR(50),
ErrorMessage  NVARCHAR(2000),
CommandText nvarchar(4000),
CREATION_DATE DATETIME DEFAULT GETDATE(),
CREATED_BY NVARCHAR(50),
LAST_UPDATE_DATE DATETIME DEFAULT GETDATE(),
LAST_UPDATED_BY NVARCHAR(50)
)  ON [LOG] ;
*/  
  set language 简体中文
  SET @I= @START_ID;
  SET @j = @END_ID;
    while @i <= @j 
  begin
  BEGIN TRY 
  BEGIN 

   
    SET @INSERT = N'INSERT OPENQUERY(LN_APPS,'' SELECT *  
       FROM CUX.CUX_TEST where "ID"  = ''''' + CAST(@I AS NVARCHAR(10)) + ''''''' ) 
        SELECT '+CAST(@I AS  NVARCHAR(10)) +N' AS "ID" , '''+  @name + CAST(@I AS NVARCHAR(10) ) +''' AS "NAME" '  ;
      PRINT @INSERT;   
      EXEC SP_EXECUTESQL @INSERT  ;
    set @i = @i+1;
 -- end;
  END ;
  END TRY
  BEGIN CATCH 
  BEGIN
    INSERT INTO T_PROCESS_LOG(OBJECTNAME, OBJECTTYPE,
      ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage,
      CommandText ) 
    SELECT 
        'P_TEST' AS OBJECTNAME, 'PROCEDURE' AS OBJECTTYPE,
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage,
        substring(@insert,1,4000) as CommandText;
  END; 
  END CATCH ;
      set @i = @i+1;
  end;
END; 

GO 

-- 測試 
EXEC P_TEST_TO_EBS  21,30  ;

　　

創建 日誌表