Add a MySQL linked server under sqlserver

MSSQL version: SQL Server 2008
MySQL version: mysql-5.1.32

1. Install msdasql
-- Install msdasql (64-bit oledb provider for ODBC (msdasql ))
-- Http://www.microsoft.com/downloads/details.aspx? Familyid = 000364db-5e8b-44a8-b9be-ca44d18b059b & displaylang = ZH-CN
-- Select the version WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe

2. Install MySQL
ODBC -- mysql-connector-odbc-5.1.6-winx64.msi

3. The peripheral application configurator registers the network.
Exec
Sp_configure 'show advanced option', 1
Reconfigure
Go
Exec sp_configure
'Ad hoc distributed queries ', 1
Go
Reconfigure
Go

4. Create a MySQL linked server
-- Used to obtain data from MySQL
Exec
Master. DBO. sp_addmediaserver
@ Server = n' MySQL ',
@ Srvproduct = n'mysql ',
@ Provider = n 'msdasql ', @ provstr = n' driver = {MySQL ODBC 5.1 Driver}; server = IP; database = dbname; user = username; Password = password; option = 3'

Select * From openquery (MySQL, 'select ID, name from test. user ');
Insert into openquery (MySQL, 'select ID, name from test. User where 1 = 0') values (5, 'newuser ');

5. Create an MSSQL linked server
Exec sp_addmediaserver
'Mssql', '', 'sqloledb', 'tcp: IP \ hostname, 100'
Go
Exec
Sp_add1_srvlogin 'mssql', 'false', null, 'username', 'Password'
Go
Select * From openquery (MSSQL, 'select ID, name from test. DBO. [user] ')
Go ============================2. Database Access and operations

After the connected database is created, you can access the connected database. There are two common methods:
(1) T-SQL mode select * from [chain table name]. [database name]. [architecture]. [Table name]
Access is generally used: Select * from [chain table name]... [Table name] (neither database name nor Architecture)
MSSQL is generally used: Select * from [chain table name]. [database name]. [DBO]. [Table name]
Oracle is generally used: It is said that select * from [chain table name]... [Oracle user name]. [Table name] (I have not tried it ..)
(2) Use openquery for PL/SQL interaction (although not the best, the speed is at least half faster than the first method)
query an instance:
select * From openquery (connection server name, 'select * from [Table name] ')
new instance:
insert openquery (Link Service name, 'select Field 1, Field 2 from table') values ('value 1 ', 'value 2');
by the way: openquery returns a dataset, in other words, the preceding statement, data must be inserted only after all the data in the table is selected. If the data volume is large, this process takes a long time. Therefore, the preceding statement needs to be improved:
insert openquery (name of the Link Service, 'select Field 1, Field 2 from Table where 1 = 2' ) values ('value 1 ', 'value: 2');
Update an instance:
Update openquery (name of the linked service, 'select Field 1, Field 2 from table where id = 1') Set field 1 = 'value 1', Field 2 = 'value 2 ';
delete an instance:
Delete openquery (name of the linked service, 'select Field 1 from table where field 1 = 1');
(if the server connected to all the above operation instances is MSSQL, you need to add the database and architecture before the table name, such as the database name. DBO. table Name)

In addition, the quotation marks are easy to ignore, although simple. For example:
Select * From openquery (connection server name, 'select * from [Table name] Where id = 1') is okay, but if ID is a character, it should be
Select * From openquery (connection server name, 'select * from [Table name] Where id = ''value ''') requires two 'after the first one ', if the statements in the middle are more complex, or the function needs to be introduced and written separately, add four ''to the front and back. The principle is to add two'' to the front and back ', it is actually an escape.

3. Use of synonyms (only supported by sql2005 and later versions !)

This stuff is good! Very powerful!(Openquery is actually its simplified version)

Create statement:

Create synonym [synonym name] for [linked server name]. [database]. [architecture]. [Table name]
Go

My personal understanding is that, first create a remote link through the linked server, and then create a synonym to the local database through the linked server, in this way, a virtual table (table, view, function, and stored procedure) is created in the local database. The remote table is like a local table, therefore, it seems more appropriate to translate synonyms into chain tables or linked objects ..

In this way, it is very convenient to operate the remote database, whether it is access, SQL, or Oracle (it is estimated that the connection from other databases is the same, the premise is that the corresponding access interface is required) you can use the most common T-SQL statement operations, such:
Select * from Synonym name
Insert into synonym name (...) values (...)
Delete synonym name where...
(Of course, you need to add, delete, modify, and other operations. You need to have the corresponding permissions on the linked server)
In this way, the two methods mentioned above can be used to access the connected database. However, if SQL2000 is used, you can only use openquery to interact with the database...

From: http://www.cnblogs.com/seerlin/archive/2011/06/01/2065638.html