반응형

/********************************************************************************************
-- Title : [2k8] 오류 CATCH위한 sp_error_input 시스템 프로시저 등록
-- Key word : sp_error_input, sp_MS_marksystemobject 시스템프로시저 시스템 프로시저
********************************************************************************************/

-- dbo.sp_error_input 프로시저 유무 확인
select *
from master.sys.procedures
where name = 'sp_error_input';

 

-- 기 프로시저 없을 시 설치
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_error_input]    Script Date: 2013-05-30 오후 2:02:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[sp_error_input]
AS
SET NOCOUNT ON;
 
DECLARE @error_servername             SYSNAME;
DECLARE @error_dbname                 SYSNAME;
DECLARE @error_login_account  SYSNAME;
DECLARE @error_number                 INT;
DECLARE @error_severity                      INT;
DECLARE @error_state                  INT;
DECLARE @error_procedure              SYSNAME;
DECLARE @error_line                          INT;
DECLARE @error_message                NVARCHAR(4000);
 
SELECT  @error_servername             =       @@SERVERNAME,
               @error_dbname                 =       DB_NAME(),
               @error_login_account   =       SYSTEM_USER,
               @error_number                 =       ERROR_NUMBER(),
               @error_severity                       =       ERROR_SEVERITY(),
               @error_state                  =       ERROR_STATE(),
               @error_procedure              =       ISNULL(ERROR_PROCEDURE(),N'-'),
               @error_line                           =       ERROR_LINE(),
               @error_message                =       ERROR_MESSAGE();
 
IF @error_number IS NULL
        RETURN;
 
INSERT INTO
        OPENQUERY
        ([linked_test],
        'SELECT
        err_server,
        err_db,
        err_login_ac,
        err_num,
        err_sv,
        err_st,
        err_proc,
        err_line,
        err_msg                      
        FROM metadb.sch_meta.m_proc_err_cntnt
        WHERE 1=0')
VALUES
        (@error_servername,
        @error_dbname,
        @error_login_account,
        @error_number,
        @error_severity,
        @error_state,
        @error_procedure,
        @error_line,
        @error_message);
 
SELECT @error_message =
                              N'Error:%d, Level:%d, State:%d, Procedure:%s, Line:%d, ServerName:%s, DBName:%s, Login Account:%s, ' +
                              N'Message:'+ @error_message;
 
RAISERROR
               (
               @error_message,
               @error_severity,
               1,              
               @error_number,
               @error_severity,
               @error_state,
               @error_procedure,
               @error_line,
               @error_servername,
               @error_dbname,
               @error_login_account
               );
GO

 

-- 시스템프로시저로 등록
EXEC sp_configure N'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
 
EXEC sp_MS_marksystemobject '[dbo].[sp_error_input]';
 
 
EXEC sp_configure N'allow updates', 0;
RECONFIGURE WITH OVERRIDE;

 

-- PUBLIC 롤 허용
USE [master]
GO
 
GRANT EXECUTE ON [dbo].[sp_error_input] TO [public]
GO

 

 

 

반응형
저작자표시 비영리 변경금지

+ Recent posts

티스토리툴바