Tx Replication from SQL Server 2008 R2 to Postgres 9.1

More
10 years 2 months ago #4532 by sqlrepl
Hello,

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) :
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:
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!

Please Log in or Create an account to join the conversation.

More
10 years 2 months ago #4533 by sqlrepl
The error msg above is for a table with 1 row.

If I try to replicate a table with > 1 rows, I get the following error msg:
ERROR: column "Col1" of relation "replicated" does not exist
LINE 1: INSERT INTO "replicated"("Col1","Col2") VALUES($1,$2)

Please Log in or Create an account to join the conversation.

More
10 years 2 months ago #5367 by Moderator
After setting transactional replication for the first time, we had to reinitialize subscription, then it works. We will repeat tests using your script, and we’ll let you know.
Thank you!

Please Log in or Create an account to join the conversation.

More
10 years 2 months ago #5490 by sqlrepl
Ok, I finally got the Tx Replication working between 2008 R2 and Postgres 9.1 with help from the Mods and Michael Giagnocavo. I will document the steps involved and gotchas shortly and post it.

Thanks Everyone!

Please Log in or Create an account to join the conversation.

More
10 years 1 month ago #6424 by Moderator
That would be great. Thank you!

Please Log in or Create an account to join the conversation.

More
10 years 1 month ago #6451 by sqlrepl
I have been running various scenarios for replication and I seem to be running into an issue when I need to change the schema of the table on postgres side after it has been created. I Drop/Create the table to change the schema. Once I create the table on Postgres side again, I get this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "PGNP" for linked server "PGNP_Samples" supplied inconsistent metadata for a column. The column "notifymebyemail" (compile-time ordinal 24) of object ""slave"."public"."Users"" was reported to have a "DBTYPE" of 11 at compile time and 130 at run time.

I tried to refresh the metadata for the PGNP Provider by running : EXEC('pgnp_refreshmetadata('''',''tablename'')') AT PGNP_SAMPLE and the error message changes to:

Msg 7355, Level 16, State 1, Line 1
The OLE DB provider "PGNP" for linked server "PGNP_Samples" supplied inconsistent metadata for a column. The name was changed at execution time.

Any ideas on how to refresh metadata if you Drop/Create a table?

Please Log in or Create an account to join the conversation.

More
10 years 1 month ago #6452 by sqlrepl
Actually, the error message is :

OLE DB provider "PGNP" for linked server "PGNP_Samples" returned message "Undefined column name "Tbl1001"."columnname"".
Msg 7321, Level 16, State 2, Line 1

Please Log in or Create an account to join the conversation.

More
10 years 1 month ago #6471 by Moderator
We have tested the stored procedure call, and it worked fine in current version: 1.4.0.3056.

EXEC('pgnp_refreshmetadata('''',''test'')') AT PGNP_SAMPLE

What version of provider are you using?

Btw, another way of refreshing metadata cache in all connected OLEDB providers is to execute following statement: NOTIFY pgnp_rtx.

Please Log in or Create an account to join the conversation.

  • Visitor
  • Visitor
9 years 7 months ago #9834 by
I used the same script in order to setup a transactionnal replication between sql 2008 r2 ( publisher) and Postgresql 9.2 .
The table MSREPL7 has been created, a row is inserted , but I coudn't find the replicated table. ( Even if I reinitialize the subscription)
I've got no error on SQLSERVER.

Thank you

Please Log in or Create an account to join the conversation.

More
9 years 6 months ago #9835 by Moderator
I do not know the reason. It can happen if a step was missed during the replication configuration. Could you check SQL Server logs, NT Events log and Postgres logs in order to see if any errors were reported?

Second thought: usually a snapshot replication is used in order to create destination table(s).

Please Log in or Create an account to join the conversation.

  • Visitor
  • Visitor
9 years 4 months ago #9878 by
Hello,

I have these errors :
invalid parameter
when the agent try to bulk copy

and right after: ERROR: relation "dossier" already exists (Source : MSSQLServer, Numéro d'erreur : -2147217900)

Thank you

Please Log in or Create an account to join the conversation.

More
9 years 3 months ago #9908 by Moderator
Would you send more details on how to reproduce the issue to Support? We need configuration script, table(s) DDL, sample rows. Thank you!

Please Log in or Create an account to join the conversation.

Time to create page: 0.086 seconds
Powered by Kunena Forum