MSSQL 2005 new Feature - 향상된 분산쿼리

안녕하세요. 제가 정리하게되는 2번째 포스팅입니다. 첫번째는 2009/10/26 - [MSSQL/SQL Server 2005 new Feature] - MSSQL 2005 new Feature - OPENROWSET 이였고요. 2번째는 향상된 분산쿼리는 제목으로 포스팅을 하게되었습니다. 그럼 향상된 분산쿼리란 무엇이냐? 저도 잘 몰르겠고요 -0-;; 그래서 찾아보았죠 -0-;;

향상된 분산쿼리는 말그대로 EXECUTE 기능이 조금 향상이 되었다는 거... 어떻게? 좀더 원활하게 자기가 원하는 Query를 원격 서버로 전송할 수 있다는거... 예를 들어서 A라는 SQL Server에서 B라는 SQL Server로 CREATE TABLE 구문을 날리고 싶어요. 그럼 어떻게 하죠 ?? 여러분들도 아시다시피 B라는 SQL Server서버에 CREATE TABLE구문을 넣은 procedure을 만들고, A라는 SQL Server에서 그 프로시져를 실행을 시켰죠?? 그죠?? 근데 우리의 SQL Server 2005에서는 그 기능들이 향상되었다고 하네요 *^^*

일반적으로 Linked Server에 있는 Data을 Query을 할때 4구문 [링크드서버이름].[데이터베이스].[스키마].[테이블] 방식으로 해야 합니다.

--########################################################
-- 일반적인 EXEC 사용법
--########################################################
select * from LEEPC.TESTDB.dbo.test where idx = 1
EXEC ('select * from LEEPC.TESTDB.dbo.test where idx = 1')

위와 같은 방법이 일반적인 방법입니다. 또는 Linked Server의 procedure을 호출할때는 EXEC [링크드서버].[데이터베이스].[스키마].[프로시져] 이렇게 사용을 할 것 같아요.

우리는 지금 EXECUTE의 일반적인 사용방법을 보고 있는데요. 저장프로시져이름을 변수로 받고 싶다고 하면 다음과 같이 하면 될 것 같아요.

--########################################################
-- 저장프로시져와 함께 EXECUTE 사용
--########################################################
DECLARE @proc_name VARCHAR(30);
SET @proc_name = 'sys.sp_who'
EXEC @proc_name

위의 예제는 MSDN에서 발취해 온 예제에요. 그럼 여기서 우리가 보게 될 내용인 향상된 분산쿼리 기능이란 도대체 무엇일 까요? MSDN의 기록되어 있는 말을 그대로 적자면, EXECUTE 구문에서 이제 연결 서버의 저장 프로시져를 실행하도록 지정하는 AT LinkedServer절을 지원한다고 기록되어 있어요. 그럼 AT LinkedServer절이 어떻게 사용되는지 직접 보자구요.

지금 현재 SQL Server에 LEEPC라는 LinkedServer가 설정되어 있다는 가정하에 다음 예제 Query을 보자고요.

--########################################################
-- AT Linked Server name
--########################################################
EXEC ('CREATE TABLE TESTDB.dbo.TEST_2 (idx int, name varchar(10));') AT LEEPC

헐... LEEPC Server에 CREATE TABLE구문을 날리고 있는것 같아요. 앞서 말한거와 같이 위와 같이 원격 서버에 테이블을 만드려면 다른 방법을 써야 했었는데 이제 권한만 있으면 AT LinkedServer 구문으로 다 할 수 있을 것 같아요. *^^* 다음 예제는 AT LinkedServer구문을 이용해서 Oracle에 Query을 전달하는 방법을 보게 될꺼에요. 물론, 해당 예제는 MSDN 에 있는 예제에요.

--########################################################
-- Oracle database query
-- http://msdn.microsoft.com/ko-kr/library/ms188332.aspx
--########################################################
-- Setup the linked server.
EXEC sp_addlinkedserver  
        @server='ORACLE',
        @srvproduct='Oracle',
        @provider='OraOLEDB.Oracle', 
        @datasrc='ORACLE10';

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname='ORACLE',
    @useself='false', 
    @locallogin=null, 
    @rmtuser='scott', 
    @rmtpassword='tiger';
 
EXEC sp_serveroption 'ORACLE', 'rpc out', true;
GO
 
-- Execute several statements on the linked Oracle server.
EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', 7902) AT ORACLE;
GO
DECLARE @v INT; 
SET @v = 7902;
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', @v) AT ORACLE;
GO

어라.. 눈치 빠르신분들은 위 예제에서 색다른 기법이 나왔다는 것을 발견했을 거에요. 바로 변수 값 넘기기에요. 아래 예제를 보면 바로 "아하" 하실 꺼에요.

--########################################################
-- execute as user
--########################################################
DECLARE @int INT
SET @int = 1
EXEC ('SELECT * FROM TESTDB.dbo.TEST WHERE idx = ?', @int) AT LEEPC

"아하~!" 위와 같은 형태로 값도 자유자재로 보낼 수가 있어요. 참 쉽죠 ~ ! (이 포스팅을 하기 전에는 몰랐다는 ㅋㅋㅋ) 마지막으로 로그인한 사용자가 SELECT권한이 부여되어 있지 않아 SELECT을 하지 못할때 SELECT 권한이 있는 사용자인것 처럼 해서 실행을 할 수가 있죠?? 예 있습니다. *^^* 그것을 바로 사용자 컨텍스트 전환이라고 하는데요. AS 구문으로 그렇게 전환을 할 수 가 있습니다.

--########################################################
-- execute as user
--########################################################
EXECUTE ('CREATE TABLE TESTDB.dbo.TEST_2 (idx int, name varchar(10));') AS USER = USER1

설명을 SELECT로 했는데 예제는 CREATE TABLE구문이네요 *^^*;
이렇게 SQL Server 2005에서는 좀 더 다양하게 구문을 실행할 수 있다고 하네요.