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 ransp_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

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

error applying initial snapshot

Hi

Using HTTPS web synchronisation - merge replication.

I have a very strange error where it bombs out half-way through downloading the snapshot

Downloaded a total of 3 chunks.
Downloaded snapshot file 'snapshot.pre'.
Downloaded snapshot file 'myView_6.sch'.
Downloaded snapshot file 'table2_2.sch'.
Downloaded snapshot file 'table2_2.cft'.
Downloaded snapshot file 'table3_3.sch'.
Downloaded snapshot file 'table3_3.cft'.
Downloaded snapshot file 'mytestProc1_4.sch'.
Downloaded snapshot file 'mytestProc3_5.sch'.
Downloaded snapshot file 'MSmerge_tombstone.bcp'.
Downloaded snapshot file 'MSmerge_contents90_forall.bcp'.
Downloaded snapshot file 'MSmerge_genhistory90.bcp'.
Downloaded snapshot file 'sysmergesubsetfilters90.bcp'.
Downloaded snapshot file 'table2_2.bcp'.

A failure occurred when accessing 'MSmerge_contents_table290_forall.bcp' due to an operating system error [3='The system cannot find the path specified.'] during Web synchronization. Ensure that the -InternetLogin user when using basic authentication and the user running the merge when using Windows integrated authentication has been granted access to the snapshot share.

A failure occurred when accessing 'MSmerge_contents_table290_forall.bcp' due to an operating system error [3='The system cannot find the path specified.'] during Web synchronization. Ensure that the -InternetLogin user when using basic authentication and the user running the merge when using Windows integrated authentication has been granted access to the snapshot share.

The processing of the response message failed.

I've tried compressing the snapshot, and can see the bcp file in the snapshot.

HELP !!!

Bruce...

Did you have a snapshot agent job (from SQL Agent) while merge is running? Was the job disabled? It is very likely that some process is holding that file when downloading the snapshot files.

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

No - definitely not

The subscriptions is setup as follows:

subscription.CreateSyncAgentByDefault = False -- I wrote a windows service to synchronise
subscription.UseWebSynchronization = True
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous

We've tried compressing and not-compressing the snapshot....

It used the snapshot fine when initialising the subscription - it's only after we added a proc to the publication and recreated the subscription. We've tried a number of times to synchronise - we've even rebooted the server (publisher)...

Thanks
Bruce

|||

Is it necessary to re-create the snapshot after adding a SP to the publication?

Also I am not sure why you need to recreate the subscription in this scenario?

Thanks.

|||

Yes it is necessary - this is covered in BOL.

I didn't say we recreated the subscription in this scenario

Thanks
Bruce

|||

Let us get back to your original question.

1. Since you used basic authentication in the web sync, so I assuem you have provided NT user name and password to access the web virtual directory.

2. Is 'MSmerge_contents_table290_forall.bcp' a big file to get downloaded? Please also check the timeout value on both the web server and the merge subscription.

3. I assum you have tried multiple times of the initial snapshot. Is it always failed on the same file ('MSmerge_contents_table290_forall.bcp' )?

Thanks.

|||

1. yes

2. no - it is not timing out- it even downloads the cabinet file with the file in it

3. yes

|||

Let us try to isolate this problem. Does this problem also appear if you run the merge agent to sync in fully connected mode, not web sync?

Thanks.

|||

Hi

We don't create the sync agent - so it a bit hard to test - some of our subscribers are sqlExpress - which doesn't have the agent....

(see earlier append - (subscription.CreateSyncAgentByDefault = False)

Bruce

|||

For debugging purpose, you can start the initial sync by invoking replmerg.exe. This executable should be available on SQL Express.

To run the sync agent in fully connected mode, you may want to try

replmerg.exe" -Publisher [publisher server name] -PublisherDB [publisher database name] -Publication [publication name] -PublisherSecurityMode 0 -PublisherLogin [xxxxxx] -PublisherPassword xxxxxx -Distributor [distributor server name] -DistributorLogin [xxxxxx] -DistributorPassword xxxxxx -DistributorSecurityMode 0 -Subscriber [subscriber server name] -SubscriberDB [subscriber database name] -SubscriberLogin [xxxxxx] -SubscriberPassword xxxxxx -SubscriberSecurityMode 0 -SubscriptionType (?) -QueryTimeout 0 -LoginTimeout 0

To run the sync agent in web sync mode, additional parameters needed as

-InternetURL https://your_web_server_name/virtual_directory_path/replisapi.dll -InternetTimeout 5000 -InternetLogin [domain user name] -InternetPassword [domain user password].

Let's see if the same problem shown again.

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Ok

I've spent most of the day on this problem (and another one you are also helping me on!)

The problem is such:

1. The initial snapshot downloads fine

2. If I add a proc to the publication, I need to recreate the snapshot - the next time it sync's I get the error listed earlier (about not being able to find the file etc)

3. If I mark the subscription for initilization, it works successfully

Sorry if I've been obtuse in explaining this. The only difference in my code in scenarios 2. and 3. is the addition of the following line:

subscription.Reinitialize(False)

All other parameters are the same - this is what is so strange...

4. Also - if I mark all subscriptions to be reinitialized (at the publisher) - it is able to download the snapshot ok next time we try and synchronise at the subscriber.

Bruce

|||

Hello Bruce,

1. Looks like you got a workaround already.

2. I still have no luck to repro your scenario at this moment but I will continuous to try.

3. Can you profile (if you prefer) the TSQLs which a "NEW" SP is added to the existing publication. Particularly when sp_addmergearticle is invoked, what is the value set for @.force_reinit_subscription? If you got "1", can you somehow modify the RMO code to make it "0" and see if this issue still occurs.

Thanks.

Leo

This posting is provided "AS IS" with no warranties, and confers no rights.