블로그 이미지
따뜻한 코드 만들기 리본매냐

카테고리

분류 전체보기 (34)
.NET (6)
Database (5)
Daily life (11)
세미나 후기 (0)
마음의 지식 (7)
코드 (1)
Python (1)
외출 (3)
Total41,953
Today17
Yesterday20

달력

« » 2019.8
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31

공지사항

태그목록

SQL 원격디비 연결

Database / 2011.03.15 00:15


원격디비 연결을 하기 위한 몇가지 방법
1. Openrowset

Syntax
 
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
   | 'provider_string' }
   , {   [ catalog. ] [ schema. ] object
       | 'query'
     }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , MAXERRORS = maximum_errors ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]

>> SQL Server Native Client OLE DB Provider
      SELECT a.*
      FROM OPENROWSET('SQLNCLI',
                                      'Server=Seattle1;
                                      Trusted_Connection=yes;',
                                      'SELECT GroupName, Name, DepartmentID
                                       FROM AdventureWorks2008R2.HumanResources.Department
                                       ORDER BY GroupName, Name'
) AS a;
 
>> Microsoft OLE DB Provider For Jet
    SELECT CustomerID, CompanyName
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                   'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
                   'admin';'',Customers);

>> Inner Join

USE Northwind  ;
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
   INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)   
   AS o
   ON c.CustomerID = o.CustomerID ;
GO

>> Bulk insert file data into a varbinary(max) column
USE AdventureWorks2008R2;
GO
CREATE TABLE myTable(FileName nvarchar(60),
  FileType nvarchar(60), Document varbinary(max));
GO

INSERT INTO myTable(FileName, FileType, Document)
   SELECT 'Text1.txt' AS FileName, '.txt' AS FileType, *
   FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO

>> From a txt file

USE AdventureWorks2008R2;
GO
CREATE TABLE myTable(FileName nvarchar(60),
  FileType nvarchar(60), Document varbinary(max));
GO

INSERT INTO myTable(FileName, FileType, Document)
   SELECT 'Text1.txt' AS FileName,
      '.txt' AS FileType,
      * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO

이런 식으로 해주면 된다~
아래와 같이 미리 설정이 되어있어야한다..




2.  OpenQuery (LinkedServer 추가)

1) Linkedserver를 추가한다.

exec sp_addlinkedserver
@server = 'drcs',
@srvproduct = '',
@provider = 'SQLOLEDB',
@datasrc = '111.111.111.1',
@provstr='',
@catalog= ''

2) 로그인
sp_addlinkedsrvlogin 'drcs', 'false', null, 'ID', 'passwords'

3) 쿼리 
select * from [drcs].drcs.dbo.customer
OpenRowSet를 이용하는 것 보다 쿼리가 간편해진다.
Posted by 리본매냐

최근에 달린 댓글

최근에 받은 트랙백

글 보관함