70세 코딩

어떤 테이블을 삭제하려고 할 때 "FOREIGN KEY 제약 조건에서 참조하므로 삭제할 수 없습니다" 라는 오류가 나면서

삭제가 안되는 경우가 있다. 이는 해당 테이블을 외래키로 참조하는 다른 테이블이 존재 하기 때문 이다.

그런데 삭제 하려는 테이블 자체에 대한 외래키 제약조건은 sp_helpconstraint 로 확인 할 수 있으나,

해당 테이블을 참조하는 다른 테이블은 sp_helpconstraint 로는 알 수가 없다.

이럴때는 다음과 같이 하면 참조하고 있는 다른 테이블과 연결된 컬럼명을 확인 할 수 있다.

SELECT 
   f.name,
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = '테이블명'

SQL서버에서 링크드 서버와의 분산 트랜잭션을 처리하기 위한 과정을 정리 합니다.

양쪽 서버에서 제대로 설정이 되어 있지 않은 경우 아래와 같은 오류가 발생 한다.

연결된 서버 "XXXX"의 OLE DB 공급자 "SQLNCLI10"이(가) 분산 트랜잭션을 시작할 수 없으므로 요청한 작업을 수행할 수 없습니다.

1. 만약 Windows서버가 2003이면 프로그램추가삭제 > Windows구성요소 추가/제거 에서 응용프로그램서버(Application Server)를 선택하고  '자세히' 버튼을 누른 후, 네트워크 DTC 액세스 사용(Enable network DTC access)을 설치한다.

시작메뉴 > 실행에서 regedit 를 입력하여 레지스트리 편집기에서
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSTDC 에서 TurnOffRpcSecurity 키의 값을 1로 해 준다.

서비스 에서 'Distributed Transaction Coordinator'를 다시 시작 한다.

2. 만약 Windows서버가 2008 이상 이면 다음과 같이 한다.

    ① 시작메뉴 > 실행에서 dcomcnfg를 입력한 다음 확인
    ② 구성 요소 서비스 > 컴퓨터 > 내 컴퓨터 > Distributed Transaction Coordinator > 로컬 DTC 선택
    ③ 로컬 DTC를 마우스 오른쪽 단추로 클릭하고 속성 선택
    ④ 로컬 DTC 속성 창이 나타나면 보안 탭을 클릭 후 다음과 같이 설정

3. 만약 Windows 방화벽을 사용하고 있다면 Distributed Transaction Coordinator 를 예외로 두어야 한다.

4. SQL 상에서는 반드시  SET XACT_ABORT ON 으로 세팅을 하고 begin tran 대신 begin DISTRIBUTED tran 을 사용 해야 한다.

5. 만약 상대 DB가 Oracle 인 경우는 위과 같이 해도 안 될 수 있는데 , 이때는 레지스트리에서 다음과 같이 수정 해 준다.

레지스트리 경로는 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI 이다.
기본적으로 OracleSqlLib와 OracleXaLib가 각각 SQLLib80.dll, xa80.dll로 되어 있는데 이를 설치된 Oracle의 버전에 따라
변경 해 준다.

(변경 전)

(예: Oracle 10인 경우)           

이 글이 도움이 되셨으면 댓글을 남겨 주세요!

드래그 드롭으로 간단히 워드, 액셀, 파워포인트 파일을 PDF 문서로 변환이 가능하다고 합니다.

http://smallpdf.com/kr

아래와 같이 SQL서버에서 동적쿼리에 대한 파라미터를 쉽게 전달 할 수 있는 방법이 있다.

이것을 몰랐을 때는 일일이 문자열을 조합, 편집 하느라 매우 애를 먹었는데 간결하게 사용이 가능하다.

DECLARE
    @sql  nvarchar(max),
    @params  nvarchar(max)


SET @Sql = 'select * from InvoiceMaster
            where companyseq = @p_companyseq and invoicedate like @p_invoicedate + ''%'''
 

SET @params = '@p_companyseq int, 
            @p_invoicedate nchar(8)'
 

EXECUTE sp_executesql @sql, 
                      @params, 
                      @p_companyseq = 1,
                      @p_invoicedate = '20140707'