Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transactional publication with updatable subscriptions on SQL2012 release. Expand / Collapse
Author
Message
Posted Thursday, December 05, 2013 2:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:00 AM
Points: 153, Visits: 573
Hi,

We have a 4 serves around the world that needs to be in updatable subscriptions.

Server location & versions:
2 servers in North America – SQL server 2012
1 server in Europe – SQL server 2008 R2
1 server in Singapore – SQL server 2008 R2

I created Transactional publication with updatable subscriptions (2 articles ) on one of the North America sever 2012 (by script method) and adding remaining 3 server (2012 & 2008R2) has Subscribers. Most of the major load happens on North America sever 2012.
I was able to do it good, it was working has I was thinking.

There is Stored procedure (web application) using MERGE (Performs insert, update, or delete operations on a target table based on the results of a join with a source table) to load tables.
When we try using MERGE the Queue Reader agent fails below is error message. (Without MERGE it works fine).

Error Message:
The Queue Reader Agent has encountered the error ''Cannot insert the value NULL into column ''msrepl_tran_version'', table ''DataBasename.dbo.conflict_TableName''; column does not allow nulls. INSERT fails.'' when conn

My question:
1. In Transactional publication with updatable subscriptions, Merge operation can’t be used?
2. I am not sure , which is the best way to do Transactional publication with updatable subscriptions on SQL12 (by script method) or I can user Peer –to-Peer Replication?
3. Any help error message.
4. Where should be the conflict tables created. For my Publication conflict tables are on publication is it the right way?
5. We need to use the MERGE because, it save lots of data and would save time for better performance.


Any help is greatly appreciated.

Thanks.
Post #1520336
Posted Friday, December 06, 2013 7:42 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 21, 2014 2:37 AM
Points: 6, Visits: 67
From books online:
The MERGE statement should not be used when using queued updating replication. The MERGE and queued updating trigger are not compatible. Replace the MERGE statement with an insert or an update statement.


[url=http://technet.microsoft.com/en-us/library/ms151718.aspx][/url]
Post #1520587
Posted Friday, December 06, 2013 8:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:00 AM
Points: 153, Visits: 573
Thanks for your reply.

when we run the MERGE on SQL server 2012 it runs fine.
But when it runs on SQL server 2008R2, the queue reader agent fails with

Error Message:
The Queue Reader Agent has encountered the error ''Cannot insert the value NULL into column ''msrepl_tran_version'', table ''DataBasename.dbo.conflict_TableName''; column does not allow nulls. INSERT fails.'' when conn
Post #1520598
Posted Friday, December 06, 2013 9:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:00 AM
Points: 153, Visits: 573
Merge statement is compatible with Peer to Peer replication?
Post #1520643
Posted Monday, December 09, 2013 8:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:00 AM
Points: 153, Visits: 573
HELP Please..!

Merge statement is compatible with Peer to Peer replication?
Post #1521151
Posted Wednesday, December 11, 2013 9:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:00 AM
Points: 153, Visits: 573
any help please.
Post #1521948
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse