我已经成功地使MysqL服务器成为ODBC系统数据源.

我可以从我们的mssql表到MysqL表中执行插入更新语句,所以我知道我确实可以通过系统ODBC连接连接到MysqL服务器.

我使用以下方法设置了本地出版物：

-- Adding the transactional publication
use [repl_test]
exec sp_addpublication @publication = N'Repl_test',@description = N'Transactional publication of database',@sync_method = N'concurrent_c',@retention = 0,@allow_push = N'true',@allow_pull = N'false',@allow_anonymous = N'true',@enabled_for_internet = N'false',@snapshot_in_defaultfolder = N'true',@compress_snapshot = N'false',@ftp_port = 21,@allow_subscription_copy = N'false',@add_to_active_directory = N'false',@repl_freq = N'continuous',@status = N'active',@independent_agent = N'true',@immediate_sync = N'true',@allow_sync_tran = N'false',@allow_queued_tran = N'false',@allow_dts = N'false',@replicate_ddl = 0,@allow_initialize_from_backup = N'false',@enabled_for_p2p = N'false',@enabled_for_het_sub = N'true',@autogen_sync_procs = 'false'
GO

我添加了快照代理：

exec sp_addpublication_snapshot @publication = N'Repl_test',@frequency_type = 4,@frequency_interval = 4,@frequency_relative_interval = 1,@frequency_recurrence_factor = 0,@frequency_subday = 4,@frequency_subday_interval = 1,@active_start_time_of_day = 0,@active_end_time_of_day = 235959,@active_start_date = 0,@active_end_date = 0,@job_login = null,@job_password = null,@publisher_security_mode = 1
GO

我已经为非mssql服务器添加了订阅：

use [repl_test]
exec sp_addsubscription @publication = N'Repl_test',@subscriber = N'MysqL',@destination_db = N'TestTable',@subscription_type = N'Push',@sync_type = N'automatic',@article = N'all',@update_mode = N'read only',@subscriber_type = 1

我还设置了推送订阅代理：

exec sp_addpushsubscription_agent @publication = N'Repl_test',@subscriber_db = N'TestTable',@subscriber_security_mode = 0,@subscriber_login = N'root',@subscriber_password = 'PASSWORD',@subscriber_provider = N'MSDAsql',@subscriber_datasrc = N'MysqLtest',@frequency_type = 64,@frequency_interval = 0,@frequency_relative_interval = 0,@frequency_subday = 0,@frequency_subday_interval = 0,@active_start_date = 20101202,@active_end_date = 99991231,@enabled_for_syncmgr = N'False',@dts_package_location = N'Distributor'
GO

一切都是顺利创建的,我确保选择了至少一篇文章(只有一个名为“TestTable”的表.

运行复制监视器时,我得到以下错误：

Error messages:
The process could not connect to Subscriber 'MysqL'. (Source: MSsql_REPL,Error number: MSsql_REPL20084)
Get help: http://help/MSsql_REPL20084
 Data source name not found and no default driver specified (Source: MSsqlServer,Error number: IM002)
Get help: http://help/IM002

我想在“@subscriber_provider”和“@subscriber_datasrc”中搞砸了.如果无法通过复制完成,我们将不得不设置某种sql代理作业来进行更新/复制.

欢迎任何帮助.

UPDATE1
我在“TestTable”的初始复制中取得了成功.我不得不使用sp_addarticle的存储过程,以便复制不会在连接时删除MysqL表.

exec sp_addarticle @publication = N'Repl_test',@article = N'TestTable',@source_owner = N'dbo',@source_object = N'TestTable',@type = N'logbased',@pre_creation_cmd = N'none',@ins_cmd = N'sql',@del_cmd = N'sql',@upd_cmd = N'sql',@schema_option = 0x20025081,@status = 24
GO

复制现在抱怨我的表中有一个dupe唯一键.我想我仍然可以更多地使用添加文章存储过程.

UPDATE2
在初始复制期间接收以下错误

You have an error in your sql Syntax; check the manual that corresponds to your MysqL server version for the right Syntax to use near '"MSREPL7" set xactts = _binary'\0\0\0}\0\04\0\0\0\0',timecol = {ts '2010-12-' at line 1 (Source: MSsql_REPL,Error number: MSsql_REPL20046)
Get help: http://help/MSsql_REPL20046

然后后续错误是关于重复键.