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 12»»

Replication Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 10:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:17 AM
Points: 36, Visits: 182
After setting up Replication (Transactional Publication with updatable Subscription)
between two instances one instance being both Distributor and Publisher, with the other being a Subscriber running on my local machine.
I managed to replicate data between instances from the publisher Instance, however whenever I try to update the table that is being replicated at the subscriber which should then instantly update the publisher I get the errors below.


ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) --- Server 'repl_distributor' is not configured for DATA ACCESS. (Microsoft SQL Server, Error: 7411)

The RPC security information for the Publisher is missing or invalid
Use
sp_link_publication to specify it
Publisher server is not configured for RPC
The publisher is set up for RPC and the distributor was configured for data access. Not sure how to replicate updates from subscriber back to publisher.
Post #1362825
Posted Wednesday, September 26, 2012 1:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 11:29 PM
Points: 459, Visits: 1,889
kingdonshel (9/21/2012)
After setting up Replication (Transactional Publication with updatable Subscription)
between two instances one instance being both Distributor and Publisher, with the other being a Subscriber running on my local machine.
I managed to replicate data between instances from the publisher Instance, however whenever I try to update the table that is being replicated at the subscriber which should then instantly update the publisher I get the errors below.


ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) --- Server 'repl_distributor' is not configured for DATA ACCESS. (Microsoft SQL Server, Error: 7411)

The RPC security information for the Publisher is missing or invalid
Use
sp_link_publication to specify it
Publisher server is not configured for RPC
The publisher is set up for RPC and the distributor was configured for data access. Not sure how to replicate updates from subscriber back to publisher.


I have never worked with this replication scenario, however the error message to me is somewhat familiar. When you create replication, a linked server is created on the publisher end for the subscriber.

Do 1 thing. Set up a demo replication in test environment. Go the the linked server. Right click on that and in properties enable RPC and Data access. Let me know if this works.

Thanks
Chandan
Post #1364484
Posted Wednesday, September 26, 2012 1:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:17 AM
Points: 36, Visits: 182
I did have that before if you look at the bottom of my posting you can see where I said RPC is already set to True.
Post #1364903
Posted Wednesday, September 26, 2012 10:34 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 11:29 PM
Points: 459, Visits: 1,889
Sorry about that. Did you turn on the data access true as well?

Post #1365027
Posted Wednesday, September 26, 2012 10:38 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 11:29 PM
Points: 459, Visits: 1,889
My gut feeling is that you can get this done by tweaking linked server only. I will try to re-produce this in my environment today.

Thanks
Chandan
Post #1365029
Posted Wednesday, September 26, 2012 11:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:17 AM
Points: 36, Visits: 182
Yes I have set data access to true as well , however I dont see why I should have to if the link server is created automatically for replication from what I can see DATA ACCESS should be set to true, anyway I did.
When you try this in you environment remembr the type of Replication option I was trying was
Transactional Publication with updatable Subscriptions.
This allows changes to replication back to the publisher even when changes are made at the subscriber, it also allows changes to be replicated to the Subscirber even when updates is made at the Publisher.

I think part of the problem is the link server created on the distributer (repl_distributor) is run under the distributor_admin user --see Logins under Security. However the link server at the Subscriber is running under a different user.This happens automatically and there is no access to the password for these usernames.
I tried to set up my own Subscriber link server and tried to set up local to remote server mappings based on my admin user account however the link server could not connect ,I got the RPC Error message.

Also remember I am not on a network I am using my local machine which is running 2 Instanced.
Post #1365037
Posted Saturday, September 29, 2012 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 30, 2012 9:59 AM
Points: 1, Visits: 2
Hello,

Did you started MSDTC, if not please start and allow inbound and outbound.
I think your problem should be resolved.
Post #1366230
Posted Sunday, September 30, 2012 11:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:17 AM
Points: 36, Visits: 182
Thanks a million Raj, this works I droppped all link servers, users and logins created by replication, started MDTC in Services and recreated the type of replication described in my initial post. Now when I update the replicated table at the subcriber or publisher the updated table changes are updated in both places. Thank you so much.
This is the first time any of these sites has ever given me a solution that works, and thanks to for the suggestions from other user they helped to improve my knowledge.
Post #1366276
Posted Sunday, September 30, 2012 11:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:17 AM
Points: 36, Visits: 182
Just to mention,I did not see any inbound or outbound option anywhere, I am using SQL Server 2008.
Post #1366278
Posted Monday, October 1, 2012 2:34 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 11:29 PM
Points: 459, Visits: 1,889
kingdonshel (9/30/2012)
Just to mention,I did not see any inbound or outbound option anywhere, I am using SQL Server 2008.


I am glad that this worked for you. MSDTC options would be configured from windows level. Control panel has some option called 'add windows component' and there you can configure msdtc. Use web to find exact options to configure it for your OS.

Cheers!
Chandan
Post #1366392
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse