Publisher keeps trying to connect to subscriber

hi,

I manually changed a value in a table that makes part of the Replication and after that the sync failed on the procedure that updates the table on the subscriber.

So I manually re-start the sync. But for some reason it keeps trying to reconnect to the subscriber.
If I try to delete the subscriber to push it again, it locks.

I went to the Agent History, it showed the error "The process could not connect to Subscriber"
ODBC SQL SERVER DRIVER (number 08001)

I checked the SQL on the subscriber and it's running.
Does anybody has an idea of what I could do ?

Thanks !Before somebody try to answer, I got the replication running again.
I STOPPED the Sql Server at the subscriber and Re-started it.

things looks fine now.

Renata

Publication changes not appearing at subscriber?

I have setup a merge replication publication (SQL server 2005 wth SQL Mobile) with dynamic filters based on host name and when i sync to the pda all data appears fine. When I make changes on the PDA and synchronise, the changes are pushed back to the server fine.

However when I change data on the server and synchronise at the PDA, no changes ever appear!

Surely I must have missed something obvious or done something really stupid.

If i check the replication monitor it just says no changes so any suggestions would be great.

Thanks,

Andy

Ok, Ive managed to prove that if there is no @.subset_filterclause specified, it works fine. As soon as the filter is turned on i cannot add any new rows.

The filter is quite complex and as far as I can see, it will always fail because of the order in which records are added to the database. Can anybody clarify?

I have an organisation table and account management tables. The filter says download organisations that you are the account manager of. THerefore the organisation record is saved before the account management due to dependencies and at the point of creating the org, the trigger will fail because the account management record will not exist.

Surely this must be a standard problem so what am i doing wrong?

|||

After using one of our gold partner support calls, Microsoft have diagnosed the problem as my use of precomputed publications. This option in 2005 means that each insert fires a trigger which in turn evaluates the filter conditions.

In my case when i added a new organisation, it checked if the organisation was inside the users account management roles. This fails because the account management row has not yet been inserted (and cannot be added until after the org due to FK's.

The simple solution was to turn off precomputed partitions which sorted the problem.

Apparently it should be possible to use precomputed partitions if I define a row filter based on the last row to be inserted with all other rows linked through join filters. However, the suggested filters didnt work so I will try a bit more when i get an hour spare (in 2027 )

Publication changes not appearing at subscriber?

I have setup a merge replication publication (SQL server 2005 wth SQL Mobile) with dynamic filters based on host name and when i sync to the pda all data appears fine. When I make changes on the PDA and synchronise, the changes are pushed back to the server fine.

However when I change data on the server and synchronise at the PDA, no changes ever appear!

Surely I must have missed something obvious or done something really stupid.

If i check the replication monitor it just says no changes so any suggestions would be great.

Thanks,

Andy

Ok, Ive managed to prove that if there is no @.subset_filterclause specified, it works fine. As soon as the filter is turned on i cannot add any new rows.

The filter is quite complex and as far as I can see, it will always fail because of the order in which records are added to the database. Can anybody clarify?

I have an organisation table and account management tables. The filter says download organisations that you are the account manager of. THerefore the organisation record is saved before the account management due to dependencies and at the point of creating the org, the trigger will fail because the account management record will not exist.

Surely this must be a standard problem so what am i doing wrong?

|||

After using one of our gold partner support calls, Microsoft have diagnosed the problem as my use of precomputed publications. This option in 2005 means that each insert fires a trigger which in turn evaluates the filter conditions.

In my case when i added a new organisation, it checked if the organisation was inside the users account management roles. This fails because the account management row has not yet been inserted (and cannot be added until after the org due to FK's.

The simple solution was to turn off precomputed partitions which sorted the problem.

Apparently it should be possible to use precomputed partitions if I define a row filter based on the last row to be inserted with all other rows linked through join filters. However, the suggested filters didnt work so I will try a bit more when i get an hour spare (in 2027 )

Publication

I have a publication on my SQL Server.

The first time my application syncs with the server it works fine.

If I try to sync again I get "Permission Denied Number 80040E09 NativeErr: 0"

Other publications work fine, it just seems to be this one.

Any ideas?

1. What kind of publication is this one, merge publication or transactional publication?

2. How many articles are in this publication and of what kind, table, view, SP or UDF?

3. What version of SQL server are you using on distribution/publisher/subscriber?

Thanks.

|||

1 - Merge to SQL CE

2 - 14 tables

3 - SQL Server Enterprise V 8.00.679 (SP2)

|||

by "the first time sync to server ", do you mean the initial sync ?

what are the login you used to connect to SQL server ? does this login has the read permission on the database ?

thanks

Yunwen

|||

Yes the initial sync

Unfortunately the previous developer used sa as the login (DOH)

Yes sa has privileges.

We are building a new server to get rid of that.

|||

Most likely this could a security configuration issue when connecting to the backend sql server. it is still not very clear why the subsequent sync failed with permission error though since SA should have sufficient permission during sync.

Does this still repro with the new application ? we should trace it down if it repro-es.

Thanks

Yunwen

Publication

I have a publication on my SQL Server.

The first time my application syncs with the server it works fine.

If I try to sync again I get "Permission Denied Number 80040E09 NativeErr: 0"

Other publications work fine, it just seems to be this one.

Any ideas?

1. What kind of publication is this one, merge publication or transactional publication?

2. How many articles are in this publication and of what kind, table, view, SP or UDF?

3. What version of SQL server are you using on distribution/publisher/subscriber?

Thanks.

|||

1 - Merge to SQL CE

2 - 14 tables

3 - SQL Server Enterprise V 8.00.679 (SP2)

|||

by "the first time sync to server ", do you mean the initial sync ?

what are the login you used to connect to SQL server ? does this login has the read permission on the database ?

thanks

Yunwen

|||

Yes the initial sync

Unfortunately the previous developer used sa as the login (DOH)

Yes sa has privileges.

We are building a new server to get rid of that.

|||

Most likely this could a security configuration issue when connecting to the backend sql server. it is still not very clear why the subsequent sync failed with permission error though since SA should have sufficient permission during sync.

Does this still repro with the new application ? we should trace it down if it repro-es.

Thanks

Yunwen