MSSQL linked server

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

1. Install msdasql
-- Install msdasql (64-bit oledb provider
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 to be select * from
[chain table name] .. [Oracle user name]. [Table name] (I have never tried ..)
(2) PL/SQL mode
Use openquery for interaction (although not the best, the speed is at least half faster than the first method)
query 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 is very long. Therefore, the preceding statement needs to be improved:
insert
openquery (Link Service name, 'select Field 1, Field 2 from table where 1 = 2')
values ('value 1 ', 'value 2');
Update instance:
Update openquery
(Link Service name, 'select Field 1, Field 2 from table where id = 1 ') set field 1 = 'value 1', Field 2 = 'value 2';
delete an instance:
Delete openquery (Link Service name, 'select Field 1 from table where
Field 1 = 1');
(for all the above operation instances, if the linked server is MSSQL, you need to add the database and architecture before the table name, for example, 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 ')
This is fine, but if the ID is a character, it should be
Select * From openquery (connection server name, 'select * from [Table name] Where
Id = ''value ''') must be followed by two''. If the intermediate statement is more complex or a function is introduced, you need to split it and write it, add four'
The principle is to add two ''at a time, which 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]
[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...

--