我有一个MySQL数据库充当我们的websever后端。 我们有我们的生产mssql服务器，我们希望数据推送到我们的MySQL服务器。
我已经成功地使MySQL服务器成为一个ODBC系统数据源。
我可以从我们的mssql表中执行插入更新语句到mysql表，所以我知道我确实可以通过系统ODBC连接连接到mysql服务器。
我已经build立了一个本地出版物使用：
-- 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 = N'MYSQL' , @subscriber_db = N'TestTable' , @job_login = null , @job_password = null , @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_recurrence_factor = 0 , @frequency_subday = 0 , @frequency_subday_interval = 0 , @active_start_time_of_day = 0 , @active_end_time_of_day = 235959 , @active_start_date = 20101202 , @active_end_date = 99991231 , @enabled_for_syncmgr = N'False' , @dts_package_location = N'Distributor' GO
一切都被创造出来，我确信至less有一篇文章被选中（只有一个名为“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
这个复制现在正在抱怨我桌子上有一个独特的密钥。 我觉得我还有更多的玩弄添加文章存储过程。
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中的variables@force_reinit_subscription必须为null或0。
我评论说，这是一种魅力。
这是完整的工作代码：
--step 1 -- 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 --add the article to the publication 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 = 0x8000000 , @status = 24 GO --add all of the columns to the article exec sp_articlecolumn @publication = N'Repl_test' , @article = N'TestTable' , @refresh_synctran_procs = 1 GO --end step1 --step2 --add the publication snaphot 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 --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' , @subscription_type = N'Push' , @sync_type = N'automatic' , @article = N'all' , @update_mode = N'read only' , @subscriber_type = 3 GO --add the pushing subscription agent exec sp_addpushsubscription_agent @publication = N'Repl_test' , @subscriber = N'mysqltest' , @subscriber_db = N'repl_test' , @job_login = null , @job_password = null , @subscriber_security_mode = 0 , @subscriber_login = N'root' , @subscriber_password = 'PASSWORD' , @subscriber_provider = N'MSDASQL' , @subscriber_datasrc = N'mysqltest' , @frequency_type = 64 , @frequency_interval = 1 , @frequency_relative_interval = 0 , @frequency_recurrence_factor = 0 , @frequency_subday = 0 , @frequency_subday_interval = 0 , @active_start_time_of_day = 0 , @active_end_time_of_day = 235959 , @active_start_date = 20101202 , @active_end_date = 99991231 , @enabled_for_syncmgr = N'False' , @dts_package_location = N'Distributor' GO --end step3