본문 바로가기
DataBase/MS-SQL

OPENROWSET

by Dev. Jkun 2013. 5. 29.
반응형

어느날 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-;;

 

추처 :: http://ddoung2.tistory.com/155

반응형

댓글