I am trying to setup transactional replication between SQL Server 2008 R2 Publisher, SQL Server 2008 R2 Distributor and a Postgres 9.1 subscriber using Trial version of PGNP OLEDB Provider v1.4 for PostgreSQL, Server Edition. I have tried with 1 row in the table on the subscriber as well as > 1 rows. However, initialization fails and I get this error in the details panel for Replication Monitor(where 'replicated' is the name of the table) :
- Code: Select all
Command attempted:
SELECT * FROM "replicated" WHERE 0 = 1
(Transaction sequence number: 0x00000046000001B0000300000000, Command ID: 1)
Error messages:
Undefined table replicated (Source: MSSQLServer, Error number: 2)
Get help: http://help/2
I have a linked server setup on Distributor and Publisher to the postgres host using the PGNP OLEDB provider and they work fine.
These are the scripts I am using to setup Replication:
- Code: Select all
use [Replication_Test]
GO
exec sp_replicationdboption @dbname = N'Replication_Test', @optname = N'publish', @value = N'true'
GO
exec sp_addpublication @publication = N'pgnptrans1',
@description = N'Transactional publication of database ''Replication_Test'' from Publisher ''Publisher_Host''.',
@sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @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 = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'pgnptrans1',
@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
exec sp_addarticle @publication = N'pgnptrans1', @article = N'replicated',
@source_owner = N'dbo', @source_object = N'replicated', @type = N'logbased',
@description = null, @creation_script = null, @pre_creation_cmd = N'none', @schema_option =
0x000000000803509D,
@identityrangemanagementoption = N'manual', @destination_table = N'replicated', @destination_owner =
N'dbo', @vertical_partition = N'false'
GO
exec sp_changepublication N'pgnptrans1', N'enabled_for_het_sub', N'true',
@force_invalidate_snapshot = 1, @force_reinit_subscription = 1
------------------------------------------------------------------------------
exec sp_addsubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1', @destination_db =
N'slave',
@subscription_type = N'Push',
@sync_type = N'none',
@article = N'all', @update_mode = N'read only', @subscriber_type = 3
exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1',
@destination_db = N'slave', @article = N'all',
@property=N'subscriber_login', @value=N'sqlrepl'
exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1',
@destination_db = N'slave', @article = N'all',
@property=N'subscriber_password', @value=N'sqlrepl'
exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1',
@destination_db = N'slave', @article = N'all',
@property=N'subscriber_location', @value=N''
exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1',
@destination_db = N'slave', @article = N'all',
@property=N'subscriber_datasource', @value=N'10.10.10.1'
exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1',
@destination_db = N'slave', @article = N'all',
@property=N'subscriber_provider', @value=N'PGNP'
exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1',
@destination_db = N'slave', @article = N'all',
@property=N'subscriber_providerstring',
@value=N'PORT=5432;CNV_SPECIAL_FLTVAL=ON;'
exec sp_changesubscription @publication = N'pgnptrans1', @subscriber = N'10.10.10.1',
@destination_db = N'slave', @article = N'all',
@property=N'subscriber_catalog', @value=N'slave'
exec sp_addpushsubscription_agent @publication = N'pgnptrans1',
@subscriber = N'10.10.10.1', @subscriber_db = N'slave',
@job_login = null, @job_password = null, @subscriber_security_mode = 0,
@subscriber_provider = N'PGNP',
@subscriber_datasrc = N'10.10.10.1',
@subscriber_location =N'',
@subscriber_provider_string = N'PORT=5432;CNV_SPECIAL_FLTVAL=ON;',
@subscriber_catalog = N'slave',
@subscriber_login = N'sqlrepl', @subscriber_password =N'sqlrepl',
@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 = 20090715,
@active_end_date = 99991231, @enabled_for_syncmgr = N'False',
@dts_package_location = N'Distributor'
GO
Is the error because of some limitation of the trial version or am I doing something wrong?
Thanks!
