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.