2

I'm trying to setup replication from SQL Server 2005 to MySql 5.1.53. I am able to add the publication without any problem. However, when I go to add the subscription I get this error:

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).

However, I have no timestamp columns in table_1 (see below) and I have set allow_qued_tran to false.

Here is the ddl for 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 )

Here is my code to create the publication and subscriber

-- 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

Any help would be greatly appreciated.

Improve this question
2
  • Is your DSN setup for OLEDB driver or the ODBC driver? Commented Sep 22, 2011 at 22:08
  • ODBC - see my working configuration below Commented Sep 23, 2011 at 17:20

1 Answer 1

Reset to default
1

I believe this has something to do with the schema_option of sp_addarticle. If you go look at the possible values for this, 0x08 deals with timestamps.

Here is what I have that is now working (thanks to http://ratecontrol.blogspot.com/2010/12/one-way-transactional-replication-from.html)

--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
Improve this answer
1
  • +1 for finding what I wrote :) Also, if that schema_option value isn't working for you, try 0x8000000. Being a jerk, I haven't updated my page to reflect my more fiddling around. Commented Oct 14, 2011 at 15:51

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.