SQL 2000에서 SQL 2005로 데이터 이관 시 'STREAM' 에러

SQL 2000에서 SQL 2005로 데이터 이관 시 'STREAM' 에러

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

 

SQL Server 2000에서 2005로 데이터를 전송하는 과정에서 다음과 같은 에러가 발생하는 경우가 있습니다.

 

DTS를 이용해서 SQL 2000에서 2005로 보내는 경우

 

2005 데이터 가져오기/내보내기를 이용해서 처리하는 경우

 

이와 같은 현상은 원본 데이터에서 잘못된 데이터가 원인이며, SQL 2000에서는 이 경우에 에러를 발생시키지 않았지만, SQL 2005의 빠른 로드를 사용하는 방식에서는 에러를 발생합니다.

 

 

1. 상황 재연

잘못된 데이터를 만들기 위해 Hex 수정 기능이 있는 텍스트 에디터를 이용해서 다음과 같이 텍스트 데이터를 입력합니다.

Hex Editor를 이용해서 다음과 같이 임의의 값을 추가합니다.

추가 후에는 다음과 같이 ?와 같은 특수 기호가 나타납니다.

 

 

 

2. 테스트 테이블 생성 및 입력

쿼리 분석기에서 임의의 테이블을 생성하고 위의 텍스트 파일을 Bulk Loading 시킵니다.

USE TEMPDB GO   CREATE TABLE Source (                   ColData varchar(20) ) GO   BULK INSERT Source FROM 'd:\SampleData.txt' GO

 

입력된 데이터의 바이트 수를 확인합니다.

SELECT ColData,  DATALENGTH(ColData) FROM Source GO

 

 

 

3. 데이터 페이지 확인

다음과 같은 방식으로 실제 입력된 데이터를 확인합니다.

DBCC TRACEON(3604) GO   DBCC IND('tempdb','Source',0) /* 1               33             NULL       NULL       213575799              0               10             0               0               0                  0               0 1               31             1               33             213575799              0               1               0               0               0                  0               0 */   DBCC PAGE(tempdb, 1, 31, 3) /* lot 0 Offset 0x60 ------------------ Record Type = PRIMARY_RECORD                        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   4312E060:  00040030  01000001  4e001500  616d726f 0..........Norma 4312E070:  7461446c        61                     lData ColData                          = NormalData         Slot 1 Offset 0x75 ------------------ Record Type = PRIMARY_RECORD                        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS   4312E075:  00040030  01000001  42001600  656b6f72 0..........Broke 4312E085:  7461446e      c561                     nData. ColData                          = BrokenData       */

 

 

4. 이 테이블을 이용해서 SQL 2000에서 2000, 2005로 가져오기/내보내기 테스트를 수행합니다.

 

 

 

 

 

결과1) SQL 2000 à SQL 2000

에러 없이 정상적으로 처리되며, 잘못된 데이터라도 동일하게 유지됩니다.

 

결과2) SQL 2000 à SQL 2005 : SQL 2000의 데이터 가져오기/내보내기 or DTS 패키지 이용

-         데이터 가져오기/내보내기 또는 DTS 패키지는 빠른 로드 옵션이 적용되어 처리됩니다.

 

이 경우, 에러가 발생합니다.

 

 

위의 그림에서 빠른 로드 사용 옵션 체크를 해제하면 정상적으로 처리됩니다.

 

 

결과3) SQL 2000 à SQL 2005 : SQL 2005의 데이터 가져오기/내보내기 or SSIS 패키지 이용

-         2000에서와 마찬가지로 빠른 로드 옵션이 적용되어 처리되며, 옵션이 설정된 경우 역시 에러가 발생합니다.

 

SQL 2005의 경우에도 데이터 가져오기/내보내기는 다음과 같이 빠른 로드 옵션이 적용되어 처리됩니다.

      

2000에서와 같이 “테이블 또는 뷰 – 빠른 로드” 대신 “테이블 또는 뷰”로 변경해서 처리하면 정상적으로 처리됩니다.

 

 

 

[결론]

1. SQL 2005에서 Bulk Insert와 관련되어 내부적(??)으로 처리 방식이 변경되어 위와 같은 현상이 발생하는 것 같습니다. 참고로 동일한 데이터에 대해서도 Bulk Insert시 차이가 있을 수도 있습니다.

             SQL 2000

            

            

             SQL 2005

 

2. SQL 2000에서 SQL 2005로 데이터를 옮기는 과정에서 위와 같은 에러가 발생할 경우, 패키지로 저장한 후, 빠른 로드 옵션을 제거해서 수행하거나, 잘못된 데이터를 변환한 후 수행합니다.

 

송혁 이러한 문제의 원인은 SQL Server 2000과 2005의 orphaned DBCS lead byte의 유효성 체크에 대한 차이로 발생 할 수 있는것으로 알고 있습니다. SQL Server 2000에서는 데이타가 들어오는 과정에서 모든 경우에 체크를 하는 것이 아닌, 변환 혹은 문자열 작업에 의해 orphaned DBCS lead byte가 제거되게 됩니다. 하지만 데이타의 타입이 같은 경우 어떤 체크 없이 잘못된 데이터가 그대로 SQL Server 2000로 입력되어 지게 됩니다. SQL Server 2005의 경우는 이러한 데이터 유효성 체크가 강화되어 2000의 잘못된 데이터를 2005로 전송시 오류가 발생하게 됩니다. 이러한 문제에 대한 임시 방편으로는 현재의 데이터 타입과 다른 타입으로 변환하여 업데이트 하는 방법도 있습니다. 만약 char(10)타입이었다면 varchar(10)으로 변환하면 orphaned DBCS lead byte가 제거되어 동일한 문제가 발생하지 않습니다. 2007/07/27

김태환 DBCC TRACEON(-1, 7307)을 통해서도 해결 가능합니다. ^^; 7307 : TEMP QP\QE\gargisur Fix up data while validating data length of remote columns 2007/12/13