用户名：wd河按小伟3745657
1.安装安装mysqlconnector2.配置mysqlconnectorODB问答C**管理器->系统DSN->添加->mysqlODBC5.3ANSIdriver->填入datasourcename如jt,mysql的ip、用户名、密码即可3.新建链接服务器execsp_addlinkedse必界渐松象副极督担座积rver@server='jt',--ODBC里面datasourcename@s二心rvpro**ct='mysql',--自己随便@provider='MSDASQL',--固定这个@datasrc=**LL,@location=**LL,@provstr='D胶实紧弦山含RIVER={MySQLOD便话木BC5.3ANSIDrive局积盟急量线从境r};**RVER=192.168.5.188;DATABA**=suzhou;UID=root;PORT=3306;',@catalog=**LLexecsp_addlinkedsrvlogin@rmtsrvname='jt',@us父专材翻希eself='false',@rmtuser='root',@rmtpassword='password';select*fromopenquery(j迫找t,'**LECT*FROMsz;')GOUSE[master]GOEXECmaster.dbo.sp_serveroption@server=N'jt',@optname=N'rpcout',@optvalue=N'TRUE'GOEXECmaster.dbo.sp_serveroption@server=N'jt',@optname=N'remoteproctransactionpromotion',@optvalue=N'false'GO--永贵就良伯育础注肉独沙-4.sqlserver和mysql新建库和表createdatabasesuzhou;createtablesz(idintnot**llidentity(1,1)primarykey,ordernochar(20)n住而液导且ot**ll,ordertimedatetimenot**lldefaultgetdate(),remarkvarchar(200))由担远脚活到括孔gocreatetablesz(idint(11)not**ll,ordernochar(20)no破心物t**ll,ordertimedatetime(6)not**ll,remarkvarchar(200),primarykey(id))engine=innodbdefaultcharset=utf8;---5.建立回环--建立LOOPBACK服务器链接EXECsp_addlinkedserver@server=N'loopback',@srvpro**ct=N'',@provider=N'SQLNCLI',@datasrc=@@**RVE财理输界杆约RNAMEgo--设置服务器链接选项，阻止SQLServer由于远过程调用而将本地事务提升为分布事务（重点祖吗长令首与步老）U**[master]GOEXECmaster.dbo.sp_serveroption@server=N'loopback',@optname=N'rpcout',@optvalue=N'TRUE'GOEXECmaster.dbo.sp_serveroption@server=N'loopback',质结@optname=N'remoteproctransactionpr督杨语地令利管界所保omotion'呀文批罗优花困,@optvalue=N治征道房之剂不'false'GO----6.编写触发器和存储过程----6.1insert--重写触发器usesuzhougoaltertriggertr_insert_szonsuzhou.dbo.szforinsertasdeclare@idint,@ordernochar(20),@ordertimedatetime,@remarkvarchar(200)select@id=id,@orderno=orderno,@ordertime=ordertime,@remark=remarkfrominserted;beginprint@idprint@ordernoprint@ordertimeprint@remarkexecloopback.suzhou.dbo.sp_insert@id,@orderno,@ordertime,@remarkendgo--存储过程usesuzhougocreatePROCEDUREsp_insert(@idint,@ordernochar(20),@ordertimedatetime,@remarkvarchar(200))ASBEGIN**TNOCOUNTON;Insertopenquery(jt,'select*fromsz')(id,orderno,ordertime,remark)values(@id,@orderno,@ordertime,@remark)ENDgo----6.2update--重写触发器usesuzhougocreatetriggertr_update_szonsuzhou.dbo.szforupdateasdeclare@ordernochar(20),@remarkvarchar(200)select@orderno=orderno,@remark=remarkfrominserted;beginexecloopback.suzhou.dbo.sp_update@orderno,@remarkendgo--存储过程usesuzhougocreatePROCEDUREsp_update(@ordernochar(20),@remarkvarchar(200))ASBEGIN**TNOCOUNTON;updateopenquery(jt,'select*fromsz')setremark=@remarkwhereorderno=@ordernoENDgo---update**测试usesuzhou**pdateszsetremark='ocpyang'whereorderno='a001'go----6.3delete--重写触发器usesuzhougocreatetriggertr_delete_szonsuzhou.dbo.szfordeleteasdeclare@ordernochar(20)select@orderno=ordernofromdeleted;beginexecloopback.suzhou.dbo.sp_delete@ordernoendgo--存储过程usesuzhougocreatePROCEDUREsp_delete(@ordernochar(20))ASBEGIN**TNOCOUNTON;deleteopenquery(jt,'select*fromsz')whereorderno=@ordernoENDgo---delete**测试usesuzhougodeletefromszwhereorderno='a001'go
