Linked Servers for SQL Server (MySQL, Oracle, Ms_sql, Access, SYBASE)

Original: Linked server for SQL Server (MySQL, Oracle, Ms_sql, Access, SYBASE)

One, using Microsoft OLE DB Provider for ODBC link MySQL
Installing MySQL's ODBC driver MYODBC
1, for MySQL to establish an ODBC system data source, for example: Select the database as test, the data source name is MyDSN

2. Establish a linked 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 ')
The following is not possible:
SELECT * from OPENQUERY (mysqltest, ' table ')

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

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

Ii. using Microsoft OLE DB Provider for Oracle link Oracle

1. Establish a linked database
sp_addlinkedserver ' aliases ', ' Oracle ', ' msdaora ', ' Service name '
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname = ' Alias ', @useself = ' false ',
@locallogin = ' sa ', @rmtuser = ' Oracle user name ', @rmtpassword = ' password '

2. Query data
SELECT * FROM alias: User name. Table (view)
Note: Four-part names are all capitalized

3. Execute Stored Procedure
Using OpenQuery:
SELECT * from OPENQUERY (alias, ' exec user name. Stored Procedure name ')

Third, set up the linked server to access the formatted text file
The Microsoft OLE DB provider 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 a table in an Access. mdb file, execute sp_addlinkedserver, as shown in the following example.
The provider is microsoft.jet.oledb.4.0, and the provider string is "Text". The data source is the full path name of the directory that contains the text file. The Schema.ini file (which describes the structure of the text file) must exist in the same directory as this text file. For more information about creating a Schema.ini file,

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. Linking SQL Server servers:

1. Microsoft OLE DB provider using ODBC

EXEC sp_addlinkedserver ' aliases ', ' ', ' msdasql ', Null,null, ' Driver={sql Server};
server= remote name; Uid= users; pwd= password; '
If you add the 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 that uses SQL Server

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

Then you can do the following:
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 that 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 mapping
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}
Therefore, this property cannot be set through the connection server
Into also has this 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 have been 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 want to install the client that accesses Sybase 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 implementation steps:
1. Requires the SYBASE8.0 client software and sqlserver2000 software to be installed on the PC.
2. Configure the ODBC data source for Windows:
Start Menu-"program set-" System management Tools-"data source (ODBC)-" into the configuration

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

The name of the semantic data source (optional: sybasetest)-database name (required!) )-"OK to finish."

3. Select the data source name you just configured, and then select Configure to 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) to

Enter the connect to SYBASE database screen:

User ID: Users entering Sybase database

PASSWORD: Password for the user entering Sybase database

CONNECTION mode: You can choose the default share mode

Choose OK (confirm)!

Configure and test ODBC complete!

4. Configure the connection server in the sqlserver2000:
Enterprise Manager-"Security-" Connect Server-"Right-click New Connection Server-" Define the connection name

Select a different data source; Specify the program name: SYBASE ADAPTIVE SERVER ANYWHERE

PROVIDER8.0; The product name is optional; The data source specifies the name of the data source that was 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 user name and password correspond to the

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

Database user name and password for Sybase-"Server Options tab can default-"

Are you sure.
5. All preparations are complete! In SQL Server Enterprise Manager-"security-" connect the servers to open just

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

All of the table names, but it is not possible to view the table records here, which requires query analysis in Sqserver

Specific SQL implementation in the converter! When accessing a table, use the format: [Connect server name] ... [Sybase User

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

Linked Servers for SQL Server (MySQL, Oracle, Ms_sql, Access, SYBASE)