SQL Create Linkserver

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

--author:htl258 (Tony)

--Date:2010-06-25 22:23:18

--Version:microsoft SQL Server (RTM)-10.0.1600.22 (Intel X86)

--Jul 9 2008 14:43:34

--Copyright (c) 1988-2008 Microsoft Corporation

--Developer Edition on Windows NT 5.1 <X86> (Build 2600:service Pack 2)

--blog:http://blog.csdn.net/htl258 (reprint retain this information)

--subject:sql data manipulation between different server databases

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

--1. Create a linked server

--1.1 Create a link name

EXEC sp_addlinkedserver ' linkname ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '--with custom instance name plus '/instance name '

/* For example: EXEC sp_addlinkedserver ' tonylink ', ', ' SQLOLEDB ', ' 192.168.58.208 ' */

--1.2 Create login information (or create linked server login mappings) (just choose one way)

--1.2.1 log in as Windows Certified

exec sp_addlinkedsrvlogin ' linkname '--or exec sp_addlinkedsrvlogin ' linkname ', ' true '

/* For example: EXEC sp_addlinkedsrvlogin ' tonylink ' */

--1.2.2 log in as SQL authentication

EXEC sp_addlinkedsrvlogin ' linkname ', ' false ', NULL, ' username ', ' password '

/* For example: EXEC sp_addlinkedsrvlogin ' Tonylink ', ' false ', NULL, ' sa ', ' 123 ' */



--2. Linked server related data operations

--2.1 Query Example

SELECT * from linkname. Database name. Schema name. Table Name

/* For example: SELECT * from TONYLINK.MYDB.DBO.TB */

--2.2 Import Example

SELECT * into table name from Linkname. Database name. Schema name. Table Name

/* For example: SELECT * into NEWTB from TONYLINK.MYDB.DBO.TB */

--2.3 Update Example

UPDATE linkname. Database name. Schema name. Table name SET field = ' value ' WHERE field = ' condition '

/* For example: UPDATE TonyLink.Mydb.dbo.tb SET persons= ' g ' WHERE persons= ' a ' * *

--2.4 Delete Example

DELETE linkname. Database name. Schema name. Table name WHERE field name = ' Condition '

/* For example: DELETE TonyLink.Mydb.dbo.tb WHERE persons= ' g ' */



--3. Using the rowset function (Openquery/openrowset/opendatasource) Action method

--3.1 OPENQUERY method (need to use the linked server you just created):

--3.1.1 Query Example

SELECT * from OPENQUERY (linkname, ' SELECT * from database name. Schema name. Table Name ')

/* For example: SELECT * from OPENQUERY (tonylink, ' SELECT * from MYDB.DBO.TB ') */

--3.1.2 Import Example

--3.1.2.1 Import All Columns

INSERT OPENQUERY (linkname, ' SELECT * from database name. Schema name. Table name ') SELECT * from local surface

/* For example: INSERT OPENQUERY (Tonylink, ' select * from MYDB.DBO.TB ') SELECT * from TB */

--3.1.2.2 Importing a specified column

INSERT OPENQUERY (linkname, ' SELECT * from database name. Schema name. Table name ') (columns, Columns ...)

SELECT column, column ... From local surface

/* For example: INSERT OPENQUERY (Tonylink, ' SELECT * from MYDB.DBO.TB ') (range,level,persons)

SELECT range,level,persons from TB

*/

--3.1.3 Update Example

UPDATE OPENQUERY (linkname, ' SELECT * from database name. Schema name. Table name ') SET field = ' value ' WHERE field = ' condition '

/* For example: UPDATE OPENQUERY (Tonylink, ' SELECT * from MYDB.DBO.TB ') SET persons= ' G ' WHERE persons= ' a ' * *

--3.1.4 Delete Example

DELETE OPENQUERY (linkname, ' SELECT * from database name. Schema name. Table name ') WHERE field name = ' Condition '

/* For example: DELETE OPENQUERY (Tonylink, ' SELECT * from MYDB.DBO.TB ') WHERE persons= ' g ' * *



--3.2 OpenRowset Method (you do not need to use the created link name.) If the instance name of the connection is not the default, you need to add the "/instance name" after "SQL Server name or IP address"

--3.2.1 Query Example

--3.2.1.1 Windows Authentication Mode query (one of the following methods)

SELECT * from OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; Trusted_connection=yes ', database name. Schema name. Table name)

SELECT * from OPENROWSET (' sqlncli ', ' server=sql server name or IP address; Trusted_connection=yes ', database name. Schema name. Table name)

SELECT * from OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; Trusted_connection=yes ', ' SELECT * from database name. Schema name. Table Name ')

SELECT * from OPENROWSET (' sqlncli ', ' server=sql server name or IP address; Trusted_connection=yes ', ' SELECT * from database name. Schema name. Table Name ')

/* For example: SELECT * from OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208; Trusted_connection=yes ', MYDB.DBO.TB)

Or: SELECT * from OPENROWSET (' sqlncli ', ' server=192.168.58.208; Trusted_connection=yes ', MYDB.DBO.TB)

Or: SELECT * from OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208; Trusted_connection=yes ', ' SELECT * from MYDB.DBO.TB ')

Or: SELECT * from OPENROWSET (' sqlncli ', ' server=192.168.58.208; Trusted_connection=yes ', ' SELECT * from MYDB.DBO.TB ')

*/

--3.2.1.2 SQL authentication Mode query (one of the following methods)

SELECT * from OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password ', database name. Schema name. Table name)

SELECT * from OPENROWSET (' sqlncli ', ' server=sql server name or IP address; uid= user name; pwd= password ', database name. Schema name. Table name)

SELECT * from OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password ', ' select * from database name. Schema name. Table Name ')

SELECT * from OPENROWSET (' sqlncli ', ' server=sql server name or IP address; uid= user name; pwd= password ', ' select * from database name. Schema name. Table Name ')

SELECT * from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; User name '; ' Password ', database name. Schema name. Table name)

SELECT * from OPENROWSET (' SQLNCLI ', ' SQL Server name '; User name '; ' Password ', database name. Schema name. Table name)

SELECT * from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; User name '; ' Password ', ' SELECT ' from database name. Schema name. Table Name ')

SELECT * from OPENROWSET (' SQLNCLI ', ' SQL Server name '; User name '; ' Password ', ' SELECT ' from database name. Schema name. Table Name ')

/* For example: SELECT * from OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB)

Or: SELECT * from OPENROWSET (' sqlncli ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB)

Or: SELECT * from OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', ' select * from MYDB.DBO.TB ')

Or: SELECT * from OPENROWSET (' sqlncli ', ' server=192.168.58.208;uid=sa;pwd=123 ', ' select * from MYDB.DBO.TB ')

Or: SELECT * from OPENROWSET (' SQLOLEDB ', ' 192.168.58.208 '; ') Sa '; ' 123 ', MYDB.DBO.TB)

Or: SELECT * from OPENROWSET (' sqlncli ', ' 192.168.58.208 '; ') Sa '; ' 123 ', MYDB.DBO.TB)

Or: SELECT * from OPENROWSET (' SQLOLEDB ', ' 192.168.58.208 '; ') Sa '; ' 123 ', ' SELECT * from MYDB.DBO.TB ')

Or: SELECT * from OPENROWSET (' sqlncli ', ' 192.168.58.208 '; ') Sa '; ' 123 ', ' SELECT * from MYDB.DBO.TB ')

*/

--3.2.2 Import Example

--3.2.2.1 Import All Columns

INSERT OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password ', database name. Schema name. Table name)

SELECT * from local surface

/* For example: INSERT OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB)

SELECT * from TB

*/

--3.2.2.2 Importing a specified column

INSERT OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password ', database name. Schema name. Table name) (column, column ...)

SELECT column, column ... From local surface

/* For example: INSERT OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB) (range,level,persons)

SELECT range,level,persons from TB

*/

--Note: More alternative reference. 2.1 Query example, just replace the contents of the rowset function (OPENROWSET).

--3.2.3 Update Example

UPDATE OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password ', database name. Schema name. Table name)

SET field = ' Value '

WHERE field = ' condition '

/* For example: UPDATE OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB)

SET persons= ' g '

WHERE persons= ' a '

*/

--Note: More alternative reference. 2.1 Query example, just replace the contents of the rowset function (OPENROWSET).

--3.2.4 Delete Example

DELETE OPENROWSET (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password ', database name. Schema name. Table name)

WHERE Field name = ' Condition '

/* For example: DELETE OPENROWSET (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 ', MYDB.DBO.TB)

WHERE persons= ' g '

*/

--Note: More alternative reference. 2.1 Query example, just replace the contents of the rowset function (OPENROWSET).



--3.3 OpenDataSource Method (you do not need to use the created link name.) If the instance name of the connection is not the default, you need to add the "/instance name" after "SQL Server name or IP address"

--3.3.1 Query Example

--3.3.1.1 Windows Authentication Mode query (one of the following methods)

SELECT * from OpenDataSource (' SQLOLEDB ', ' server=sql server name or IP address; Trusted_connection=yes '). Database name. Schema name. Table Name

SELECT * from OpenDataSource (' sqlncli ', ' server=sql server name or IP address; Trusted_connection=yes '). Database name. Schema name. Table Name

/* For example: SELECT * from OpenDataSource (' SQLOLEDB ', ' server=192.168.58.208; Trusted_connection=yes '). Mydb.dbo.tb

Or: SELECT * from OpenDataSource (' sqlncli ', ' server=192.168.58.208; Trusted_connection=yes '). Mydb.dbo.tb

*/

--3.3.1.2 SQL authentication Mode query (one of the following methods)

SELECT * from OpenDataSource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. Schema name. Table Name

SELECT * from OpenDataSource (' sqlncli ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. Schema name. Table Name

SELECT * from OpenDataSource (' SQLOLEDB ', ' Data source=sql server name or IP address; uid= user name; pwd= password '). The database name. Schema name. Table Name

SELECT * from OpenDataSource (' sqlncli ', ' Data source=sql server name or IP address; uid= user name; pwd= password '). The database name. Schema name. Table Name

/* For example: SELECT * from OpenDataSource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB

Or: SELECT * from OpenDataSource (' sqlncli ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB

Or: SELECT * from OpenDataSource (' SQLOLEDB ', ' Data source=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB

Or: SELECT * from OpenDataSource (' sqlncli ', ' Data source=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB

*/

--3.3.2 Import Example

--3.3.2.1 Import All Columns

INSERT opendatasource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. Schema name. Table Name

SELECT * from local surface

/* For example: INSERT opendatasource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB

SELECT * from TB

*/

--3.3.2.2 Importing a specified column

INSERT opendatasource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. Schema name. Table name (column, column ...)

SELECT column, column ... From local surface

/* For example: INSERT opendatasource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB (Range,level,persons)

SELECT range,level,persons from TB

*/

--Note: More alternative reference. 3.1 Query example, just replace the contents of the rowset function (OPENDATASOURCE).

--3.3.3 Update Example

UPDATE opendatasource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. Schema name. Table Name

SET field = ' Value '

WHERE field = ' condition '

/* For example: UPDATE opendatasource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB

SET persons= ' g '

WHERE persons= ' a '

*/

--Note: More alternative reference. 3.1 Query example, just replace the contents of the rowset function (OPENDATASOURCE).

--3.3.4 Delete Example

DELETE opendatasource (' SQLOLEDB ', ' server=sql server name or IP address; uid= user name; pwd= password '). The database name. Schema name. Table Name

WHERE Field name = ' Condition '

/* For example: DELETE opendatasource (' SQLOLEDB ', ' server=192.168.58.208;uid=sa;pwd=123 '). MYDB.DBO.TB

WHERE persons= ' g '

*/

--Note: More alternative reference. 3.1 Query example, just replace the contents of the rowset function (OPENDATASOURCE).



--4. Remove a linked server method

--Delete Link information if you are not using it later

--4.1 Delete login information (or called delete linked server login mapping)

EXEC sp_droplinkedsrvlogin ' linkname ', NULL

/* For example: EXEC sp_droplinkedsrvlogin ' Tonylink ', NULL */

--4.2 removing a linked server name

EXEC sp_dropserver ' linkname ', ' droplogins '--if you specify Droplogins, delete the login mappings before deleting the linked server

/* For example: EXEC sp_dropserver ' tonylink ', ' droplogins ' */







--Attach: Gets the method of provider name (EXEC master. xp_enum_oledb_providers)

SELECT CAST ([Provider Name] as VARCHAR ()) ProviderName,

CAST ([Provider Description] as VARCHAR) providerdescription

From OPENROWSET (

' SQLOLEDB ',

' Server=.; Trusted_connection=yes ',

' SET fmtonly OFF;

EXEC Master. Xp_enum_oledb_providers '

)

/*

ProviderName providerdescription

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

SQLOLEDB Microsoft OLE DB Provider for SQL Server

DTSPackageDSO Microsoft OLE DB Provider for DTS Packages

Sqlreplication.oledb SQL Server Replication OLE DB Provider for DTS

MSOLAP Microsoft OLE DB Provider for Analysis Services 10.0

Msdmine Microsoft OLE DB Provider for Data Mining Services

microsoft.ace.oledb.12.0 Microsoft Office 12.0 Access Database Engine OLE DB Provider

Adsdsoobject OLE DB Provider for Microsoft Directory Services

SQLNCLI10 SQL Server Native Client 10.0

MSDAIPP. DSO Microsoft OLE DB Provider for Internet Publishing

Msdasql Microsoft OLE DB Provider for ODBC Drivers

microsoft.jet.oledb.4.0 Microsoft Jet 4.0 OLE DB Provider

Msdaosp Microsoft OLE DB Simple Provider

Msdaora Microsoft OLE DB Provider for Oracle

MSIDXS Microsoft OLE DB Provider for indexing Service



(14 rows affected)

*/

SQL Create Linkserver