반응형

원격지 서버의 테이블에 대한 갱신 작업을 위해서 다음과 같이 Linked Server의 테이블을 JOIN 하여 UPDATE 하였습니다.

SET XACT_ABORT ON;

DECLARE @ano VARCHAR(30) = 'S201910250010'

BEGIN DISTRIBUTED TRAN

UPDATE Trgt
SET
    AcptYn = 'N',
    AcptEmpCode = '', 
    AcptDeptCode = '',
    AcptDateTime = NULL
FROM
    RemoteServer.RemoteDB.dbo.FIAccSlipDisappMaster AS Trgt
        JOIN RemoteServer.RemoteDB.dbo.FIAccSlipAggreationNo AS A
            ON a.SlipNo = Trgt.SlipNo
            AND A.AggregationNo = @ano

SET XACT_ABORT OFF;

COMMIT

그러나 동작은 잘 되지만 성능이 너무 나오지 않았습니다.

실행계획을 보니 원격지 테이블을 모두 읽어와서 JOIN 시키다 보니 실제 UPDATE 시킬 행은 1개 인데 수십만 건의 데이터를 읽고 있었습니다.

그래서 OpenQuery로 변경 하려고 했으나, 문제는 OpenQuery의 SQL문이 순수한 문자열만 가능 하도록 되어 있어서 파라미터(변수)를 사용할 수가 없었습니다.

SET XACT_ABORT ON;

DECLARE @ano VARCHAR(30) = 'S201910250010'

BEGIN DISTRIBUTED TRAN

UPDATE x
SET
    AcptYn = ''N'',
    AcptEmpCode = '''', 
    AcptDeptCode = '''',
    AcptDateTime = NULL
FROM
    OpenQuery (RemoteServer,
        '
        SELECT
            Trgt.*
        FROM RemoteDB.dbo.FIAccSlipAggreationNo AS A
            JOIN RemoteDB.dbo.FIAccSlipDisappMaster AS Trgt
                ON a.SlipNo = Trgt.SlipNo
        WHERE
            A.AggregationNo = ''' + @ano + '''
        '
    ) x

SET XACT_ABORT OFF;

COMMIT

그래서 최종적으로 OpenQuery를 포함한 전체 쿼리 문장을 문자열 변수에 넣고 EXEC()로 처리 하였습니다.

SET XACT_ABORT ON;

DECLARE @ano VARCHAR(30) = 'S201910250010'
DECLARE @sql varchar(max)

BEGIN DISTRIBUTED TRAN

set @sql = 
    '
    UPDATE x
    SET
        AcptYn = ''N'',
        AcptEmpCode = '''', 
        AcptDeptCode = '''',
        AcptDateTime = NULL
    FROM
        OpenQuery (RemoteServer,
            ''
            SELECT
                Trgt.*
            FROM RemoteDB.dbo.FIAccSlipAggreationNo AS A
                JOIN RemoteDB.dbo.FIAccSlipDisappMaster AS Trgt
                    ON a.SlipNo = Trgt.SlipNo
            WHERE
                A.AggregationNo = ''''' + @ano + '''''
            ''
        ) x
    '
EXEC(@sql)

SET XACT_ABORT OFF;

COMMIT

성능도 좋고, 파라미터(변수)도 처리할 수 있어서 만족할 만한 결과가 나왔습니다.
원격지 서버에 대한 갱신작업이 필요하실 때 참고 하시기 바랍니다.

반응형
반응형
SQL서버에서 Linked Server로 연결된 Oracle서버에 곧바로 DML 을 던지면 오류가 발생 한다.

 


다음과 같이 해야 오류가 발생 하지 않는다.

INSERT openquery(linked1, 'select num, names from Temp_1') VALUES (100, '박찬호')

마찬가지로 UPDATE, DELETE도 다음과 같이 할 수 있다.

UPDATE openquery(linked1, 'select num from Temp_1 where num = 100')  SET num = 200

DELETE FROM openquery(linked1, 'select num from Temp_1 where num = 200')
반응형
반응형

1. Oracle Client 를 설치 및 TNS 설정(tnsnames.ora)

2. SSMS에서 서버개체>연결된 서버 마우스 오른쪽 버튼> 새 연결된 서버 ... 선택

3. 일반 에서 공급자는 Oracle Provider for OLE DB를 선택, 제품이름 및 데이터원본에는 TNS 명 입력


4. 보안에서 '다음 보안 컨텍스트를 사용하여 연결' 선택 후 ID, PW 입력 후 확인



5. SSMS 에서 서버 개체>연결된 서버>공급자>OraOLEDB.Oracle 속성에서 Inprocess 허용을 check 해 준다.

반응형
반응형

SQL서버에서 Oracle을 연결된 서버로 구성한 후 OpenQuery로 조회를 하면

메시지 9803, 수준 16, 상태 1, 줄 1
"numeric" 유형에 대한 데이터가 잘못되었습니다.


라는 오류가 발생 할 때가 있다.

원인은 Oracle 테이블에서 Type이 int 나 decimal인 컬럼을 사용하게 되면 발생 한다.

연결된 서버 구성시 공급자를 'Oracle Providier for OLE DB'로 지정 했을 때 이런 현상이 있다.

(공급자를 Microsoft OLE DB Provider for Oracle로 했을 때는 어떤 지는 모르겠다.

참고로 연결된 서버로 Oracle 을 추가한 후 공급자 속성에서 반드시 Inprocess허용을 해줘야 된다.)


오류를 해결 하기 위해서는 두가지 정도의 방법이 있는 것 같다.

첫번째는 OpenQuery 파라미터로 넘기는 쿼리에서 int 나 decimal 타입의 컬럼은 모두 to_number() 또는 to_char()로 형변환을

해서 SELECT를 하는 방법이고, 두번째는 컬럼 타입을 NUMBER 또는 FLOAT 로 변경 하는 방법이다.

단 첫번째 방법을 사용하면 SELECT 인 경우에는 문제가 없으나 해당 컬럼에 대하여 INSERT, UPDATE 할때

오류가 발생 한다.


연결된 서버 "hr"의 OLE DB 공급자 "OraOLEDB.Oracle"이(가) 메시지 "ROW-00008: Cannot update data in a read-only column"을(를) 반환했습니다.
메시지 7343, 수준 16, 상태 4, 줄 1
연결된 서버 "hr"의 OLE DB 공급자 "OraOLEDB.Oracle"이(가) 테이블 "[OraOLEDB.Oracle]"을(를) UPDATE할 수 없습니다.
반응형

+ Recent posts