SQL Server could not set the Publisher logon name for updatable subscriptions sp_link_publication

Source: Internet
Author: User
Tags ole

Original: SQL Server could not set the Publisher login for updatable subscriptions sp_link_publication

No:


Create an updatable subscription, create the publication normally, create a subscription at the Subscriber, and the last prompt completes with a warning:

Unable to set the publisher login for updatable subscriptions. You may need to use sp_link_publication to set it directly on the subscriber computer.


Suddenly remembered that this publication and subscription database is restored by other libraries, it should be an account problem:


Both the publication database and the subscription database are executed:

--View Orphaned database user exec sp_change_users_login @Action = ' report ';

The database user (Repllinkproxy) that was prompted in the discovery warning is orphaned, that is, a login account that is not associated to the local (repllinkproxy)

--database user associated to SQL Server login exec sp_change_users_login       @action = ' Update_One ',       @usernamepattern = ' Repllinkproxy ' ,    @loginname = ' repllinkproxy '; go  


-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

There is another way:

In the final step of creating a subscription, use the existing connection server name as a connection, or you may have the above error prompt!

The workaround is to set the connection security information at the Subscriber by executing the following script:

exec sp_link_publication   @publisher = ' publisher ', @publisher_db = ' publisher_db ', @publication = ' publication ', @s Ecurity_mode = Security_mode

If the following error occurs:

The OLE DB provider "SQLNCLI10" of the linked server "DB" returned the message "The transaction manager has disabled its support for remote/network transactions." "。
Msg 7391, Level 16, State 2, line 1th
The operation could not be performed because the OLE DB provider "SQLNCLI10" of the linked server "DB" could not start the distributed transaction.

The workaround is to right-click the server link, and in the server option, change the "Enable upgrade for RPC for distributed transactions" to "false"and then re-execute the above statement. or SQL Script execution

Use [master]goexec master.dbo.sp_serveroption @server =n ' linkservername ', @optname =n ' remote proc transaction Promotion ', @optvalue =n ' False ' GO




SQL Server could not set the Publisher logon name for updatable subscriptions sp_link_publication

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.