MSSQL 2005 new Feature - OPENROWSET

어느날 SQL Server 2000보다 SQL Server 2005가 좋은것이 무엇무엇이 있지? 누군가가 나에게 "왜 SQL Server 2000보다 SQL Server 2005가 좋아요?" 라고 물으면 어떻게 대답해야 하지? "그래 아직 내 머릿속에 이런것들이 정리가 되어 있지 않는구나" 라는 생각에 회사 팀원들과 하나하나 정리하면서 간략한 세미나형식으로 발표를 하기로 하였다. 그 첫번재 주제가 OPENROWSET 이다.

OPENROWSET이란?
첫번째, 링크드서버로 연결되어 있지 않은 SQL Server에서 데이터를 읽을 때 사용한다.
두번째, 특정 파일의 데이터를 Table로 로드시키지 않고 내용을 읽는다.

여기서 잠깐!! 무작정 OPENROWSET을 사용하면 에러가 발생을 한다.
메시지 15281, 수준 16, 상태 1, 줄 1
구성 요소 'Ad Hoc Distributed Queries'이(가) SQL Server 보안 구성의 일부로 해제되었으므로 이 구성 요소의 문 'OpenRowset/OpenDatasource'에 대한 액세스가 차단되었습니다. 시스템 관리자는 sp_configure를 사용하여 'Ad Hoc Distributed Queries'의 사용을 활성화할 수 있습니다. 'Ad Hoc Distributed Queries' 활성화 방법은 SQL Server 온라인 설명서의 "노출 영역 구성"을 참조하십시오.
이와 같은 에러이다. OPENROWSET을 사용하기 전에 SQL Server 서버 구성을 살짝 바꾸자.

exec sp_configure 'show advanced options', 1
RECONFIGURE

exec sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

이렇게 구성요서를 바꾸면 일딴 OPENROWSET을 사용하기 위한 준비가 된것이다. 여기서 우리는 MSDASQL provider을 사용해서 연결해 보자.

SELECT o.* 
FROM OPENROWSET(
	'MSDASQL'
,	'DRIVER={SQL Server};SERVER=192.168.*.*;UID=****;PWD=****'
,	'select * from TESTDB.dbo.test') o

이렇게 하면 링크드서버를 연결하지 않고도 원격서버의 데이터를 읽을 수 있다. 하지만, 해당 데이터를 빈번하게 참조해야한다면 링크드서버로 연결하는게 당연히 좋겠다.

다음으로 어떠한 File Data을 Table에 로드시키지 않고 바로 읽는것을 보겠다. 우리는 2가지 방법을 통해서 볼 것이다. 첫번째 방법은 OLE DB을 사용해서 읽는 방법과 두번째 방법은 BULK 옵션으로 보는 방법이다.
먼저, OLE DB를 사용하는 방법 부터 보자. 방법을 보기전에 TEST용 File먼저 만들자. *^^* 메모장을 열고 "이승연","1" 이렇게 적고 .txt파일로 저장을 하자 그리고 아래와 같은 Query문을 실행시키자.

select * from
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,	'text;Database=C:\;HDR=NO'
, 'select * from test.txt')

select * from
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,	'text;Database=C:\;HDR=YES'
,	'select * from test.txt')

이렇게 위와 같이 실행을 하고 윗 Query와 아래 Query을 비교하여 보자. HDR=NO와 HDR=YES 차이다. HDR=NO을 하면 첫번째 행부터 ROW에 출력이 되고, HDR=YES을 하면 첫번째 행은 필드명으롤 올라간다.

덤으로 MSDASQL문도 보자.

SELECT a.* FROM
OPENROWSET('MSDASQL', 
'Driver={Microsoft Text Driver (*.txt; *.csv)};  DefaultDir=C:\;',
'SELECT * FROM test.txt') a

이제 마지막으로 BULK문을 보도록하자. MSDN에서는 OPENROWSET(BULK..) 와 INSERT을 같이 사용할 경우 BULK절에 테이블 힌트를 사용할수 있다고 기술되어 있다. 또한 TABLOCK과 같은 일반적인 힌트 외에도 IGNORE_CONSTRAINTS, IGNORE_TRIGGERS, KEEPDEFAULTS, KEEPIDENTITY와 같은 특수 힌트도 사용할수 있다고는 하지만, 여기에서는 언급하지 않겠다. (나도 잘 모르니깐요 -0-;;) BULK문을 사용하기 전에 FORMAT형식의 XML을 만들자.

<?xml version="1.0"?>




<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 




xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">




 <RECORD>




  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/>




  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/>




 </RECORD>




 <ROW>




  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLNVARCHAR"/>




  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>




 </ROW>




</BCPFORMAT>

해당 XML코드를 설명해주지 않아도 다들 알고 있을거라 믿어 의심치 않는다. (나도 잘 모른다. -0-;;) 아무튼 해당 XML코드를 TEST.xml로 저장하고 다음 Query을 실행시키면 된다.

SELECT Col1, Col2
	FROM  OPENROWSET(BULK  'C:\test.txt',
	FORMATFILE='C:\test.Xml'  
) AS t1;

다음에는 향상된 분산쿼리에 대해서 포스팅을 할것이다. 음.. 확장된 분산쿼리는 도대체 멀까나 -0-;;