서로 다른 DB의 내용 Join 하기 (sp_addLinkedServer 만들기)

2007. 12. 4. 14:40 개발언어/SQL

요약하자면~ 이렇게
[CODE]
----------------------------------- 서버생성하기
EXEC sp_addlinkedserver 
       @server='xpt_Server_A',  -- 앞으로 사용할 링크드 서버이름입니다.
       @srvproduct = '', -- 공백처리 합니다.
       @provider = 'SQLOLEDB', -- SQL 서버이면 그대로 씁니다.
       @datasrc = '11.10.111.111', -- 아이피 적어 줍니다.       
       @provstr='',   -- 공백처리 합니다.
       @catalog='xpt_upgrade_A'   -- 특정 카다로그 즉 데이터 베이스 이름을 적습니다.
GO

---------------------------------- 로그인추가하기
EXEC sp_addlinkedsrvlogin 'xpt_Server_A', 'false', NULL, 'id', 'pwd' 
-- 로그인을 만듭니다.
-- MemDB 라는 서버에 MemID 라는 유저아이디로 MemPW 의 암호를 갖는
-- 유저를 생성하라는 명령입니다.

---------------------------------- 원격쿼리날리기
select top 10 * from [xpt_Server_A].[xpt_upgrade_A].[dbo].[t_upgradelist]

---------------------------------- 원격로그인 삭제하기
exec sp_droplinkedsrvlogin @rmtsrvname = 'xpt_Server_A' , @locallogin = NULL

---------------------------------- 원격서버 삭제하기
exec sp_dropserver @server = 'xpt_Server_A'
[/CODE]

아래는 갈무리하것!

연결된 서버(Linked Server) 기능을 이용하면, Remote의 MS-SQL, Microsoft OLE DB Provider for ODBC,
Microsoft OLE DB Provider for Oracle 및 Microsoft OLE DB Provider for Indexing Service 를 이용
서로 다른 Engine간의 연결도 가능합니다.
구체적인 연결방법은 각각의 Engine별로 약간 차이가 있습니다. Books Online을 참조하세요.


Step 0. DB NETWORK 환경 설정
MS-SQL Server가 운영중인 서버 Local의 "SQL Serevr클라이언트 네트워크 유틸리티"를 이용 Remote MS-SQL Server를 등록한다.
MS-SQL에서 Remote Oracle을 연결하는 경우 MS-SQL Server가 설치된 Server에 Oracle Client가 설치 되어야 합니다.
 

가장 간당한 Remote MS-SQL을 연결하는 방법은 다름과 같습니다.
(아래의 작업은 EM에서도 가능 합니다. )


-- Step 1. 연결된 서버 추가
-- sp_addlinkedserver 를 사용 합니다.
-- Books Online의 sp_addlinkedserver 를 참조하세요. 다양한 이기종 DB에 연결하는 예가 있습니다.

USE master
go
-- Remote DB ProductDBMS_AS-IS(SQL Serevr클라이언트 네트워크 유틸리티 의 서버 별칭) 을
--  linkedserver명 LINK_REMOTE 로 생성하는 경우
EXEC sp_addlinkedserver @server='LINK_REMOTE', @srvproduct='', @provider='SQLOLEDB', @datasrc='ProductDBMS_AS-IS'
go

-- 연결된 서버 생성 확인
exec sp_linkedservers


-- Step 2. 로그인(연결에 사용하는 Remote MS-SQL Server 계정) 의 설정
-- sp_addlinkedsrvlogin 를 사용 합니다.
-- LINK_REMOTE 에 접속시 사용할 계정 설정 Login = RLogin , Password = RLoginPasswd 인 경우
EXEC sp_addlinkedsrvlogin 'LINK_REMOTE', 'false', NULL, 'RLogin', 'RLoginPasswd'


-- Step 3. Linked server를 이용한 SQL 사용
-- SQL의 사용시 4 Part Name (Full Qualified Name) 을 사용해야 합니다.
-- [server or instance name].[db name].[owner name].[object name]
select count(*) from [LINK_REMOTE].[dbname].[dbo].[tablename]


------------------------------------------------------------------------------------
-- 참조 구성을 삭제하는 경우
-- 로그인 삭제
exec sp_droplinkedsrvlogin @rmtsrvname = 'LINK_REMOTE' , @locallogin = NULL
 
-- 연결된 서버 삭제
exec sp_dropserver @server = 'LINK_REMOTE'


-------------------------------------------------------------------------------------


아래는 MS-SQL Linked Server를 이용한 이기종 DBMS 연결 하는 경우 입니다.


1. MS-SQL --> Oracle 연결
------------------------------------------------------------------------------------------------------------------
http://support.microsoft.com/kb/280106/ko

--------------------------------------------------------------------------------------------
--- Case 1. MS Oracle OLD DB Driver
--------------------------------------------------------------------------------------------
-- LINKED SERVER 생성

EXEC sp_addlinkedserver   'ECTEST_MS_Dr',  'Oracle',  'MSDAORA',  'ECTEST.INCEC.COM'
-- ECTEST.INCEC.COM ==> Local TNSNAME.ORA에 정의된 이름

EXEC sp_addlinkedsrvlogin 'ECTEST_MS_Dr', 'FALSE',NULL, 'mgro', 'mgro'

sp_tables_ex  @table_server='ECTEST_MS_Dr', @table_schema='EMP'

select * from [ECTEST_MS_Dr]..[MGRO].[EMP]

-- LINKED SERVER 삭제
EXEC sp_dropserver 'ECTEST_MS_Dr' , 'droplogins'


--------------------------------------------------------------------------------------------
--- Case 2. Oracle OLD DB Driver
--------------------------------------------------------------------------------------------
-- LINKED SERVER 생성

EXEC sp_addlinkedserver   'ECTEST_oracle_Dr',  'Oracle',  'OraOLEDB.Oracle',  'ECTEST.INCEC.COM'

EXEC sp_addlinkedsrvlogin 'ECTEST_oracle_Dr', 'FALSE',NULL, 'mgro', 'mgro'

-- LINKED SERVER 삭제
EXEC sp_dropserver 'ECTEST_oracle_Dr' , 'droplogins'


------------------------------------------------------------------------------------------------------------------
-- Help on the linked server:
-------------------------------------------------------------------------
EXEC sp_linkedservers
EXEC sp_helpserver
select * from sysservers

-----------------------------------------------------------------------------------------------
-- DML
-----------------------------------------------------------------------------------------------
-- Linked Server 확인
EXEC sp_linkedservers

-- Oracle Select
SELECT COUNT(*) FROM [linkedserver]..[owner].[table_name]