[sql server] sql server 連結 MySql 實測經過

[sql server] sql server 連結 MySql 實測經過以下測試是在 xp sp3系統下，使用 sql server 2005 express 與 MySql 5.0

 

 

--通過odbc建立連結的伺服器

-- 方法1、在系統odbc資料來源中 預先添加一個DSN名為MySQL_TestDB的資料來源
--EXEC sp_addlinkedserver  @server = 'MySQL_TestDB', @srvproduct='MySQL', @provider = 'MSDASQL', @datasrc = 'MySQL_TestDB'
--GO
-- 方法2、不在系統odbc資料來源中資料來源，直接寫在配置參數中
EXEC sp_addlinkedserver  @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_addlinkedsrvlogin  @rmtsrvname='MySQL_TestDB',@useself='false',@locallogin='sa',@rmtuser='root',@rmtpassword=''
--刪除連結的伺服器
exec sp_dropserver 'MySQL_TestDB' , 'droplogins'

--查詢
SELECT * FROM OPENQUERY (MySQL_TestDB ,'select * from tb' )
/*
id          name
----------- --------------------
35586       35586
35587       35587
35588       35588
.....
*/
--插入
insert OPENQUERY (MySQL_TestDB ,'select * from tb' ) select 1,2
--更新
update OPENQUERY (MySQL_TestDB ,'select * from tb' ) set name=3 where id=1
--刪除
delete  FROM OPENQUERY (MySQL_TestDB ,'select * from tb' ) where id=1
delete  FROM OPENQUERY (MySQL_TestDB ,'select * from tb' )

 

 

--觸發器中測試，大家比較關係如何同步

--建立測試環境
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
--第一次執行報錯
/*
訊息 8501，層級 16，狀態 3，過程 tri_tb，第 8 行
伺服器 'LANDSEA-8CC1455/SQLEXPRESS' 上的 MSDTC 不可用。
*/

--查看了一下系統服務，發現dtc的服務未啟動 (Distributed Transaction Coordinator)
--將其啟動後，再次執行，就沒有問題，可能是我都是本機資料庫，沒有分布式的問題（我沒有太多機器），
--這裡很多人都遇到問題，對於這裡的問題已經有很多人整理的很好了，我們可以參考：

[sql server] SQL Server 分散式資料庫的問題和解決方案1
http://blog.csdn.net/xys_777/archive/2010/07/12/5729339.aspx

[sql server] SQL Server 分散式資料庫的問題和解決方案2
http://blog.csdn.net/xys_777/archive/2010/07/12/5729334.aspx

 

--繼續執行成功，為了顯示了影響的行數，我故意沒有在觸發器中加 set nocount on，
/*
(1 行受影響)

(1 行受影響)
*/

--以下測試均很順利
select * from tb
/*
id          code       name
----------- ---------- --------------------
1           111        111

(1 行受影響)

*/
SELECT * FROM OPENQUERY (MySQL_TestDB ,'select * from tb' )
/*
id          name
----------- --------------------
1           111

(1 行受影響)
*/
insert tb select 222,222 union select 333,333
SELECT * FROM OPENQUERY (MySQL_TestDB ,'select * from tb' )
--順序有些亂
/*
id          name
----------- --------------------
2           222
3           333
1           111

(3 行受影響)
*/
update tb set code = 555
SELECT * FROM OPENQUERY (MySQL_TestDB ,'select * from tb' )
--順序有些亂
/*
id          name
----------- --------------------
2           555
3           555
1           555

(3 行受影響)
*/
delete from tb where id=1
SELECT * FROM OPENQUERY (MySQL_TestDB ,'select * from tb' )
/*
id          name
----------- --------------------
2           555
3           555

(2 行受影響)
*/

 

----〉實際大家可以看出來上述方法，只要連結的伺服器改為其他任何資料庫，皆通用

 

網上類似文章：

http://doc.chinaunix.net/sqlserver/201004/492237.shtml（帶圖推薦）