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.
