Link SQL Server to Linked Server

Usage
Exec sp_addlinkedserver @ server, @ srvproduct, @ provider, @ datasrc, @ location, @ provstr, @ catalog;
Exec sp_addjavassrvlogin @ rmtsrvname, @ useself, @ locallogin, @ rmtuser, @ rmtpassword;
Exec sp_serveroption @ server, @ optname, @ optvalue;
Sp_helpserver
Sp_incluservers
Sp_dropserver

For example: Exec sp_addmediaserver 'erp ', 'oracle', 'msdaora ', 'amt ';
Exec sp_add1_srvlogin 'erp ', 'false', null, 'crm', 'crm ';
Exec sp_serveroption 'erp ', 'rpc', 'true ';

When Oracle is used as a remote connection Server, the Oracle Client must be installed on the SQL Server. Configure SQL * Net name for OLE DB provider

Sp_addmediaserver
For various providers, see sp_addmediaserver.
For more information about how SQL Server supports ole db providers, see ole db providers Tested with SQL Server.

Sp_add1_srvlogin
@ Rmtsrvname: name of the remote connection Server
@ Useself: whether to use the authentication information currently logged on to the SQL Server to log on to the linked Server. Both SQL Server and remote linked Server can use Windows integrated authentication for logon.
@ Locallogin: Establishes the correspondence between the Logon account of the local SQL Server and the Logon account of the remote connection Server. For example, the local SQL Server sa account uses A1 to log on to the linked Server, and the crm account uses A2 to log on to the linked Server. @ Locallogin can be a domain account. If it is null, all accounts of the local SQL Server can use this logon information.
@ Rmtuser, @ rmtpassword: account and password used to log on to the remote connection Server

Query statement
Method 1: Use pai_server_name.catalog.schema.object_name, for example:
Select * from ERP... CRM. INQ
__Server_name Linked server referencing the ole db data source
Catalog Catalog in the ole db data source that contains the object
Schema Schema in the catalog that contains the object
Object_name Data object in the schema
SQL Server uses pai_server_name to obtain the configuration information of the linked server, and then transmits catalog, schema, and object_name as parameters to OLEDB. For example, when the remote connection Server is SQL Server, the catalog is the database instance name, the schema is the owner id (dbo), the connection Server is Oracle (OLEDB), the catalog is empty, and the schema is the user

Note:
A) when the remote connection server is Oracle, schema and object_name must be capitalized (other types are unclear); otherwise, an error will be reported.
Message 7314, level 16, state 1, 1st rows
The ole db access interface of the linked server "ERP" "MSDAORA" does not contain the table "" CRM "." INQ "". The table does not exist, or the current user does not have the permission to access the table.
B). The complete name must be used. For example, if SQL Server executes SQL locally, dbo can be omitted without writing it. If you use remote connection to the Server, you must provide

Method 2: Use openquery, for example:
Select *
From openquery (ERP, 'select t. * from (select inq. *, rownum as rindex from inq order by img01) t where t. rindex> = 51 and t. rindex <= 100 ')

Advantages:
A). The SQL Server only sends the SQL statements in openquery to the remote Server for execution. Therefore, you can use all the SQL syntax (such as the Oracle syntax) of the remote connection Server, and the SQL object does not have to be capitalized.
B). There are very few exceptions in the Data Type (in some cases, it will still happen)
C) openquery can also be used for update, insert, and delete operations.
D) when the linked server is Oracle, the schema does not need to appear in the SQL statement.

For Restrictions and prerequisites on using linked servers, refer to External Data and Transact-SQL, Keyset-Driven Cursors Requirements for OLE DB Providers.

FAQs

1. Data Type exceptions, such:
The ole db access interface "MSDAORA" of Msg 7356, Level 16, State 1, Line 1 linked server "ERP" provides inconsistent metadata for columns. The column "IMG21" of the object "CRM". "INQ" "(The compilation time series is 7) has 131" DBTYPE "at compilation, but 130 at runtime.
When using a query statement, this error is very easy. openquery is rarely used (in some cases, it will still happen). We recommend that you use openquery. If openquery is still in this situation, we recommend that you convert the Data Type of the remote connection Server into simple and clear data types as much as possible, and there is a corresponding data type in SQL Server. For example, the Number in Oracle is a flexible one, and the SQL Server should be able to determine whether to convert to decimal or int type.

2. Character Set conversion. For example, if you perform comparative operations such as = and like on the record set on the remote connection server, a character set error may occur:
Msg 468, Level 16, State 9, Line 1
The sorting rule conflict between "Chinese_PRC_90_CI_AI" and "Chinese_PRC_CI_AS" cannot be solved in the equal to operation.
You can use forced Character Set conversion, for example
Select *
From openquery (ERP, 'select t. * from (select inq. *, rownum as rindex from inq order by img01) t where t. rindex> = 51 and t. rindex <= 100 ') t
Inner join itm_item I on t. img01 collate Chinese_PRC_CI_AS = I. itm_code

3. Permission issues
Scenario: A 64-bit SQL Server uses the 64-bit Oracle OLE DB provider (OraOLEDB. oracle.1), after the Linked Server is added, the account that uses the SQL Server authentication to log on has the permission to access, and the domain account that uses the Windows integrated authentication to log on returns an error:
Message 7399, level 16, state 1, 1st rows
The ole db access interface "OraOLEDB. Oracle.1" of the linked server "ERP" reports an error. Access is denied.
Message 7301, level 16, status 2, 1st rows
You cannot obtain the required interface ("IID_IDBCreateCommand") from the ole db access interface "OraOLEDB. Oracle.1" of the linked server "ERP ").
Solution:
[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft SQL Server \ MSSQL.1 \ Providers \ OraOLEDB. Oracle.1]
"AllowInProcess" = dword: 00000001
If OraOLEDB. Oracle.1 is not found under Providers, a new

Related Knowledge and terminology

In SQL Server, openquery, openrowset, and opendatasource are called distributed query distributed queries.
SQL Server supports two distributed queries: one is the Linked Server mentioned above, and the other is the Ad Hoc method, that is, openrowset or opendatasource. The Linked Server is used when the execution frequency is high, and the Ad Hoc mode is used when the execution frequency is low.
Ole db provider has two methods. One is to expose the database table directly as the rowset row set, so that the client program can operate on the database table through the ole db interface, this method is called remote tables. The other method is to send the query SQL statement to the database server through the OLE DB interface. The server executes the query and returns the rowset to the client program. This method is called pass-through queries.
The preceding two methods are used to query the statement. method 1 should use the remote tables method, and method 2 should use the pass-through queries method. Therefore, method 2 fully supports SQL syntax for remote connection to the server
SQL Server regards the rowset returned by openquery and remote tables as a table, which is processed in the same way as the SQL Server table. In SQL, condition filtering, sorting, and association with other tables under the rowset field of openquery may be performed. According to the information provided by the OLE DB provider Interface, determine whether these operations can be delegated to a remote connection server. For example,. img02 = '21cdk' and img10 = 700. If a remote connection server can be delegated, these conditional operations are sent to the remote server, the returned rowset is after the condition filter. Otherwise, only all data can be returned. the SQL Server performs the filtering operation on the returned data.
The ole db provider interface provides very limited metadata information and data conversion operations caused by data structures between different databases, as a result, SQL Server cannot perform excessive query optimization policies on non-SQL Server linked servers. When the pass-through queries method is used, the remote database server can take full advantage of its own maintenance statistics, indexes, and other optimization measures. Therefore, the pass-through queries method should be used to make full use of the linked server.
In the following example, erp is an Oracle database.
Select .*
From erp... CRM. INQ
Where a. img02 = '21cdk' and img10 = 700
Query plan

Remote Query returns all data in the INQ table. The Filter operation is CONVERT_IMPLICIT (int, [erp]. [CRM]. [INQ]. [IMG10] as [a]. [IMG10], 0) = (0, 700) AND [erp]. [CRM]. [INQ]. [IMG02] as [a]. [IMG02] = '10bbk'

Link Server Configuration

For distributed queries, SQL Server supports two levels of configuration: OLE DB provider level, in the windows registry; linked server level, use sp_serveroption to configure (both levels of configuration can be configured through the link Server right-click menu attribute in SQL Server Enterprise Manager)

Ole db provider level:
DynamicParameters: ole db provider supports parameterized queries, and the parameter usage? As a tag, you can set it to true (non-0 value)
SqlServerLike: supports the like operation and can be set to true (non-0 value ). When the like operation is supported, SQL Server can submit the operation to the remote Server for execution. Otherwise, SQL Server needs to perform the like operation on its own. In the remote tables mode, SQL Server may face such a decision.
DisallowAdhocAccess: whether to allow SQL Server to execute distributed queries in ad hoc mode. If this parameter is set to true (not 0) or not set, SQL Server does not allow ad hoc.
IndexAsAccessPath: whether SQL Server can use remote Server index information through OLE DB provider requires OLE DB provider to implement related interfaces
NonTransactedUpdates: whether to support transactions. If it is set to true, SQL Server does not use transactions for Distributed update statements even if the OLE DB provider implements the transaction interface.
AllowInProcess: whether to perform ole db interface operations within the SQL Server process. Configured as in-process operations. An ole db exception may affect the SQL Server process. The ole db operation is performed outside the SQL Server process. The SQL Server cannot update or insert a LOB object, for example, text, image, and clob. This parameter requires that the SQL Server and the remote connection Server be on the same machine (do you want the remote connection Server to be SQL Server ?)
LevelZeroOnly: if it is set to true, SQL Server only uses OLE DB level0 Interfaces
NestedQueries: whether nested queries are allowed

Linked Server level:
The following server options can be configured for sp_serveroption:
Collation compatible:
Affects Distributed Query execution against linked servers. if this option is set to true, Microsoft SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order ). this enables SQL Server to send comparisons on character columns to the provider. if this option is not set, SQL Server always evaluates comparisons on character columns locally.
This option shoshould be set only if it is certain that the data source corresponding to the linked server has the same character set and sort order as the local server.
Use remote collation:
If this parameter is set to true, SQL Server uses the character set of the remote connection Server for the text field. If the link Server is SQL Server, SQL Server obtains the character set information from the ole db provider Interface of SQL Server, if it is not SQL Server, use the character set configured by collation name. When set to false, SQL Server uses the default Character Set of the local Server.
Collation name: Character Set
Connect timeout:
Data access:
Enables and disables a linked server for distributed query access. Can be used only for sysserver entries added through sp_add1_server.
Dist: Distributor.
Dpub: Remote Publisher to this Distributor.
Lazy schema validation: Determines whether the schema of remote tables will be checked. If true, skip schema checking of remote tables at the beginning of the query.
Pub: Publisher.
Query timeout: Time-out value for queries against a linked server. If 0, use the sp_configure default.
Rpc: Enables RPC from the given server.
Rpc out: Enables RPC to the given server.
Sub: Subscriber.