Ssql Server 2000 link server (linkserver) special instructions

MSSQL Server 2000 Standard Method for accessing an object is composed of four parts:

[Servername].[Databasename].[Username].[Objectname]
For each part, if it is the current value, it can be omitted;
For the current server,[Servername]Omitted (or, I have not found a replacement token for the current server );

For example, to access the northwind. DBO. Employees table on the server, write as follows:

Select * FromNorthwind. DBO. employeesgo
If the current database is switched to northwind, the database name is omitted, for example:

UseNorthwindgoselect* FromDBO. employeesgo
If the current user is a DBO login (such as SA or another DBO login name), you can also omit the User Name:

Select * FromEmployeesgo
If the middle part is the same, it can be omitted, but the position should be retained, for example:

UseTempdbgoselect* FromNorthwind .. employeesgo
If you access the table on a remote server, assume that the connection name of the server on the remote server is remoteserver.

Select * FromRemoteserveremployeesgo
Assume that the remote server is logged on using SA and the current database is set to northwind.

In addition, remote servers of other engine types may have different server mechanisms. If some part of the 4 Section does not have corresponding elements, this part can be omitted. For example, for Oracle databases, the user's database is the solution (SchemaIf you do not have a database, use the following methods to access the Oracle Remote Server:

Select * FromOracleserver.. Scott. EMP

Linked server

MSSQL Server 2000 provides a linked server that allows you to easily access remote SQL Server database data (tables and views) and other database engine data. The linked server is located in the secure.

Link to Oracle Database

Link to the Oracle database and enter the following content:
ProvideProgramName, select Microsoft ole db ProviderForOracle; data source, enter the service name connecting to the Oracle database; then enter the Logon account and password on the Security tab.

Access the remote ORACLE database table:

Select * FromOra_server..scott.empselect* FromOra_server ..User. Demo_table/*You can access tables of other users.*/

Specifically:

For the provider name, select "Microsoft ole db provider ".ForOracle, the value stored in the sysservers table is "msdaora ". If "Oracle providerForOle db. The value stored in the table is "oraoledb. Oracle ". Selecting the program name will not allow you to access data properly. (Maybe I have not found the correct access method ).

Objects accessing the Oracle database by connecting to the server are case sensitive. That is, access the database table,[Username]And[Tablename]Generally, uppercase letters are required unless the Oracle database uses lowercase letters.

If Chinese characters are stored, You must select the sorting rule name of the "chinese_prc _" series on the "server options" option page. Otherwise, Chinese data will be garbled.

Link to ODBC database

Link to the ODBC database and enter the following content:

provider name, select "Microsoft ole db Provider for ODBC driver". The value stored in the table is "msdasql", the product name is "ODBC", and the data source is the ODBC Data Source of the system DSN; provide the program string and enter the ODBC connection options (which are not described in ODBC ).

Example:
Connect to MSSQL Server6. 5 (MSSQL Server6. 5 you cannot use oledb for direct connection)
The data source uses the existing ODBC localserver (which is the default data source of ODBC in the operating system). This is not enough to connect to the database we need because the data source is actually a NULL data source. In the provider string, enter the link parameters:
Driver={SQL Server}; uid=SA; wsid=Host1; app={Application name}; server=Mssql65srv

Note:
Enter the driver name for the data source, Udi enter the Logon account, wsid enter the client name (workstationid), app enter the application name, and server enter the database server to be connected, database indicates the default database to be connected.

in particular, ODBC connects to sqlserver 6 . 5. The garbled characters generated for Chinese characters are not set in the above sorting rule name, but due to the problem of ODBC's own mechanism. When setting ODBC, deselect the "execute character data translation" option (although this option is required during ODBC testing, you do not need to care about it ).