[SQL Server] the link between SQL Server and MySQL has been tested.

[SQL Server] the following test is performed on the link between SQL Server and MySQL 2005 In the XP SP3 system.

 

 

-- Create a linked server through ODBC

-- Method 1: Add a data source named mysql_testdb in advance to the system ODBC data source.
-- Exec sp_addmediaserver @ Server = 'mysql _ testdb', @ srvproduct = 'mysql', @ provider = 'msdasql ', @ datasrc = 'mysql _ testdb'
-- Go
-- Method 2: the data source is not in the system ODBC data source and is directly written in the configuration parameters
Exec sp_addmediaserver @ Server = 'mysql _ testdb', @ srvproduct = 'mysql', @ provider = 'msdasql ', @ provstr = 'driver = {BMF MySQL ODBC driver }; server = localhost; database = testdb; user = root; Password =; option = 3 ;'
Go

Exec sp_add1_srvlogin @ rmtsrvname = 'mysql _ testdb', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'root', @ rmtpassword =''
-- Delete a linked server
Exec sp_dropserver 'mysql _ testdb', 'droplogins'

-- Query
Select * From openquery (mysql_testdb, 'select * From tb ')
/*
ID name
-------------------------------
35586 35586
35587 35587
35588 35588
.....
*/
-- Insert
Insert openquery (mysql_testdb, 'select * From tb') select 1, 2
-- Update
Update openquery (mysql_testdb, 'select * From tb') Set Name = 3 where id = 1
-- Delete
Delete from openquery (mysql_testdb, 'select * From tb') Where id = 1
Delete from openquery (mysql_testdb, 'select * From tb ')

 

 

-- Test in the trigger to compare how to synchronize

-- Create a test environment
If object_id ('tb') is not null drop table TB
Go
Create Table TB
(
Id int identity,
Code varchar (10 ),
Name varchar (20 ),
Constraint pk_tb primary key (ID)
)
Go

If object_id ('tri _ tb') is not null drop trigger tri_tb
Go
Create trigger tri_tb
On TB
For insert, update, delete
As
Begin
If not exists (select 1 from deleted)
Insert openquery (mysql_testdb, 'select * From tb') Select ID, code from inserted
Else
If not exists (select 1 from inserted)
Delete t from openquery (mysql_testdb, 'select * From tb') T, deleted D where T. ID = D. id
Else
Update t
Set Name = I. Code
From openquery (mysql_testdb, 'select * From tb') T, inserted I, deleted d
Where I. ID = D. id and I. ID = T. ID
End
Go
-- 1 isnert
Insert TB select 111,111
-- The first execution reports an error
/*
Message 8501, level 16, status 3, process tri_tb, 8th rows
MSDTC on 'landsea-8cc1455/sqlexpress 'server is unavailable.
*/

-- Check the system service and find that the DTC service is not started (Distributed Transaction Coordinator)
-- After it is started and executed again, there is no problem. It may be that I am a local database and there is no distributed problem (I don't have many machines ),
-- A lot of people have encountered problems here, and many of them have already been well organized. For details, refer:

[SQL Server] SQL Server Distributed Database Problems and Solutions 1
Http://blog.csdn.net/xys_777/archive/2010/07/12/5729339.aspx

[SQL Server] SQL Server Distributed Database Problems and Solutions 2
Http://blog.csdn.net/xys_777/archive/2010/07/12/5729334.aspx

 

-- The execution continues successfully. to display the affected number of rows, I intentionally did not add set nocount on to the trigger,
/*
(One row is affected)

(One row is affected)
*/

-- The following tests are successful:
Select * from TB
/*
ID code name
-----------------------------------------
1 111 111

(One row is affected)

*/
Select * From openquery (mysql_testdb, 'select * From tb ')
/*
ID name
-------------------------------
1 111

(One row is affected)
*/
Insert TB select 222,222 Union select 333,333
Select * From openquery (mysql_testdb, 'select * From tb ')
-- The order is messy.
/*
ID name
-------------------------------
2 222
3 333
1 111

(3 rows affected)
*/
Update TB set code = 555
Select * From openquery (mysql_testdb, 'select * From tb ')
-- The order is messy.
/*
ID name
-------------------------------
2 555
3 555
1 555

(3 rows affected)
*/
Delete from TB where id = 1
Select * From openquery (mysql_testdb, 'select * From tb ')
/*
ID name
-------------------------------
2 555
3 555

(2 rows affected)
*/

 

----> As you can see, the above method can be used as long as the linked server is changed to any other database.

 

Similar articles on the Internet:

Http://doc.chinaunix.net/sqlserver/201004/492237.shtml (with diagram recommended)