Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Is your DSN setup for OLEDB driver or the ODBC driver? –  mrdenny Sep 22 '11 at 22:08
    
ODBC - see my working configuration below –  btenney Sep 23 '11 at 17:20

1 Answer 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
share|improve this answer
    
+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. –  RateControl Oct 14 '11 at 15:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.