SQL Server's linked server technology summary _mssql

First, use Microsoft OLE DB Provider for ODBC link to MySQL

Install MySQL-driven ODBC driver MYODBC

1, for MySQL to establish an ODBC system data sources, such as: Select the database test, the data source name called

MyDSN

2, establish the link database

EXEC sp_addlinkedserver @server = ' mysqltest ', @srvproduct = ' MySQL ', 

@provider = ' Msdasql ', @datasrc = ' MyDSN '
go
EXEC sp_addlinkedsrvlogin 
@rmtsrvname = ' mysqltest ', @useself = ' false ', @locallogin = ' sa ', @rmtuser = ' MySQL username ', @rmtpassword = ' mysql password '

3. Query data

SELECT * from OPENQUERY (mysqltest, ' select * from table ')

This is not the following:
SELECT * from OPENQUERY (mysqltest, ' table ')

Note: You cannot use the SELECT * from link server name directly. database name. User name. Table (or view)

Four-part name query data, may be a bug.

second, use Microsoft OLE DB Provider for Oracle link Oracle

1, establish the link database

sp_addlinkedserver ' Alias ', ' Oracle ', ' msdaora ', ' service name '
go
EXEC sp_addlinkedsrvlogin @rmtsrvname = ' Alias ', @useself = ' False ', @locallogin = ' sa ', @rmtuser = ' Oracle username ', @rmtpassword = ' password '

2. Query data

SELECT * FROM Alias ... User name. Table (view)

Note: Four part names are all capitalized

3. Execute Stored Procedure

Use OpenQuery:
SELECT *
From OPENQUERY (alias, ' exec username. Stored Procedure name ')

third, set up a linked server to access the formatted text file

Microsoft OLE DB providers for Jet can be used to access and query text files.

To create a linked server that accesses a text file directly without linking the file to an access. mdb file

Table, follow the sp_addlinkedserver, as shown in the following example.
The provider is microsoft.jet.oledb.4.0 and the provider string is "Text." Data source is a package

The full path name of the directory that contains the text file. The Schema.ini file (the structure that describes the text file) must

must exist in the same directory as this text file. For more information about creating Schema.ini files,

See the Jet Database engine documentation.

--create a linked server.
EXEC sp_addlinkedserver txtsrv, ' Jet 4.0 ',
' Microsoft.Jet.OLEDB.4.0 ',
' C:/data/distqry ',
Null
' Text '
Go

--set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
Go

--list the tables in the linked server.
EXEC sp_tables_ex Txtsrv
Go

--query one of the Tables:file1#txt
--using a 4-part name.
SELECT *
From Txtsrv ... [File1#txt]

Iv. linked SQL Server servers:

1. Microsoft OLE DB provider using ODBC

EXEC sp_addlinkedserver ' Alias ', ', ' msdasql ', Null,null, ' Driver={sql

Server}; server= remote name; Uid= users; pwd= password; '
If you add a parameter @catalog, you can specify the database
exec sp_addlinkedsrvlogin @rmtsrvname = ' Alias ', @useself = ' false ', @locallogin = ' sa ', @rmtuser = ' sa ', @rmtpassword = ' password '

2. Microsoft OLE DB provider using SQL Server

exec sp_addlinkedserver @server = ' Alias ', @provider = ' sqloledb ', @srvproduct = ', @datasrc = ' Remote server name '
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ' Wzb ', @useself = ' false ', @locallogin = ' sa ', @rmtuser = ' sa ', @rmtpassword = ' password '

Then you can go as follows:

SELECT * from alias. Library name. dbo. Table name
Insert library name. dbo. Table name select * from alias. Library name. dbo. Table name
SELECT * into library name. dbo. new table name from alias. Library name. dbo. Table name
Go

Example 1,

This example creates a linked server named S1_instance1 on an instance of SQL Server.
This server uses the Microsoft OLE DB provider for SQL Server.

EXEC sp_addlinkedserver @server = ' S1_instance1 ', @srvproduct = ',
@provider = ' SQLOLEDB ', @datasrc = ' S1/instance1 '

Example 2,

--Establish a linked server

EXEC sp_addlinkedserver ' xiaoming ', ', ' msdasql ', Null,null, ' Driver={sql

Server}; server=192.168.0.1; Uid=sa; pwd=123; '
--Establish a linked server login map
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ' Xiaoming ', @useself = ' false ', @locallogin = ' sa ', @rmtuser = ' sa ',
@rmtpassword = ' 123 '
Go
--Querying data
SELECT * FROM Xiaoming.schooladmin.dbo.agent

--Delete linked server login mappings and linked servers:
exec sp_droplinkedsrvlogin ' xiaoming ', ' sa '
exec sp_dropserver ' xiaoming '

Precautions :

SET Identity_insert [database.[owner.]] {table} {ON | OFF}
So this property cannot be set through the connection server
Into the existence of such a problem
SELECT * Into Xiaoming.northwind.dbo.tt from

Xiaoming.northwind.dbo.tt

V. Setting up a linked server to access the Access database

Using the Microsoft OLE DB provider for Jet
This example creates a linked server named Test.

Note This example assumes that Microsoft Access and the sample Northwind database are installed, and

The Northwind database resides in c:/.

Use master
Go
--To use named parameters:
EXEC sp_addlinkedserver
@server = ' Test ',
@provider = ' microsoft.jet.oledb.4.0 ',
@srvproduct = ' OLE DB Provider for Jet ',
@datasrc = ' C:/northwind.mdb '
Go
--OR to use no named parameters:
Use master
Go
EXEC sp_addlinkedserver
' Test ',
' OLE DB Provider for Jet ',
' Microsoft.Jet.OLEDB.4.0 ',
' C:/northwind.mdb '
Go
Use
SELECT * FROM Test ... Table name

Vi. connecting Sybase
--First, you'll have to access Sybase's client on the SQL Server

--Create a linked server
exec sp_addlinkedserver ' Sybase1 ', ', ' msdasql ', NULL, NULL
, ' Driver={sybase System

one};D atabase=hisdb; srvr=10.211.135.12; Uid=sa; pwd=1111; '
Use:
SELECT * FROM Sybase1.hisdb.dbo.table1

Method Two
Using ODBC
Implementation of SQL Server to Sybase connection server

Author: ccbzzp

The test environment for this article is:
Operating system: WINDOWS2000 SERVER (Traditional system)
Installation database: SQLSERVER2000 (English) and SYBASE8.0 client (English version)

Specific steps to achieve:
1. require PC to install SYBASE8.0 client software and sqlserver2000 software.
2. Configure Windows ODBC Data source:
Start Menu-"assembly-" System management Tool-"data source (ODBC)-" Access configuration

User DSN or System DSN can: Add-"Select adaptive SERVER anywhere8.0-" Custom

Semantic data source name (optional: sybasetest)-"Database name (required!) )-"OK complete."

3. Select the name of the data source that you just configured, then select Configure and jump out of sybasetest MESSAGES:

The data source is not connected. Connecting to the data source would

Provide useful information during configuration. Would to

Connect to the data source?

Select Yes (OK or confirm)

Enter connect to SYBASE database screen:

User ID: Enter the users of Sybase database

PASSWORD: Enter the user's password for Sybase database

CONNECTION mode: You can choose the default share mode

Select OK (confirm)!

Configure and test ODBC complete!

4. Configure the connection server in sqlserver2000:
Enterprise Manager-"Security-" Connection server-"right" new connection server-Define connection name

To select a different data source; Specify a program name: SYBASE Adaptive SERVER ANYWHERE

PROVIDER8.0; Product name is not filled; Data source Specifies the name of the data source that is defined in ODBC just now;

The provider string is filled in the following format: User id=username; PASSWORD=USERPASSWD (or

According to the following format: Uid=username; PWD=USERPASSWD), where the username and password correspond to the

User name and password in the Sybase database-Security tab: Setting this security up and down

text, and input Sybase database username and password-server Option tab page can default-"

Are you sure.
5. All preparations are complete! In SQL Server Enterprise Manager-"Security-" Connect server open just

Built connection server-click on the table to see what the Sybase database user owns in the right window

All of the table names, but you can't see the table's records here, this needs to be Sqserver query analysis

With the specific SQL implementation of the device! When accessing a table, use the format: [Connect server name]. [Sybase Users

]. [Table name]. More detailed and specific use here no longer repeat.