Error during merge web sync

Hello,

We have 5 subscribers trying to replicate via web synchronization. Two of the subscribers get the following message and the other three are fine. Any ideas on what the problem is or where to troubleshoot? Thanks in advance.

John

Error on client output:

The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Are you using a mix of .NET 1.1 and .NET 2.0 in your applications? If so, that can cause this error to happen. Try migrating your application to .NET 2.0 completely and see if that fixes your problem.|||

Mahesh,

Our app does uses a class that was developed using 1.1. However this class has nothing to do with replication. Could this still cause this error?

John

|||

On the IIS machine if there is an app that uses .NET 1.1, it could cause this to happen.

Another way to troubleshoot is to run https://Machine.domain/virtualDir/replisapi.dll?diag and you should see SUCCESS in all the class instantiation on that page. If not, then that is the cause of the problem.

|||

Mahesh,

This is a web server box that only has two virtual directories. Both are used for replication. One for sqlexpress replication and the other for WM 5.0 devices. We get successes from both sqlexpressurl/replisapi.dll?diag and wm50url/sqlcesa30.dll?diag, with the exception of the reconciler for the wm50url/sqlcesa30.dll?diag for version 8.0 which we are not using. This sites are all running asp 2.0

John

|||I have seen typically that once is 3 times you can get successes. Other times it could fail to load the dll and hance cause the sync to fail. Can you try on a different IIS box that doesnt have anything except the virtual directory to rule out this possibility?|||Hi John,

I have had this exact same message. Basically this occured because changes to the publisher database meant the subscribers required a new data partition snapshot. I pre-generated the snapshot and I have the option to allow subscribers to auto generate snapshots switched off. When the subscriber connects it does not recognise and hence does not download the pre-generated snapshot file and there for began the process of enumerating all the data that is needed for the subscriber. Immediately after downloading x number of chunks it then pops the same messaqge you receive.

If I was to switch auto generate snapshots option on then the subscriber happily runs the snapshot agent then downloads and applies it as expected. This was not ideal as we did not want auto-snapshot on for server load reasons.

We were at this timeusing FTP not UNC for delivery of snapshots. We had trouble with UNC shares between the servers which forced our hand on this. I spent two weeks in conversation with Microft Tech Support, until I tried finally to solve the UNC issue. I managed to get the UNC share visible from our webserver and hey presto it sorted our problem out and the subscribers happily retrieve pre-generated snapshots.

Not sure if this is anyway similar situation but might be useful

Cheers
Rab|||

Rab,

I'll give that a try thanks for the input I appreciate it.

John

|||

Mahesh,

We are having the same problem. The client application is solely 2.0 framework, but the client unit also has framework 1.1 for some other applications not related to the replication process. The webserver is solely 2.0 framework and doesn't even have 1.1 installed on the machine. Is there another reason for these errors? It looks like we get the 14151 error first the the web synchronization right after it. SQL 2k5 SP1.

Server logs:

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Replication-Replication Merge Subsystem: agent INSTANCE_NAME failed. The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Error: 14151, Severity: 18, State: 1.

Error during merge web sync

Hello,

We have 5 subscribers trying to replicate via web synchronization. Two of the subscribers get the following message and the other three are fine. Any ideas on what the problem is or where to troubleshoot? Thanks in advance.

John

Error on client output:

The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Are you using a mix of .NET 1.1 and .NET 2.0 in your applications? If so, that can cause this error to happen. Try migrating your application to .NET 2.0 completely and see if that fixes your problem.|||

Mahesh,

Our app does uses a class that was developed using 1.1. However this class has nothing to do with replication. Could this still cause this error?

John

|||

On the IIS machine if there is an app that uses .NET 1.1, it could cause this to happen.

Another way to troubleshoot is to run https://Machine.domain/virtualDir/replisapi.dll?diag and you should see SUCCESS in all the class instantiation on that page. If not, then that is the cause of the problem.

|||

Mahesh,

This is a web server box that only has two virtual directories. Both are used for replication. One for sqlexpress replication and the other for WM 5.0 devices. We get successes from both sqlexpressurl/replisapi.dll?diag and wm50url/sqlcesa30.dll?diag, with the exception of the reconciler for the wm50url/sqlcesa30.dll?diag for version 8.0 which we are not using. This sites are all running asp 2.0

John

|||I have seen typically that once is 3 times you can get successes. Other times it could fail to load the dll and hance cause the sync to fail. Can you try on a different IIS box that doesnt have anything except the virtual directory to rule out this possibility?|||Hi John,

I have had this exact same message. Basically this occured because changes to the publisher database meant the subscribers required a new data partition snapshot. I pre-generated the snapshot and I have the option to allow subscribers to auto generate snapshots switched off. When the subscriber connects it does not recognise and hence does not download the pre-generated snapshot file and there for began the process of enumerating all the data that is needed for the subscriber. Immediately after downloading x number of chunks it then pops the same messaqge you receive.

If I was to switch auto generate snapshots option on then the subscriber happily runs the snapshot agent then downloads and applies it as expected. This was not ideal as we did not want auto-snapshot on for server load reasons.

We were at this timeusing FTP not UNC for delivery of snapshots. We had trouble with UNC shares between the servers which forced our hand on this. I spent two weeks in conversation with Microft Tech Support, until I tried finally to solve the UNC issue. I managed to get the UNC share visible from our webserver and hey presto it sorted our problem out and the subscribers happily retrieve pre-generated snapshots.

Not sure if this is anyway similar situation but might be useful

Cheers
Rab
|||

Rab,

I'll give that a try thanks for the input I appreciate it.

John

|||

Mahesh,

We are having the same problem. The client application is solely 2.0 framework, but the client unit also has framework 1.1 for some other applications not related to the replication process. The webserver is solely 2.0 framework and doesn't even have 1.1 installed on the machine. Is there another reason for these errors? It looks like we get the 14151 error first the the web synchronization right after it. SQL 2k5 SP1.

Server logs:

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Replication-Replication Merge Subsystem: agent INSTANCE_NAME failed. The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server.

Date 3/27/2007 10:14:47 PM
Log SQL Server (Current - 3/28/2007 6:00:00 AM)

Source spid102

Message
Error: 14151, Severity: 18, State: 1.

Error during Merge Replication initialization

Hi,
We had a healthy 2-way merge replication set up with a SQL 2005
publisher/distributor and a SQL 2000 subscriber which had been running for
about a week. I re-created the publication so I could add automatic identity
range management to an article. I sucessfully re-created a push subscription
to a SQL 2005 subscriber but when I tried to create one to the same SQL 2000
machine that had been quite happy a few hours earlier, I kept getting this
error during initialization: "bulk data stream was incorrectly specified as
sorted". Nothing much has changed as far as I can see. Can anyone give me
some pointers?
Thanks,
Pete
Thanks for your response.
The collations were different and, having read about a similar problem on
another newsgroup, I changed the collation on my subscriber. The problem went
away so guess that fixed it. It's odd that the subscription had been
synchronizing fine up to the point that I changed the identity range
management options because I certainly didn't change the collation in between
dropping the subscriber and re-creating it. I'm sure there's a good reason
for it but it's no longer urgent.
Pete
"JPD" wrote:

> Hi Pete,
> Are you using the same collation on both the publishing database and the
> subscribing database? Have you tried using sp_dboption to set the
> select into/bulkcopy option of the subscriber database to ON?
> Jonathan
>
> Pete wrote:
>

Error creating merge replication

Hello:
I have implemented many trasactional replications on my SQL Server 2005
standar and thay are working ok.
Now, I am trying to implement a merge replication (in the same server) of a
table with a filter and when I click the finish option of the wizard in the
last step I receive the next error:
"Object reference not set to an instance of an object. (CreatePubWizard)"
What is happening?
Please, can anybody help my to solve this problem?
Thanks in advance
Jesús
its hard to say what the problem is. Recreate your publication using the
wizard, but this time when you get to the Wizard Actions dialog, select
Generate a script file, and deselect create the publication. Click next,
give the publication a name and click Finish. Then using Query Analyzer or
SSMS recreate the publication.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cetel Sistemas" <CetelSistemas@.discussions.microsoft.com> wrote in message
news:067B6719-0D4F-4E8E-9BDB-0A8189FF981B@.microsoft.com...
> Hello:
> I have implemented many trasactional replications on my SQL Server 2005
> standar and thay are working ok.
> Now, I am trying to implement a merge replication (in the same server) of
> a
> table with a filter and when I click the finish option of the wizard in
> the
> last step I receive the next error:
> "Object reference not set to an instance of an object. (CreatePubWizard)"
> What is happening?
> Please, can anybody help my to solve this problem?
> Thanks in advance
> Jess

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 configure

Hi every1,

I have problem about configuration in Merge Replication.

First, I configure for SQL Server 2000, expand Replication> Publication>New Publication>Choose Publication Database, I choose pubs>Select Publication Type, choose Merge Replication>Specify Subscribers Type, choose Servers running SQL Server 2000 and Devices running SQL Server CE>Specify article (ex. authors)…>Finish. I configure succeeded (A snapshot of 1 article(s) is generated).

Snapshot folder reference to path: \\server_name\C$\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\Replication

Next, I configure for SQL Server Mobile Agent, Subscriber Type, choose SQL Server Mobile Edition>Web server, choose Create a new virtual directory>Alias: Replication (reference to the path: C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\Replication)..>Client Authentication, choose Client will connect anonymously….>Snapshot share access, enter \\server_name\Replication =>error: The share '\\server_name\Replication'cannot be accessed. Please specify a new share. Why that?

I want to ask u I configure that correctly? If Subscriber is Pocket PC Emulator, what is subscriber ’sproperty of SqlCeReplication (Windows PocketPC Emulator?)?

Plz help me. Thanks so much.

I need your help very much. Please.|||

Firstly you should not use \\server_name\c$\.... By C$ you are assuming that the login that syncs has access to this machine's c$.

You should use a share: \\servername\repl.

Then give this share name created in the Configure virtual directory.

And I do not understand your last question about emulator.

error configure

Hi every1,

I have problem about configuration in Merge Replication.

First, I configure for SQL Server 2000, expand Replication> Publication>New Publication>Choose Publication Database, I choose pubs>Select Publication Type, choose Merge Replication>Specify Subscribers Type, choose Servers running SQL Server 2000 and Devices running SQL Server CE>Specify article (ex. authors)…>Finish. I configure succeeded (A snapshot of 1 article(s) is generated).

Snapshot folder reference to path: \\server_name\C$\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\Replication

Next, I configure for SQL Server Mobile Agent, Subscriber Type, choose SQL Server Mobile Edition>Web server, choose Create a new virtual directory>Alias: Replication (reference to the path: C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\Replication)..>Client Authentication, choose Client will connect anonymously….>Snapshot share access, enter \\server_name\Replication =>error: The share '\\server_name\Replication'cannot be accessed. Please specify a new share. Why that?

I want to ask u I configure that correctly? If Subscriber is Pocket PC Emulator, what is subscriber ’sproperty of SqlCeReplication (Windows PocketPC Emulator?)?

Plz help me. Thanks so much.

I need your help very much. Please.|||

Firstly you should not use \\server_name\c$\.... By C$ you are assuming that the login that syncs has access to this machine's c$.

You should use a share: \\servername\repl.

Then give this share name created in the Configure virtual directory.

And I do not understand your last question about emulator.

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.

Error after Disabling Merge Replication

Hi...
I disabled merge replication via the Ent. Mgr wizard and when I try to update a table that was previously part of the replication (either via stored proc or manually in query analyzer) I get this error:
Invalid object name 'dbo.sysmergearticles'
I am guessing that there are still some references or parts of the replication that were somehow left around even after disabling replication. I don't have this table in my database anymore.
Does anyone know how to resolve this issue?
Thanks
- dw
dw,
if the database is no longer involved in replication, either as a subscriber
or as a publisher, then you can run sp_removedbreplication.
HTH,
Paul Ibison
|||It sounds like the merge triggers are still in place. To get this working correctly you should run this script in your former subscription database.
DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM syspublications
OPEN list_pubs
FETCH NEXT FROM list_pubs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping publication ' +@.name
EXEC sp_dropsubscription @.publication=@.name,
@.article='all', @.subscriber ='all'
EXEC sp_droppublication @.name
FETCH NEXT FROM list_pubs INTO @.name
END
CLOSE list_pubs
DEALLOCATE list_pubs
GO
DECLARE @.name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo 0
UNION
SELECT name FROM sysmergearticles
OPEN list_replicated_tables
FETCH NEXT FROM list_replicated_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'unmarking replicated table ' +@.name
--select @.name='drop Table ' + @.name
EXEC sp_msunmarkreplinfo @.name
FETCH NEXT FROM list_replicated_tables INTO @.name
END
CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables
GO
UPDATE syscolumns set colstat = colstat & ~4096 WHERE
colstat &4096 0
GO
UPDATE sysobjects set replinfo=0
GO
DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')
OPEN list_views
FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@.name
select @.name='drop View ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END
CLOSE list_views
DEALLOCATE list_views
GO
DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name
like 'sp_ins_%' or name like 'sp_MSdel_%' or name
like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@.name
select @.name='drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END
CLOSE list_procs
DEALLOCATE list_procs
GO
DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'
OPEN list_conflict_tables
FETCH NEXT FROM list_conflict_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@.name
select @.name='drop Table ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_conflict_tables INTO @.name
END
CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables
GO
UPDATE syscolumns set colstat=2 WHERE name='rowguid'
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_constraints CURSOR FOR
select object_name(sysobjects.parent_obj), sysobjects.name
from sysobjects, syscolumns where sysobjects.type ='d'
and syscolumns.id=sysobjects.parent_obj
and syscolumns.name='rowguid'
OPEN list_rowguid_constraints
FETCH NEXT FROM list_rowguid_constraints INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid constraints ' +@.name
select @.name='ALTER TABLE ' + rtrim(@.name ) + '
DROP CONSTRAINT ' +@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_constraints INTO
@.name, @.constraint
END
CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'
OPEN list_rowguid_indexes
FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid indexes ' +@.name
select @.name='drop index ' + rtrim(@.name ) + '.'
+@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
END
CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes
GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1
OPEN list_rowguid_columns
FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + '
drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END
CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns
GO
DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM syssubscriptions
GO
DELETE FROM sysarticleupdates
GO
DELETE FROM systranschemas
GO
DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM sysarticles
GO
DELETE FROM sysschemaarticles
GO
DELETE FROM syspublications
GO
DELETE FROM sysmergeschemachange
GO
DELETE FROM sysmergesubsetfilters
GO
DELETE FROM MSdynamicsnapshotjobs
GO
DELETE FROM MSdynamicsnapshotviews
GO
DELETE FROM MSmerge_altsyncpartners
GO
DELETE FROM MSmerge_contents
GO
DELETE FROM MSmerge_delete_conflicts
GO
DELETE FROM MSmerge_errorlineage
GO
DELETE FROM MSmerge_genhistory
GO
DELETE FROM MSmerge_replinfo
GO
DELETE FROM MSmerge_tombstone
GO
DELETE FROM MSpub_identity_range
GO
DELETE FROM MSrepl_identity_range
GO
DELETE FROM MSreplication_subscriptions
GO
DELETE FROM MSsubscription_agents
GO
|||Thanks folks. The problem was the triggers. I manually deleted them (I didn't see the replies to this message earlier) and now it works. However, I will be making a copy of the script that Hilary provided.
great! - dw