Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby sqlrepl » Thu Apr 12, 2012 4:05 pm

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

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!
sqlrepl
 
Posts: 5
Joined: Thu Apr 12, 2012 3:48 pm

Re: Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby sqlrepl » Thu Apr 12, 2012 4:22 pm

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)
sqlrepl
 
Posts: 5
Joined: Thu Apr 12, 2012 3:48 pm

Re: Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby Moderator » Tue Apr 17, 2012 9:13 pm

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!
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Re: Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby sqlrepl » Wed Apr 18, 2012 1:36 pm

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!
sqlrepl
 
Posts: 5
Joined: Thu Apr 12, 2012 3:48 pm

Re: Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby Moderator » Thu Apr 26, 2012 7:57 am

That would be great. Thank you!
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Re: Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby sqlrepl » Wed May 02, 2012 10:41 am

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?
sqlrepl
 
Posts: 5
Joined: Thu Apr 12, 2012 3:48 pm

Re: Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby sqlrepl » Wed May 02, 2012 4:27 pm

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
sqlrepl
 
Posts: 5
Joined: Thu Apr 12, 2012 3:48 pm

Re: Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby Moderator » Wed May 16, 2012 9:09 pm

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.
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Re: Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby mpeng » Fri Nov 23, 2012 9:28 am

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
mpeng
 
Posts: 2
Joined: Thu Nov 22, 2012 7:21 am

Re: Tx Replication from SQL Server 2008 R2 to Postgres 9.1

Postby Moderator » Tue Nov 27, 2012 9:03 am

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).
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Next

Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: Google [Bot] and 1 guest

cron