erro ao configurar a replicação do mssql para o mysql

Question

Estou tentando configurar a replicação do SQL Server 2005 para o MySql 5.1.53. Eu sou capaz de adicionar a publicação sem nenhum problema. No entanto, quando vou adicionar a assinatura, recebo este erro:

OLE DB or ODBC Subscribers cannot subscribe to article 'table_1' in publication 'mySqlReplicationPub' because the article has a timestamp column and the publication is 'allow_queued_tran' (allows queued updating subscriptions).

No entanto, não tenho colunas de registro de data e hora em table_1 (veja abaixo) e defini allow_qued_tran como false.

Aqui está o ddl para table_1

CREATE TABLE [dbo].[table_1](
    [objectId] [int] NOT NULL,
    [name] [varchar](50) NOT NULL,
    [aBit] [bit] NOT NULL,
    [nVar] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [objectId] ASC
)

Aqui está o meu código para criar a publicação e o assinante

-- Enable Replication
exec sp_replicationdboption 
    @dbname = N'mySqlReplication',
    @optname = N'publish', 
    @value = N'true'
    GO

-- Adding the transactional publication
exec sp_addpublication 
    @publication = N'mySqlReplicationPub', 
    @description = N'Transactional publication of database ''mySqlReplication'' from Publisher ''hostname''.', 
    @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, 
    --    @ftp_login = N'anonymous', 
    @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', 
    @autogen_sync_procs = 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'
GO


exec sp_addpublication_snapshot 
    @publication = N'mySqlReplicationPub', 
    @frequency_type = 1, 
    @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 = 0, 
    @active_end_date = 0, 
    @job_login = null, 
    @job_password = null, 
    @publisher_security_mode = 1

--Add the table to be replicated
exec sp_addarticle 
    @publication = N'mySqlReplicationPub', 
    @article = N'table_1', 
    @source_owner = N'dbo', 
    @source_object = N'table_1', 
    @type = N'logbased', 
    @description = null, 
    @creation_script = null, 
    @pre_creation_cmd = N'drop', 
    @schema_option = 0x000000000803509F, 
    --    @identityrangemanagementoption = N'manual', 
    @destination_table = N'table_1', 
    @destination_owner = N'dbo', 
    @vertical_partition = N'false', 
    @ins_cmd = N'CALL sp_MSins_dbotable_1', 
    @del_cmd = N'CALL sp_MSdel_dbotable_1', 
    @upd_cmd = N'SCALL sp_MSupd_dbotable_1'
GO


--add all of the columns to the article
exec sp_articlecolumn
    @publication = N'mySqlReplicationPub'
    ,@article = N'table_1'
    ,@refresh_synctran_procs = 1
GO

--Add the subscriber, the error is coming when running sp_addsubscription
exec sp_addsubscription 
    @publication = N'mySqlReplicationPub', 
    @subscriber = N'mysql-pc', --change to subscriber hostname
    @destination_db = N'mysqlDb', --change to subscriber db name
    @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'mySqlReplicationPub', 
    @subscriber = N'mysql-pc', --change to subscriber hostname
    @subscriber_db = N'mysqlDb', --change to subscriber db name
    @job_login = null, 
    @job_password = null, 
    @subscriber_security_mode = 0,
    @subscriber_login = N'mssql', --change
    @subscriber_password = '', --change
    @subscriber_provider = N'MSDASQL',
    @subscriber_datasrc = N'mySQLdsn', --change
    @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 = 20110922, 
    @active_end_date = 99991231, 
    @enabled_for_syncmgr = N'False', 
    @dts_package_location = N'Distributor'
GO

Qualquer ajuda seria muito apreciada.

    

Answer 1

Acredito que isso tenha algo a ver com o schema_option do sp_addarticle. Se você der uma olhada nos valores possíveis para isso, o 0x08 lida com registros de data e hora.

Aqui está o que eu tenho agora (graças ao link )

--step 1
-- Adding the transactional publication
exec sp_replicationdboption 
    @dbname = N'mySqlReplication', 
    @optname = N'publish', 
    @value = N'true'
GO

exec sp_addpublication @publication = N'mySqlReplication'
    , @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'mySqlReplication'
    , @article = N'table_1'
    , @source_owner = N'dbo'
    , @source_object = N'table_1'
    , @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

--add all of the columns to the article
exec sp_articlecolumn @publication = N'mySqlReplication'
    , @article = N'table_1'
    , @refresh_synctran_procs = 1
GO

--end step1

--step2
--add the publication snaphot
exec sp_addpublication_snapshot 
    @publication = N'mySqlReplication',
    @frequency_type = 1, 
    @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 = 0, 
    @active_end_date = 0, 
    @job_login = null, 
    @job_password = null, 
    @publisher_security_mode = 1
GO
--end step2

--step3
--add the subscriber(s)
use [bjMySqlReplication]
exec sp_addsubscription @publication = N'mySqlReplication'
    , @subscriber = N'mySQLdsn'  --system DSN
    , @destination_db = N'mySQLdb'
    , @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'mySqlReplication'
    , @subscriber = N'mySQLdsn'  --system DSN
    , @subscriber_db = N'mySQLdb'
    , @job_login = null
    , @job_password = null
    , @subscriber_security_mode = 0
    , @subscriber_login = N'mssql'
    , @subscriber_password = ''
    , @subscriber_provider = N'MSDASQL'
    , @subscriber_datasrc = N'mySQLdsn'  --system DSN
    , @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