error creating a new pull subscription

Hi

Using RMO, VB.net 2005 and SQL 2005

Trying to create a new pull subscription programatically - merge HTTPS ovfr the internet

Works fine when within out internal network - but when externally there is a problem....

I'm using the code from BOL - using their values - but pretty much cut and pasted.

' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publisherName As String = instanceName
Dim subscriberName As String = instanceName
Dim subscriptionDbName As String = "AdventureWorksReplica"
Dim publicationDbName As String = "AdventureWorks"
Dim hostname As String = "adventure-works\garrett1"

'Create connections to the Publisher and Subscriber.
Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)
Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As MergePublication
Dim subscription As MergePullSubscription

Try
' Connect to the Subscriber.
subscriberConn.Connect()

' Ensure that the publication exists and that
' it supports pull subscriptions.
publication = New MergePublication()
publication.Name = publicationName
publication.DatabaseName = publicationDbName
publication.ConnectionContext = publisherConn

This last line gives me an error:

+ InnerException {"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"} System.Exception
The server/instance is obviously the name of the internal server / sql instance

What next - yikes - I've got to deliver this tomorrow

thanks as always.
BruceDoes the publisher allow remote connections as per the error message?

If that is the case, use SAC (Surface Area COnfiguration) to enable the remote connections, recycle the SQL server and try connecting again.

To access it Click on Start-->All Programs-->Microsoft SQL Server-->Configuration Tools-->SQL Server Surface Area Configuration-->Surface Area Configuration for Services and Connections-->DataBase Engine-->Remote Connections-->Local and Remote conenctions-->Using both TCP/IP and named pipes.
Then recycle the server|||

Hi

It was already marked as allowing remote connections, but named pipes was not enabled. I enabled named pipes and restarted but not difference. Looking at the code, I can't really see how it could work !

The sample code seems to assume that the server/instance is reachable from the client (unless I'm missing something) - in order to validate the publication.

Given that we are trying to synch via HTTPS and at this point no mention has been made of the URL, isn't the sample code a little bit wrong - again I'm not having the cleverest of weeks so I may be talking rubbish..

Look forward to your feedback

Bruce

|||When you are trying to create a subscription, it does not go through HTTPS. You need a connection to the publisher to first create the subscription. Only the sync uses HTTPS and the IIS machine. Alternatively you could create subscription by any other means and use HTTPS just for synching.

You could try something else, like creating a publication on the publisher using similar code and see if your connections to the publisher are going through from the machine you are using.|||Hi

So are you saying that if the machine is not on the network / a trusted connection, in order create a subscription then port 1433/1434 will have to be open ?

I hope not.

thanks
Bruce|||

try to set the subscription as anonymous, otherwise, when the sub.create() was called, it will try to connect to the publisher and setup the subscription entry there, hence the error.

sub.SubscriptionType = SubscriptionOption.Anonymous

Hope this helps.

Thanks
Yunwen

|||I can see BOL has been updated recently with this sort of scenario (web synch where the subscriber does not have a trusted connection) - that's great and responsive...

So can I take it when I create the subscription I can set the subscription.syncType to indicate whether I have the initial data ?

Also, one thing I noticed is that after I create the subscription this way (subscriberType being anonymous), that the subscription no longer shows up in the enumeration of the subscriberSubscriptions - is this a bug or is there another way of finding out whether the subscription has been created.....

Dim serverConnection As New ServerConnection(m_subscriberName)serverConnection.Connect()

Dim replicationServer As New ReplicationServer(serverConnection)availableSubscriptions = replicationServer.EnumSubscriberSubscriptions("", Convert.ToInt32(2))

For Each subscriber In availableSubscriptions
... nothing gets returned in the enumeration.....
Next

Thanks again.
Bruce|||

the syncType is actually used to determine if the initial data needed to be downloaded to the subscriber ( please refer to sp_addmergepullsubscription for details ).

regarding with the EnumSubscriberSubscriptions() method, it is equivalent to the sp_MSenumallsubscriptions ( you can give it a try ). if your call this method with the publisher, in this case, you will not get the anonymous subscriptions. you can call this method on a subscriber server to get the info you need. Please let us know if the RMO method returns anything different from what you get from the SP call.

Hope this helps.

Thanks
Yunwen

|||Hi

I ran sp_MSenumallsubscriptions on the subscriber and it returned no entries. As did EnumSubscriberSubscriptions()

However, when I was creating the subscription on the internal network and was able to connect to the publisher prior to creating the subscription, then EnumSubscriberSubscriptions() did return the subscription details.....

I am executing this method against the subscriber (as per the code in my previous post)

thanks
Bruce|||

Thanks Bruce for reminding me to take a careful look at your code. This actually turn out to be a bug in replication( at least it is a document bug). For now, you can try to pass in 3 for the subscriptionType in EnumSubscriberSubscriptions() to get the subscription info returned. Similarly, you need to specify @.subscription_type='both' for the SP call.

Sorry for the inconvenience. Please let us know if there is any other issues you encountered in your application.

Thanks again for helping us to make it better.

Yunwen