我有一个
mysql数据库充当我们的websever后端.我们有我们希望将数据推送到我们的MysqL服务器的生产mssql服务器.
我已经成功地使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
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
然后后续错误是关于重复键.
解决方法
得到了解决.
sp_addrticlecolumn中的变量@force_reinit_subscription必须为null或0.
我评论说,这是一种魅力.
这是完整的工作代码：
--step 1 -- Adding the transactional publication use [repl_test] exec sp_addpublication @publication = N'Repl_test',@autogen_sync_procs = 'false' GO --add the article to the publication exec sp_addarticle @publication = N'Repl_test',@schema_option = 0x8000000,@status = 24 GO --add all of the columns to the article exec sp_articlecolumn @publication = N'Repl_test',@refresh_synctran_procs = 1 GO --end step1 --step2 --add the publication snaphot exec sp_addpublication_snapshot @publication = N'Repl_test',@publisher_security_mode = 1 GO --end step2 --step3 --add the subscriber(s) use [repl_test] exec sp_addsubscription @publication = N'Repl_test',@subscriber = N'MysqLtest',@destination_db = N'repl_test',@subscriber_type = 3 GO --add the pushing subscription agent exec sp_addpushsubscription_agent @publication = N'Repl_test',@subscriber_db = N'repl_test',@frequency_interval = 1,@dts_package_location = N'Distributor' GO --end step3
