The subscription to publication is Invalid

Hot Topic

Activating the Queue
Dialog Security and Message Encryption
Replication of Great Plains Data
MDQ Query Question: Passing sets into a function
Service Broker Security Question
execute DBCC on linked server
How I can copy object SMO between DB and servers?
Missing SqlContext.GetCommand() in new namespace
Problem creating diagrams
Log Reader Agent Access Violation

SQL Server

Tables in reports splitting between multiple pages
Express Manager
SP2 - Need Your Feedback!
High CPU Usage SSIS Package
MSDE 2000 installation fails on Windows Vista 64-bit
auto numbering
Serious limitation in SQL server installation
Change Column Order
"Cummilative Sum"
Create New Table

The subscription to publication is Invalid

Hi,

I have two SQL 2000 Server running on Windows 2003 Standard Edtion. I am using merge option to replicate database as Transactional option is excluding some tables. Both server are not in Active Directory and I am trying to replicate over the internet. I got following error message while using Merge option:

Error Message:

The subscription to publication 'XXX' is invalid.

Error details:

The subscription to publication 'XXX' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
---------------------------------------------------------------------------------------------------------------
The remote server is not defined as a subscription server.
(Source: x.x.x.x (Data source); Error number: 14010)
---------------------------------------------------------------------------------------------------------------

Any help regarding the same will be appricated. Thanks.




Answer this question

The subscription to publication is Invalid

  • KCReed

    Hi,

    One more question: May I have few databases in transactional replication and few with with merge replication Thanks.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />



  • plague_dog32

    Did you add the subscription before you started to replicate through the internet

    Can you try doing the synchronization without Internet, i.e. through the network (LAN)

    Can you verify the subscription entries in sysmergesubscription at the publisher and the subscriber



  • Ashok Kumar Roy

    Hi,

    I have added subscription using UI i.e. Enterprise Manager. No anonymous subscription. I have tried to query sysmergesubscriptions table at published database but no help and found two rows and in subscriber column there is different entries.  Thanks for you kind help. Please let me know if you need more information.



  • JMZuniga

    Hi Mahesh,

    Following are the full result of sysmergesubscriptions table from both the server. Thanks

    Server1:

    subid partnerid datasouce_type datasource_path srvid db_name pubid Status subscriber_type subscription_type priority sync_type description login_name lastvalidation subscriber_server user_interactive_resolver publication distribute validation_level resyn_gen attempted_validation last_sysn_date last_sync_date last_sync_summary
    A9B8714D-BDC3-4051-9AB0-397F6F067A75 A9B8714D-BDC3-4051-9AB0-397F6F067A75 0  NULL 0 XXX A9B8714D-BDC3-4051-9AB0-397F6F067A75 1 1 0 100 1 NULL sa 31:04.9 Server1 0 XXX Server1 0 -1 NULL NULL NULL NULL
    E11B4086-D1D0-4EE8-A5CE-B144E2CE16B0 A9B8714D-BDC3-4051-9AB0-397F6F067A75 0  NULL 3 XXX A9B8714D-BDC3-4051-9AB0-397F6F067A75 0 2 0 0 1 NULL sa NULL Server2 IP 0 XXX Server1 0 0 NULL NULL NULL NULL

    Server2:

    subid partnerid datasouce_type datasource_path srvid db_name pubid Status subscriber_type subscription_type priority sync_type description login_name lastvalidation subscriber_server user_interactive_resolver publication distribute validation_level resyn_gen attempted_validation last_sysn_date last_sync_date last_sync_summary
    A9B8714D-BDC3-4051-9AB0-397F6F067A75 A9B8714D-BDC3-4051-9AB0-397F6F067A75 0 Server1.dbname 1 XXX A9B8714D-BDC3-4051-9AB0-397F6F067A75 1 1 0 100 1 NULL sa NULL Server1 0 XXX Server1 0 -1 NULL NULL NULL NULL
    E11B4086-D1D0-4EE8-A5CE-B144E2CE16B0 A9B8714D-BDC3-4051-9AB0-397F6F067A75 0 NULL 0 XXX A9B8714D-BDC3-4051-9AB0-397F6F067A75 1 2 0 0 1 NULL sa NULL Server2 IP 0 XXX Server1 0 -1 NULL NULL NULL NULL

    Please do let me know if you need further information. Thanks for your help with this issue.



  • Paul Programmer

    Can you run the profiler and see what is failing when the merge agent runs.

  • Kari Ko

    Hi Mahesh,

    Thanks I have checked Find following is the result:

    Server1:

    subid partnerid datasouce_type datasource_path srvid db_name pubid Status subscriber_type subscription_type priority sync_type description login_name lastvalidation subscriber_server user_interactive_resolver publication distribute validation_level resyn_gen attempted_validation last_sysn_date last_sync_date last_sync_summary
    A9B8714D-BDC3-4051-9AB0-397F6F067A75 A9B8714D-BDC3-4051-9AB0-397F6F067A75 0  NULL 0 XXX A9B8714D-BDC3-4051-9AB0-397F6F067A75 1 1 0 100 1 NULL sa 31:04.9 Server1 0 XXX Server1 0 -1 NULL NULL NULL NULL
    E11B4086-D1D0-4EE8-A5CE-B144E2CE16B0 A9B8714D-BDC3-4051-9AB0-397F6F067A75 0  NULL 3 XXX A9B8714D-BDC3-4051-9AB0-397F6F067A75 0 2 0 0 1 NULL sa NULL Server2 IP 0 XXX Server1 0 0 NULL NULL NULL NULL

    Server2:

    subid partnerid datasouce_type datasource_path srvid db_name pubid Status subscriber_type subscription_type priority sync_type description login_name lastvalidation subscriber_server user_interactive_resolver publication distribute validation_level resyn_gen attempted_validation last_sysn_date last_sync_date last_sync_summary
    A9B8714D-BDC3-4051-9AB0-397F6F067A75 A9B8714D-BDC3-4051-9AB0-397F6F067A75 0 Server1.dbname 1 XXX A9B8714D-BDC3-4051-9AB0-397F6F067A75 1 1 0 100 1 NULL sa NULL Server1 0 XXX Server1 0 -1 NULL NULL NULL NULL
    E11B4086-D1D0-4EE8-A5CE-B144E2CE16B0 A9B8714D-BDC3-4051-9AB0-397F6F067A75 0 NULL 0 XXX A9B8714D-BDC3-4051-9AB0-397F6F067A75 1 2 0 0 1 NULL sa NULL Server2 IP 0 XXX Server1 0 -1 NULL NULL NULL NULL

    Please do let me know if you need further information. Thanks for your help with this issue.

     

     



  • jn____

    I dont see anything wrong in the above.

    It somehow seems to me that the publisher server is not able to connect to the subscriber or the other way round.

    Can you try to connect (not from EM but say osql) to each of the machines from the other machine

    See if the firewall needs to be opened for the specific ports so that the servers can talk to each other.

    And when you say using internet, what eactly are you doing to use it



  • Keith Boyd -MSFT

    Can you please verify the sysmergesubscriptions entries at ublisher and subscriber

    Also use SQL Authentication to connect to the Publisher/Subscriber/Distributor when running the Merge agent.

     



  • MarkDuffy

    Yes, you can have some dbs with Tran and some others with Merge.

    Are you running the sync through the Agent or Command line.

    If command line, run select command from msdb..sysjobsteps and verify your command line matches the one there.

    Also have you enabled the subscription server to be a subscriber

    Make sure that the subscription type on Command line matches actual created subscription type (0 for Push, 1 for Pull)

    Also the full sysmergesubscriptions entries and the command line will help me to debug more.



  • henrybhavsar

    Hi Mahesh,

     

    Thanks for reply. Yes, I added the subscription before I started to replicate. Sorry I cannot syncronize withont internet as both server are over the internet and with different IP subnet. May I know where I can find "sysmergesubscription"   Many Thanks :)

    Regards,

    Gaurav



  • Mark Arend

    Firstly, if you are trying to use Web Synchronization which is a new feature in SQL Server 2005 and have SQL Server 2000 subscribers, it will not work.

    All subscribers need to be SQL Server 2005 level.

    sysmergesubscriptions can be found in the publisher and subscriber databases.

    For a pull subscription that you added you should see two entries each at the publisher and subscriber sysmergesubscriptions table.

     



  • Manabu

    sysmergesubscriptions is a table that can be queried at the published database.

    How did you add the subscription, through the UI or using TSQL   Is this an anonymous, or well-known subscription   If you added using TSQL, could you tell us the exact TSQL statements that were executed


  • Ed Casey

    Hi,

    I also try to connect using osql and successfully connected to other server. I have used following query:

    Server1:

    osql -U sa -P password -S Server2 IP

    Server2:

    osql -U sa -P password -S Server1 IP

    Both server are in different IP Subnets and have firewall between them.  One is act as Webserver while other is specially used as subscriptions. Thanks for your help



  • viewpl

    Hi,

    I am not trying Web Syncronization as my both servers are MSSQL 2000. They both are in different IP subnet and have firewall between them. I can access both server visa versa from Enterprise manager. Transactional Replication is working fine but there is problem only probelm with merge replciation.  Another thing is that I cannot add few databases with Transactional Replication because many tables and views have excluded when I choose Transactional option.  Many Thanks for your help.



  • The subscription to publication is Invalid
Answer this question