We have 2 SQL2000 servers updating in real-time using merge replication. I need to shut one server down over this weekend, but the other one needs to remain up. What will happen? Will we have to replicate all the data all over again or will it pick up where it left off?
If I'd have to re-replicate everything, can I prevent this by stopping a service or setting a replication schedule instead or something?
Thanks for your help,
Erica <-- not a SQL AdminIt will pick up from where it left of when you shut it down. You may receive some errors in your system log.
Showing posts with label sql2000. Show all posts
Showing posts with label sql2000. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Merge replication trouble
Hello
I have made a merge replication between two server W2k with sql2000 sp3. Those servers are connected via internet. In distributor (also publisher) I have setting up aliases for both servers. Have generated snapshot but when agent run I have this error: -2
147201019 and 14010 : "the subscription for pubblication [bla bla] is invalid".
I have searching in the net some solutions but I have noticed that in all cases it is depended by alias setting. But I have defined its ?
Some ideas ?
Have solved the puzzle.
In subscription I have noticed with sp_helpserver that the subscription server hav id = 1 so have do this:
sp_dropserver 'DIST_SERVER'
sp_dropserver 'SUB_SERVER'
sp_addserver 'SUB_SERVER', @.local = 'LOCAL'
where DIST_SERVER is my distribution, publisher server and SUB_SERVER is my subscription server.
Bests
|||Rafal,
OK, and the last 2 commands will require a restart of the services.
I'm not clear what the first line is for though (sp_dropserver 'DIST_SERVER').
Regards,
Paul Ibison
I have made a merge replication between two server W2k with sql2000 sp3. Those servers are connected via internet. In distributor (also publisher) I have setting up aliases for both servers. Have generated snapshot but when agent run I have this error: -2
147201019 and 14010 : "the subscription for pubblication [bla bla] is invalid".
I have searching in the net some solutions but I have noticed that in all cases it is depended by alias setting. But I have defined its ?
Some ideas ?
Have solved the puzzle.
In subscription I have noticed with sp_helpserver that the subscription server hav id = 1 so have do this:
sp_dropserver 'DIST_SERVER'
sp_dropserver 'SUB_SERVER'
sp_addserver 'SUB_SERVER', @.local = 'LOCAL'
where DIST_SERVER is my distribution, publisher server and SUB_SERVER is my subscription server.
Bests
|||Rafal,
OK, and the last 2 commands will require a restart of the services.
I'm not clear what the first line is for though (sp_dropserver 'DIST_SERVER').
Regards,
Paul Ibison
Friday, March 23, 2012
Merge Replication SQL2000 Error 2812
Hi !
When trying to start a Merge Replication agent I get the following Error message:
The process could not enumerate the changes at the subscriber. 2812
The snapshot agent works fine as far as I can see.
The replication is set up between a Win2000 / SQL7 / SP 4 and a Win2003 / SQL2000 / SP 3a machine. Sqlserveragent on both machines is run as a system account.
Any tip is welcome!
Thanks
VincentJSProbably it was deadlock or merge agent cannot lock what does it need during enumerating. It is normal thing because of database activity. Try to repeat it and if problem still presents - check all processes by profiler and find where agent stops. Sometimes agent continue to work even if everything is red in EM.
Also you have to set up da omain account for sql server and an sql agent - I wonder how it is working at all (replication) under a local account.|||Hi snail!
I understand what you told me and believe me, it (replication) works fine as a local system account or with a local administrator account.
It wokrs like a charm between the distributor/publisher (WIN2000/SQL7) and another server (subscriber) outside the DMZ (NT4/SQL7)
With the new subscriber (WIN2003/SQL2000) I keep getting the following error when starting the merge agent:
Process could not enumerate changes at subscriber. Error 2812
Some additional information is that the merge agent stops with:
call sp_MSEnumerateChanges(?,?,?,?,?)
I very recently installed SP 3a on the new subscriber (after reading on microsoft/technet) but that didn't help.
Any more suggestions?
Thanks a lot anyway for your help!
Greetings,
VincentJS|||Are you trying to replicate a publisher to a subscriber with different schema on both side?
Originally posted by VincentJS
Hi snail!
I understand what you told me and believe me, it (replication) works fine as a local system account or with a local administrator account.
It wokrs like a charm between the distributor/publisher (WIN2000/SQL7) and another server (subscriber) outside the DMZ (NT4/SQL7)
With the new subscriber (WIN2003/SQL2000) I keep getting the following error when starting the merge agent:
Process could not enumerate changes at subscriber. Error 2812
Some additional information is that the merge agent stops with:
call sp_MSEnumerateChanges(?,?,?,?,?)
I very recently installed SP 3a on the new subscriber (after reading on microsoft/technet) but that didn't help.
Any more suggestions?
Thanks a lot anyway for your help!
Greetings,
VincentJS|||Hello!
That's a good question. I've run and rerun so many tests (but always cleaning up afterwards with sp_mergesubscription_cleanup) that honestly at this stage i couldn't give you a clear answer on that point.
I've reinitialized the subscription a lot of times though and that didn't do it either.
I DID try to publish another table with merging (to another database) and that produced the same error type, despite the fact that I used a brandnew subscription with a totally different file.
Did this answer your question?
Thanks for your help!
VincentJs|||You mentioned about the brand new file, does that mean you have a different schema on the subscriber and trying to get replicated with the publisher with another schema and setup the merge without reinitialize the whole db.
I recalled I have the same problem before with that error message, what I did is to try to recreate my subscriber database and then leave it empty as it is until I create the snapshot to pull over to the subscriber by initiializing snapshot. Then it work properly.You can give a try but, please backup your subscriber database in case you need to restore back the database.|||Hi!
I wrote file but i should have written Table (what's in a name right?). Anyway, no i won't try what you suggested until only the very last moment when all else fails. At this moment its too risky and although i do have backups of all databases, i want to avoid "contaminating" or reinstallations as much as possible.
In the meantime i ran the merge agent in verbose-mode and i got the following messages (simplified):
REPLAGENT STATUS: 3
DistribServer.Pubdb: call {sp_MSenumcolums (?,?)}
SubscrServer.Subdb: call {sp_MSenumchanges (?,?,?,?,?)}
Percent complete: 0
The process could not enumerate changes at the subscriber
REPLAGENT STATUS: 6
Percent complete: 0
Category: COMMAND
Source: failed command
Number:
Message: call sp_MSenumChanges(?,?,?,?,?)
There's more of course but I think this covers the essence. Basically ALL the commands preceding the error ran normally... only at the (presumably) last step mentionned hereabove the merge agent fails.
Have you got any clue what's going wrong?
Thanks! I'll see you tomorrow i hope... It's quite late now here and i'm going home... I've got to eat and sleep sometimes :)
VincentJSsql
When trying to start a Merge Replication agent I get the following Error message:
The process could not enumerate the changes at the subscriber. 2812
The snapshot agent works fine as far as I can see.
The replication is set up between a Win2000 / SQL7 / SP 4 and a Win2003 / SQL2000 / SP 3a machine. Sqlserveragent on both machines is run as a system account.
Any tip is welcome!
Thanks
VincentJSProbably it was deadlock or merge agent cannot lock what does it need during enumerating. It is normal thing because of database activity. Try to repeat it and if problem still presents - check all processes by profiler and find where agent stops. Sometimes agent continue to work even if everything is red in EM.
Also you have to set up da omain account for sql server and an sql agent - I wonder how it is working at all (replication) under a local account.|||Hi snail!
I understand what you told me and believe me, it (replication) works fine as a local system account or with a local administrator account.
It wokrs like a charm between the distributor/publisher (WIN2000/SQL7) and another server (subscriber) outside the DMZ (NT4/SQL7)
With the new subscriber (WIN2003/SQL2000) I keep getting the following error when starting the merge agent:
Process could not enumerate changes at subscriber. Error 2812
Some additional information is that the merge agent stops with:
call sp_MSEnumerateChanges(?,?,?,?,?)
I very recently installed SP 3a on the new subscriber (after reading on microsoft/technet) but that didn't help.
Any more suggestions?
Thanks a lot anyway for your help!
Greetings,
VincentJS|||Are you trying to replicate a publisher to a subscriber with different schema on both side?
Originally posted by VincentJS
Hi snail!
I understand what you told me and believe me, it (replication) works fine as a local system account or with a local administrator account.
It wokrs like a charm between the distributor/publisher (WIN2000/SQL7) and another server (subscriber) outside the DMZ (NT4/SQL7)
With the new subscriber (WIN2003/SQL2000) I keep getting the following error when starting the merge agent:
Process could not enumerate changes at subscriber. Error 2812
Some additional information is that the merge agent stops with:
call sp_MSEnumerateChanges(?,?,?,?,?)
I very recently installed SP 3a on the new subscriber (after reading on microsoft/technet) but that didn't help.
Any more suggestions?
Thanks a lot anyway for your help!
Greetings,
VincentJS|||Hello!
That's a good question. I've run and rerun so many tests (but always cleaning up afterwards with sp_mergesubscription_cleanup) that honestly at this stage i couldn't give you a clear answer on that point.
I've reinitialized the subscription a lot of times though and that didn't do it either.
I DID try to publish another table with merging (to another database) and that produced the same error type, despite the fact that I used a brandnew subscription with a totally different file.
Did this answer your question?
Thanks for your help!
VincentJs|||You mentioned about the brand new file, does that mean you have a different schema on the subscriber and trying to get replicated with the publisher with another schema and setup the merge without reinitialize the whole db.
I recalled I have the same problem before with that error message, what I did is to try to recreate my subscriber database and then leave it empty as it is until I create the snapshot to pull over to the subscriber by initiializing snapshot. Then it work properly.You can give a try but, please backup your subscriber database in case you need to restore back the database.|||Hi!
I wrote file but i should have written Table (what's in a name right?). Anyway, no i won't try what you suggested until only the very last moment when all else fails. At this moment its too risky and although i do have backups of all databases, i want to avoid "contaminating" or reinstallations as much as possible.
In the meantime i ran the merge agent in verbose-mode and i got the following messages (simplified):
REPLAGENT STATUS: 3
DistribServer.Pubdb: call {sp_MSenumcolums (?,?)}
SubscrServer.Subdb: call {sp_MSenumchanges (?,?,?,?,?)}
Percent complete: 0
The process could not enumerate changes at the subscriber
REPLAGENT STATUS: 6
Percent complete: 0
Category: COMMAND
Source: failed command
Number:
Message: call sp_MSenumChanges(?,?,?,?,?)
There's more of course but I think this covers the essence. Basically ALL the commands preceding the error ran normally... only at the (presumably) last step mentionned hereabove the merge agent fails.
Have you got any clue what's going wrong?
Thanks! I'll see you tomorrow i hope... It's quite late now here and i'm going home... I've got to eat and sleep sometimes :)
VincentJSsql
merge replication Sql server 2000 with SQLCE 2.0
I do a merge replication between Sql server 2000 and SQLCE 2.0
On my SQL2000 I have 4 tables i want to merge (specific columns only ) in 1 table for Merge with my SQLCe ( the table will be use for read only)
Question 1:
What is the best pratice for keep the information update?
Run store procedure before the synch for re-populate the table?:confused: or Make Trigger INSERT, UPDATE, DELETE in the all 4 table?:confused: or a mixte?:confused:
Question 2:
Does someone know about some web site talk about this type of trick?
ThanksMay refer to this link http://msdn.microsoft.com/library/en-us/sqlce/htm/_lce_repl_intro_replication_architecture.asp which lists from SQL SErver CE books online.
http://csaw.biz/tips/sql-server-ce.php about KBAs with refers to CE.
http://www.winnetmag.com/SQLServer/Article/ArticleID/9004/9004.html
On my SQL2000 I have 4 tables i want to merge (specific columns only ) in 1 table for Merge with my SQLCe ( the table will be use for read only)
Question 1:
What is the best pratice for keep the information update?
Run store procedure before the synch for re-populate the table?:confused: or Make Trigger INSERT, UPDATE, DELETE in the all 4 table?:confused: or a mixte?:confused:
Question 2:
Does someone know about some web site talk about this type of trick?
ThanksMay refer to this link http://msdn.microsoft.com/library/en-us/sqlce/htm/_lce_repl_intro_replication_architecture.asp which lists from SQL SErver CE books online.
http://csaw.biz/tips/sql-server-ce.php about KBAs with refers to CE.
http://www.winnetmag.com/SQLServer/Article/ArticleID/9004/9004.html
Monday, March 19, 2012
Merge replication on MSDE over internet
Hi,
We are having a dedicated machine (with a fix IP) running SQl2000 and it is
supposed to be the master database. And we are having 4 clients XP machine
running MSDE (without fix IP), and we would like to have a merge replication
to sync. data from / to the client / server. Coz data will be updated on
server or clients side. I have simulated this environment on a LAN
environment and it works but I 'm not sure whether if those clients machine
are connected to the server through internet via ADSL connection(without a
fix IP).
please help ... !!! thanks.
-Wing
you must use the replication ActiveX merge control. Set the
DistributorAddress and the PublisherAddress to be the IP address of your
publisher and the DIstributorNetwork and PublisherNetwork to be TCPIP
"Wing Chan" <wing_650473hk@.yahoo.com.hk> wrote in message
news:uGjl5RxyEHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are having a dedicated machine (with a fix IP) running SQl2000 and it
> is
> supposed to be the master database. And we are having 4 clients XP
> machine
> running MSDE (without fix IP), and we would like to have a merge
> replication
> to sync. data from / to the client / server. Coz data will be updated on
> server or clients side. I have simulated this environment on a LAN
> environment and it works but I 'm not sure whether if those clients
> machine
> are connected to the server through internet via ADSL connection(without a
> fix IP).
> please help ... !!! thanks.
> -Wing
>
|||thanks... is there any sample source code on that ? besides using ActiveX
merge control, is there any way not using control ?
also does the ActiveX merge control support VB6, thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> bl
news:%23f8Kxk1yEHA.2540@.TK2MSFTNGP09.phx.gbl g...[vbcol=seagreen]
> you must use the replication ActiveX merge control. Set the
> DistributorAddress and the PublisherAddress to be the IP address of your
> publisher and the DIstributorNetwork and PublisherNetwork to be TCPIP
>
> "Wing Chan" <wing_650473hk@.yahoo.com.hk> wrote in message
> news:uGjl5RxyEHA.824@.TK2MSFTNGP11.phx.gbl...
on[vbcol=seagreen]
a
>
|||We currently have 4 locations replicating such. What I do is setup SQL Server
to dial VPN setup at our main office (Publisher). Each subscriber dials VPN,
performs replication, and then disconnects the VPN. This works great...they
sync every hour.
"Wing Chan" wrote:
> Hi,
> We are having a dedicated machine (with a fix IP) running SQl2000 and it is
> supposed to be the master database. And we are having 4 clients XP machine
> running MSDE (without fix IP), and we would like to have a merge replication
> to sync. data from / to the client / server. Coz data will be updated on
> server or clients side. I have simulated this environment on a LAN
> environment and it works but I 'm not sure whether if those clients machine
> are connected to the server through internet via ADSL connection(without a
> fix IP).
> please help ... !!! thanks.
> -Wing
>
>
|||Hi
> Each subscriber dials VPN,
> performs replication, and then disconnects the VPN.
Where do you handle the VPN dial/disconnect? Do you add a step to the Agent
job or something else?
I'll appreciate any details.
Regards,
|||Yes, just add new steps to the Merge Agent job. First step would be of Type
"Operating System Command (CmdExec)" and would read something like
rasdial vpn_connection_name user password domain:/your_domain_name
Then add another step after the Replication agent to disconnect. Same as
above but the command text would be:
rasdial vpn_connection_name /disconnect
Important Note: It appears that the Merge Agent job step attempts to run
immediately after the first Connect VPN step is run. If you're relying on
NETBIOS name resolution, you WILL need to add a retry on the replication
step. This is simply because it takes a little bit of time for your VPN IP
settings to be configured upon first connection. The first merge attempt
almost always fails for me because it can't locate the Publisher name yet. If
you're connecting via straight IP addresses, then this will not be a problem.
Besides that, this works like a charm for our multi-city office replication
on an hourly basis.
"Carlos Gutierrez" wrote:
> Hi
>
> Where do you handle the VPN dial/disconnect? Do you add a step to the Agent
> job or something else?
> I'll appreciate any details.
> Regards,
>
>
We are having a dedicated machine (with a fix IP) running SQl2000 and it is
supposed to be the master database. And we are having 4 clients XP machine
running MSDE (without fix IP), and we would like to have a merge replication
to sync. data from / to the client / server. Coz data will be updated on
server or clients side. I have simulated this environment on a LAN
environment and it works but I 'm not sure whether if those clients machine
are connected to the server through internet via ADSL connection(without a
fix IP).
please help ... !!! thanks.
-Wing
you must use the replication ActiveX merge control. Set the
DistributorAddress and the PublisherAddress to be the IP address of your
publisher and the DIstributorNetwork and PublisherNetwork to be TCPIP
"Wing Chan" <wing_650473hk@.yahoo.com.hk> wrote in message
news:uGjl5RxyEHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are having a dedicated machine (with a fix IP) running SQl2000 and it
> is
> supposed to be the master database. And we are having 4 clients XP
> machine
> running MSDE (without fix IP), and we would like to have a merge
> replication
> to sync. data from / to the client / server. Coz data will be updated on
> server or clients side. I have simulated this environment on a LAN
> environment and it works but I 'm not sure whether if those clients
> machine
> are connected to the server through internet via ADSL connection(without a
> fix IP).
> please help ... !!! thanks.
> -Wing
>
|||thanks... is there any sample source code on that ? besides using ActiveX
merge control, is there any way not using control ?
also does the ActiveX merge control support VB6, thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> bl
news:%23f8Kxk1yEHA.2540@.TK2MSFTNGP09.phx.gbl g...[vbcol=seagreen]
> you must use the replication ActiveX merge control. Set the
> DistributorAddress and the PublisherAddress to be the IP address of your
> publisher and the DIstributorNetwork and PublisherNetwork to be TCPIP
>
> "Wing Chan" <wing_650473hk@.yahoo.com.hk> wrote in message
> news:uGjl5RxyEHA.824@.TK2MSFTNGP11.phx.gbl...
on[vbcol=seagreen]
a
>
|||We currently have 4 locations replicating such. What I do is setup SQL Server
to dial VPN setup at our main office (Publisher). Each subscriber dials VPN,
performs replication, and then disconnects the VPN. This works great...they
sync every hour.
"Wing Chan" wrote:
> Hi,
> We are having a dedicated machine (with a fix IP) running SQl2000 and it is
> supposed to be the master database. And we are having 4 clients XP machine
> running MSDE (without fix IP), and we would like to have a merge replication
> to sync. data from / to the client / server. Coz data will be updated on
> server or clients side. I have simulated this environment on a LAN
> environment and it works but I 'm not sure whether if those clients machine
> are connected to the server through internet via ADSL connection(without a
> fix IP).
> please help ... !!! thanks.
> -Wing
>
>
|||Hi
> Each subscriber dials VPN,
> performs replication, and then disconnects the VPN.
Where do you handle the VPN dial/disconnect? Do you add a step to the Agent
job or something else?
I'll appreciate any details.
Regards,
|||Yes, just add new steps to the Merge Agent job. First step would be of Type
"Operating System Command (CmdExec)" and would read something like
rasdial vpn_connection_name user password domain:/your_domain_name
Then add another step after the Replication agent to disconnect. Same as
above but the command text would be:
rasdial vpn_connection_name /disconnect
Important Note: It appears that the Merge Agent job step attempts to run
immediately after the first Connect VPN step is run. If you're relying on
NETBIOS name resolution, you WILL need to add a retry on the replication
step. This is simply because it takes a little bit of time for your VPN IP
settings to be configured upon first connection. The first merge attempt
almost always fails for me because it can't locate the Publisher name yet. If
you're connecting via straight IP addresses, then this will not be a problem.
Besides that, this works like a charm for our multi-city office replication
on an hourly basis.
"Carlos Gutierrez" wrote:
> Hi
>
> Where do you handle the VPN dial/disconnect? Do you add a step to the Agent
> job or something else?
> I'll appreciate any details.
> Regards,
>
>
Merge replication on MSDE over internet
Hi,
We are having a dedicated machine (with a fix IP) running SQl2000 and it is
supposed to be the master database. And we are having 4 clients XP machine
running MSDE (without fix IP), and we would like to have a merge replication
to sync. data from / to the client / server. Coz data will be updated on
server or clients side. I have simulated this environment on a LAN
environment and it works but I 'm not sure whether if those clients machine
are connected to the server through internet via ADSL connection(without a
fix IP).
please help ... !!! thanks.
-Wing
you must use the replication ActiveX merge control. Set the
DistributorAddress and the PublisherAddress to be the IP address of your
publisher and the DIstributorNetwork and PublisherNetwork to be TCPIP
"Wing Chan" <wing_650473hk@.yahoo.com.hk> wrote in message
news:uGjl5RxyEHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are having a dedicated machine (with a fix IP) running SQl2000 and it
> is
> supposed to be the master database. And we are having 4 clients XP
> machine
> running MSDE (without fix IP), and we would like to have a merge
> replication
> to sync. data from / to the client / server. Coz data will be updated on
> server or clients side. I have simulated this environment on a LAN
> environment and it works but I 'm not sure whether if those clients
> machine
> are connected to the server through internet via ADSL connection(without a
> fix IP).
> please help ... !!! thanks.
> -Wing
>
|||thanks... is there any sample source code on that ? besides using ActiveX
merge control, is there any way not using control ?
also does the ActiveX merge control support VB6, thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> bl
news:%23f8Kxk1yEHA.2540@.TK2MSFTNGP09.phx.gbl g...[vbcol=seagreen]
> you must use the replication ActiveX merge control. Set the
> DistributorAddress and the PublisherAddress to be the IP address of your
> publisher and the DIstributorNetwork and PublisherNetwork to be TCPIP
>
> "Wing Chan" <wing_650473hk@.yahoo.com.hk> wrote in message
> news:uGjl5RxyEHA.824@.TK2MSFTNGP11.phx.gbl...
on[vbcol=seagreen]
a
>
|||We currently have 4 locations replicating such. What I do is setup SQL Server
to dial VPN setup at our main office (Publisher). Each subscriber dials VPN,
performs replication, and then disconnects the VPN. This works great...they
sync every hour.
"Wing Chan" wrote:
> Hi,
> We are having a dedicated machine (with a fix IP) running SQl2000 and it is
> supposed to be the master database. And we are having 4 clients XP machine
> running MSDE (without fix IP), and we would like to have a merge replication
> to sync. data from / to the client / server. Coz data will be updated on
> server or clients side. I have simulated this environment on a LAN
> environment and it works but I 'm not sure whether if those clients machine
> are connected to the server through internet via ADSL connection(without a
> fix IP).
> please help ... !!! thanks.
> -Wing
>
>
|||Hi
> Each subscriber dials VPN,
> performs replication, and then disconnects the VPN.
Where do you handle the VPN dial/disconnect? Do you add a step to the Agent
job or something else?
I'll appreciate any details.
Regards,
|||Yes, just add new steps to the Merge Agent job. First step would be of Type
"Operating System Command (CmdExec)" and would read something like
rasdial vpn_connection_name user password domain:/your_domain_name
Then add another step after the Replication agent to disconnect. Same as
above but the command text would be:
rasdial vpn_connection_name /disconnect
Important Note: It appears that the Merge Agent job step attempts to run
immediately after the first Connect VPN step is run. If you're relying on
NETBIOS name resolution, you WILL need to add a retry on the replication
step. This is simply because it takes a little bit of time for your VPN IP
settings to be configured upon first connection. The first merge attempt
almost always fails for me because it can't locate the Publisher name yet. If
you're connecting via straight IP addresses, then this will not be a problem.
Besides that, this works like a charm for our multi-city office replication
on an hourly basis.
"Carlos Gutierrez" wrote:
> Hi
>
> Where do you handle the VPN dial/disconnect? Do you add a step to the Agent
> job or something else?
> I'll appreciate any details.
> Regards,
>
>
We are having a dedicated machine (with a fix IP) running SQl2000 and it is
supposed to be the master database. And we are having 4 clients XP machine
running MSDE (without fix IP), and we would like to have a merge replication
to sync. data from / to the client / server. Coz data will be updated on
server or clients side. I have simulated this environment on a LAN
environment and it works but I 'm not sure whether if those clients machine
are connected to the server through internet via ADSL connection(without a
fix IP).
please help ... !!! thanks.
-Wing
you must use the replication ActiveX merge control. Set the
DistributorAddress and the PublisherAddress to be the IP address of your
publisher and the DIstributorNetwork and PublisherNetwork to be TCPIP
"Wing Chan" <wing_650473hk@.yahoo.com.hk> wrote in message
news:uGjl5RxyEHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We are having a dedicated machine (with a fix IP) running SQl2000 and it
> is
> supposed to be the master database. And we are having 4 clients XP
> machine
> running MSDE (without fix IP), and we would like to have a merge
> replication
> to sync. data from / to the client / server. Coz data will be updated on
> server or clients side. I have simulated this environment on a LAN
> environment and it works but I 'm not sure whether if those clients
> machine
> are connected to the server through internet via ADSL connection(without a
> fix IP).
> please help ... !!! thanks.
> -Wing
>
|||thanks... is there any sample source code on that ? besides using ActiveX
merge control, is there any way not using control ?
also does the ActiveX merge control support VB6, thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> bl
news:%23f8Kxk1yEHA.2540@.TK2MSFTNGP09.phx.gbl g...[vbcol=seagreen]
> you must use the replication ActiveX merge control. Set the
> DistributorAddress and the PublisherAddress to be the IP address of your
> publisher and the DIstributorNetwork and PublisherNetwork to be TCPIP
>
> "Wing Chan" <wing_650473hk@.yahoo.com.hk> wrote in message
> news:uGjl5RxyEHA.824@.TK2MSFTNGP11.phx.gbl...
on[vbcol=seagreen]
a
>
|||We currently have 4 locations replicating such. What I do is setup SQL Server
to dial VPN setup at our main office (Publisher). Each subscriber dials VPN,
performs replication, and then disconnects the VPN. This works great...they
sync every hour.
"Wing Chan" wrote:
> Hi,
> We are having a dedicated machine (with a fix IP) running SQl2000 and it is
> supposed to be the master database. And we are having 4 clients XP machine
> running MSDE (without fix IP), and we would like to have a merge replication
> to sync. data from / to the client / server. Coz data will be updated on
> server or clients side. I have simulated this environment on a LAN
> environment and it works but I 'm not sure whether if those clients machine
> are connected to the server through internet via ADSL connection(without a
> fix IP).
> please help ... !!! thanks.
> -Wing
>
>
|||Hi
> Each subscriber dials VPN,
> performs replication, and then disconnects the VPN.
Where do you handle the VPN dial/disconnect? Do you add a step to the Agent
job or something else?
I'll appreciate any details.
Regards,
|||Yes, just add new steps to the Merge Agent job. First step would be of Type
"Operating System Command (CmdExec)" and would read something like
rasdial vpn_connection_name user password domain:/your_domain_name
Then add another step after the Replication agent to disconnect. Same as
above but the command text would be:
rasdial vpn_connection_name /disconnect
Important Note: It appears that the Merge Agent job step attempts to run
immediately after the first Connect VPN step is run. If you're relying on
NETBIOS name resolution, you WILL need to add a retry on the replication
step. This is simply because it takes a little bit of time for your VPN IP
settings to be configured upon first connection. The first merge attempt
almost always fails for me because it can't locate the Publisher name yet. If
you're connecting via straight IP addresses, then this will not be a problem.
Besides that, this works like a charm for our multi-city office replication
on an hourly basis.
"Carlos Gutierrez" wrote:
> Hi
>
> Where do you handle the VPN dial/disconnect? Do you add a step to the Agent
> job or something else?
> I'll appreciate any details.
> Regards,
>
>
Friday, March 9, 2012
Merge replication failure
Hello,
I have an SQL2000 server, which acts as the distributor and as the
publisher. The clients are Pocket PC's, using SQL Server CE. I have the
latest SP for both. The replication is a merge replication, with a lot of
join filters, more than 100 articles (only tables, no views or procedures
included). The average size of the database on the clients is about ~7MB.
Sometimes, following a massive delete on several tables (delete from table,
than insert into table select from sthing), I have got the following error
message during replication:
1. "a call to the sql server reconciler has failed"
2. "failed to enumerate changes"
3. "select permission denied on column <pk> of object MS_<long-long guid>"
The error occurs in the stored procedure sp_MSsetupbelongs.
Actually, the message is right, the user has no rights to the referred
table. But. This is a generated table, created by the replication itself (I
think this is the table used for deletion). After I've added the rights, the
error disapperared. Reinitialize also helps.
Do I have to do this all for the tables in the publication? I could not do
this, because of the generated table names.
Or this is a bug?
Thanks for replies,
Tamas Beri
Funny, but I can't find anything better solution than this one:
select
'grant select on '+sysobjects.name+' to sales;'
from
sysobjects
where
name like 'MS_bi%_v_%'
order by
sysobjects.name;
After you ran this generated script, the whole problem described down there
disappears.
Do You have any information about the Service Pack 4? I have a lot of
problems with sql server ce and merge replication.
Regards,
Tamas Beri
"Beri Tamas" <gfoyle@.freemail.hu> az albbiakat rta a kvetkezo zenetben
news:uYP%23YL89EHA.2984@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have an SQL2000 server, which acts as the distributor and as the
> publisher. The clients are Pocket PC's, using SQL Server CE. I have the
> latest SP for both. The replication is a merge replication, with a lot of
> join filters, more than 100 articles (only tables, no views or procedures
> included). The average size of the database on the clients is about ~7MB.
> Sometimes, following a massive delete on several tables (delete from
table,
> than insert into table select from sthing), I have got the following error
> message during replication:
> 1. "a call to the sql server reconciler has failed"
> 2. "failed to enumerate changes"
> 3. "select permission denied on column <pk> of object MS_<long-long guid>"
> The error occurs in the stored procedure sp_MSsetupbelongs.
> Actually, the message is right, the user has no rights to the referred
> table. But. This is a generated table, created by the replication itself
(I
> think this is the table used for deletion). After I've added the rights,
the
> error disapperared. Reinitialize also helps.
> Do I have to do this all for the tables in the publication? I could not do
> this, because of the generated table names.
> Or this is a bug?
> Thanks for replies,
> Tamas Beri
|||Hi Beri,
Replication shouldn't require you to issue the explicit select on this
particular object. Can you please provide some more info so that we can try
to create the repro in our test environment?
1. What kind of permission have you provided to publisher and distributor
login?
2. What's the type of object 'MS_bi%_v_%' - view, SP, etc?
3. Can you please provide the content of following tables - sysmergearticles
& sysmergepublications?
4. In case you are okay - can you post your scripts which you used to setup
your replication?
thanks - Deepak
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Beri Tamas" <gfoyle@.freemail.hu> wrote in message
news:Ocybvt%239EHA.2984@.TK2MSFTNGP09.phx.gbl...
> Funny, but I can't find anything better solution than this one:
> select
> 'grant select on '+sysobjects.name+' to sales;'
> from
> sysobjects
> where
> name like 'MS_bi%_v_%'
> order by
> sysobjects.name;
> After you ran this generated script, the whole problem described down
there[vbcol=seagreen]
> disappears.
> Do You have any information about the Service Pack 4? I have a lot of
> problems with sql server ce and merge replication.
> Regards,
> Tamas Beri
> "Beri Tamas" <gfoyle@.freemail.hu> az albbiakat rta a kvetkezo zenetben
> news:uYP%23YL89EHA.2984@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
procedures[vbcol=seagreen]
~7MB.[vbcol=seagreen]
> table,
error[vbcol=seagreen]
guid>"[vbcol=seagreen]
> (I
> the
do
>
|||Same error here, but I run the script and still get it.
Any more on this one?
I have an SQL2000 server, which acts as the distributor and as the
publisher. The clients are Pocket PC's, using SQL Server CE. I have the
latest SP for both. The replication is a merge replication, with a lot of
join filters, more than 100 articles (only tables, no views or procedures
included). The average size of the database on the clients is about ~7MB.
Sometimes, following a massive delete on several tables (delete from table,
than insert into table select from sthing), I have got the following error
message during replication:
1. "a call to the sql server reconciler has failed"
2. "failed to enumerate changes"
3. "select permission denied on column <pk> of object MS_<long-long guid>"
The error occurs in the stored procedure sp_MSsetupbelongs.
Actually, the message is right, the user has no rights to the referred
table. But. This is a generated table, created by the replication itself (I
think this is the table used for deletion). After I've added the rights, the
error disapperared. Reinitialize also helps.
Do I have to do this all for the tables in the publication? I could not do
this, because of the generated table names.
Or this is a bug?
Thanks for replies,
Tamas Beri
Funny, but I can't find anything better solution than this one:
select
'grant select on '+sysobjects.name+' to sales;'
from
sysobjects
where
name like 'MS_bi%_v_%'
order by
sysobjects.name;
After you ran this generated script, the whole problem described down there
disappears.
Do You have any information about the Service Pack 4? I have a lot of
problems with sql server ce and merge replication.
Regards,
Tamas Beri
"Beri Tamas" <gfoyle@.freemail.hu> az albbiakat rta a kvetkezo zenetben
news:uYP%23YL89EHA.2984@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have an SQL2000 server, which acts as the distributor and as the
> publisher. The clients are Pocket PC's, using SQL Server CE. I have the
> latest SP for both. The replication is a merge replication, with a lot of
> join filters, more than 100 articles (only tables, no views or procedures
> included). The average size of the database on the clients is about ~7MB.
> Sometimes, following a massive delete on several tables (delete from
table,
> than insert into table select from sthing), I have got the following error
> message during replication:
> 1. "a call to the sql server reconciler has failed"
> 2. "failed to enumerate changes"
> 3. "select permission denied on column <pk> of object MS_<long-long guid>"
> The error occurs in the stored procedure sp_MSsetupbelongs.
> Actually, the message is right, the user has no rights to the referred
> table. But. This is a generated table, created by the replication itself
(I
> think this is the table used for deletion). After I've added the rights,
the
> error disapperared. Reinitialize also helps.
> Do I have to do this all for the tables in the publication? I could not do
> this, because of the generated table names.
> Or this is a bug?
> Thanks for replies,
> Tamas Beri
|||Hi Beri,
Replication shouldn't require you to issue the explicit select on this
particular object. Can you please provide some more info so that we can try
to create the repro in our test environment?
1. What kind of permission have you provided to publisher and distributor
login?
2. What's the type of object 'MS_bi%_v_%' - view, SP, etc?
3. Can you please provide the content of following tables - sysmergearticles
& sysmergepublications?
4. In case you are okay - can you post your scripts which you used to setup
your replication?
thanks - Deepak
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Beri Tamas" <gfoyle@.freemail.hu> wrote in message
news:Ocybvt%239EHA.2984@.TK2MSFTNGP09.phx.gbl...
> Funny, but I can't find anything better solution than this one:
> select
> 'grant select on '+sysobjects.name+' to sales;'
> from
> sysobjects
> where
> name like 'MS_bi%_v_%'
> order by
> sysobjects.name;
> After you ran this generated script, the whole problem described down
there[vbcol=seagreen]
> disappears.
> Do You have any information about the Service Pack 4? I have a lot of
> problems with sql server ce and merge replication.
> Regards,
> Tamas Beri
> "Beri Tamas" <gfoyle@.freemail.hu> az albbiakat rta a kvetkezo zenetben
> news:uYP%23YL89EHA.2984@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
procedures[vbcol=seagreen]
~7MB.[vbcol=seagreen]
> table,
error[vbcol=seagreen]
guid>"[vbcol=seagreen]
> (I
> the
do
>
|||Same error here, but I run the script and still get it.
Any more on this one?
Wednesday, March 7, 2012
Merge Replication Enumerations issues
we are using sql server CE on a pocket pc for our salesforce that syncs orders with a SQL2000 sp1 server via merge replication. we had an issue where one person tried to sync and got the error message
The process could not enumerate changes at the 'Subscriber'.
i understand this probably means something is out of sync. however, i am trying to determine what exactly is out of sync. any idea how to do this? i tried to enable vergose logging with the replmerge.exe utility at the command prompt, but becaase it is a mobile device, it could not find the server connected. (we tried connecting to sync the mobile device and running the utility at the same time, but it still did not find it as it said it was not a 'registered' server.)
any help is greatly appreciated.
thanks,
tammyWow! I didn't even know they made a CE version of SQL SErver?
Sorry no help..
The process could not enumerate changes at the 'Subscriber'.
i understand this probably means something is out of sync. however, i am trying to determine what exactly is out of sync. any idea how to do this? i tried to enable vergose logging with the replmerge.exe utility at the command prompt, but becaase it is a mobile device, it could not find the server connected. (we tried connecting to sync the mobile device and running the utility at the same time, but it still did not find it as it said it was not a 'registered' server.)
any help is greatly appreciated.
thanks,
tammyWow! I didn't even know they made a CE version of SQL SErver?
Sorry no help..
Saturday, February 25, 2012
merge replication compatibility issue
All,
I have a SQL2005 server that is running merge replication against SQL2000
boxes. One of my SQL2000 box needed to be replaced due to disk issues. I
scripted out my Subscription from my 2005 box and tried to reapply it after I
rebuilt my new 2000 box. When I run the script I get an error saying:
"Publication 'PubName' cannot be added to database 'DBNAme', because a
publication with a higher compatibility level already exists. All merge
publications in a database must have the same compatibiliy level."
I am in the process of upgrading all my 2000 boxes, but this one, since i
was having issues I wanted to get it back to its original state before
upgrading it.
Right now there is a mix of replications running pointing to a few SQl2000
and Some SQL2005.
Is there a way to get around this before upgrading my new box to 2005?
TIA,
John
Similar, but what I had to do was to change the compatibilty level of all my
subscriptions to 2005 and I was able to add this new one back in.
Thanks for the response.
"Paul Ibison" wrote:
> Is this related to your issue:
> [url]http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.replica tion&tid=4e5d642b-a4c0-4574-8bae-98fcc88e19b8&p=1[/url]
> IE I'm wondering if you have a republishing setup.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||John,
I had very similar situation just the other day, where somebody deleted
publication on SQL2000's level while
there were some pubs already created in 2005's level and I needed to
recreate the publication.
I somehow managed to fix it, but I'm afraid I might have lost some
unsynchronized data in the process - I'm not sure it was the best way to do
it. Could you post step by step description how did you handle it?
thanks,
r
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:C284873D-DD99-45A5-A191-047EE33E004E@.microsoft.com...[vbcol=seagreen]
> Similar, but what I had to do was to change the compatibilty level of all
> my
> subscriptions to 2005 and I was able to add this new one back in.
> Thanks for the response.
> "Paul Ibison" wrote:
|||I scrpited out my replication as a delete and create.
I ran the delete with no issues, but when I tried to run the create that's
when I fell into the compatibilty issue.
i changed all the SQL 200 compatibity levels to 2005 and changed my script
from 80rtm to 90rtm on the create statement of the script SQl generated.
I reran the create and it created with no issues. I started my agent and
synced my data since I only care about what happens here in my main office
anything changed at my branch got overwritten if there were any changes at
both places.
Is that the same way you did yours? maybe you had a better solution, mine
seemed to work for me but I am always looking for better and different
solutions.
"Rafael Lenartowicz" wrote:
> John,
> I had very similar situation just the other day, where somebody deleted
> publication on SQL2000's level while
> there were some pubs already created in 2005's level and I needed to
> recreate the publication.
> I somehow managed to fix it, but I'm afraid I might have lost some
> unsynchronized data in the process - I'm not sure it was the best way to do
> it. Could you post step by step description how did you handle it?
> thanks,
> r
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:C284873D-DD99-45A5-A191-047EE33E004E@.microsoft.com...
>
>
|||how did you manage to change the compatibility level? using
sp_changemergepublication ?
I wasn't able to do it, it was complaining something about the incompatible
snapshot...
besides - in my setup the data from the remote location IS the vital data -
they submiting their
daily revenue reports to the central office reporting server, I can't have
anything overwritten or lost.
thanks
r
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:EE97BCC9-6445-44F4-BF33-D115DDD98B43@.microsoft.com...[vbcol=seagreen]
>I scrpited out my replication as a delete and create.
> I ran the delete with no issues, but when I tried to run the create that's
> when I fell into the compatibilty issue.
> i changed all the SQL 200 compatibity levels to 2005 and changed my script
> from 80rtm to 90rtm on the create statement of the script SQl generated.
> I reran the create and it created with no issues. I started my agent and
> synced my data since I only care about what happens here in my main office
> anything changed at my branch got overwritten if there were any changes at
> both places.
> Is that the same way you did yours? maybe you had a better solution, mine
> seemed to work for me but I am always looking for better and different
> solutions.
> "Rafael Lenartowicz" wrote:
I have a SQL2005 server that is running merge replication against SQL2000
boxes. One of my SQL2000 box needed to be replaced due to disk issues. I
scripted out my Subscription from my 2005 box and tried to reapply it after I
rebuilt my new 2000 box. When I run the script I get an error saying:
"Publication 'PubName' cannot be added to database 'DBNAme', because a
publication with a higher compatibility level already exists. All merge
publications in a database must have the same compatibiliy level."
I am in the process of upgrading all my 2000 boxes, but this one, since i
was having issues I wanted to get it back to its original state before
upgrading it.
Right now there is a mix of replications running pointing to a few SQl2000
and Some SQL2005.
Is there a way to get around this before upgrading my new box to 2005?
TIA,
John
Similar, but what I had to do was to change the compatibilty level of all my
subscriptions to 2005 and I was able to add this new one back in.
Thanks for the response.
"Paul Ibison" wrote:
> Is this related to your issue:
> [url]http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.replica tion&tid=4e5d642b-a4c0-4574-8bae-98fcc88e19b8&p=1[/url]
> IE I'm wondering if you have a republishing setup.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||John,
I had very similar situation just the other day, where somebody deleted
publication on SQL2000's level while
there were some pubs already created in 2005's level and I needed to
recreate the publication.
I somehow managed to fix it, but I'm afraid I might have lost some
unsynchronized data in the process - I'm not sure it was the best way to do
it. Could you post step by step description how did you handle it?
thanks,
r
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:C284873D-DD99-45A5-A191-047EE33E004E@.microsoft.com...[vbcol=seagreen]
> Similar, but what I had to do was to change the compatibilty level of all
> my
> subscriptions to 2005 and I was able to add this new one back in.
> Thanks for the response.
> "Paul Ibison" wrote:
|||I scrpited out my replication as a delete and create.
I ran the delete with no issues, but when I tried to run the create that's
when I fell into the compatibilty issue.
i changed all the SQL 200 compatibity levels to 2005 and changed my script
from 80rtm to 90rtm on the create statement of the script SQl generated.
I reran the create and it created with no issues. I started my agent and
synced my data since I only care about what happens here in my main office
anything changed at my branch got overwritten if there were any changes at
both places.
Is that the same way you did yours? maybe you had a better solution, mine
seemed to work for me but I am always looking for better and different
solutions.
"Rafael Lenartowicz" wrote:
> John,
> I had very similar situation just the other day, where somebody deleted
> publication on SQL2000's level while
> there were some pubs already created in 2005's level and I needed to
> recreate the publication.
> I somehow managed to fix it, but I'm afraid I might have lost some
> unsynchronized data in the process - I'm not sure it was the best way to do
> it. Could you post step by step description how did you handle it?
> thanks,
> r
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:C284873D-DD99-45A5-A191-047EE33E004E@.microsoft.com...
>
>
|||how did you manage to change the compatibility level? using
sp_changemergepublication ?
I wasn't able to do it, it was complaining something about the incompatible
snapshot...
besides - in my setup the data from the remote location IS the vital data -
they submiting their
daily revenue reports to the central office reporting server, I can't have
anything overwritten or lost.
thanks
r
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:EE97BCC9-6445-44F4-BF33-D115DDD98B43@.microsoft.com...[vbcol=seagreen]
>I scrpited out my replication as a delete and create.
> I ran the delete with no issues, but when I tried to run the create that's
> when I fell into the compatibilty issue.
> i changed all the SQL 200 compatibity levels to 2005 and changed my script
> from 80rtm to 90rtm on the create statement of the script SQl generated.
> I reran the create and it created with no issues. I started my agent and
> synced my data since I only care about what happens here in my main office
> anything changed at my branch got overwritten if there were any changes at
> both places.
> Is that the same way you did yours? maybe you had a better solution, mine
> seemed to work for me but I am always looking for better and different
> solutions.
> "Rafael Lenartowicz" wrote:
Merge Replication Code Example - Where Can I Find One?
Hi,
I'm trying to program (c#) merge replication with one publisher DB
(SQL2000) and many subscribers (MSDE). I'm trying to find info and
examples online, but it seems pretty scarce. If you can recommend a
site, please let me know.
Thanks,
JJ
try this
http://support.microsoft.com/default...b;en-us;319646
Hilary Cotter
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
"JJ" <joe.jabour@.gmail.com> wrote in message
news:1118069995.044129.200820@.g47g2000cwa.googlegr oups.com...
> Hi,
> I'm trying to program (c#) merge replication with one publisher DB
> (SQL2000) and many subscribers (MSDE). I'm trying to find info and
> examples online, but it seems pretty scarce. If you can recommend a
> site, please let me know.
> Thanks,
> JJ
>
|||Looks good, Thanks!
I'm trying to program (c#) merge replication with one publisher DB
(SQL2000) and many subscribers (MSDE). I'm trying to find info and
examples online, but it seems pretty scarce. If you can recommend a
site, please let me know.
Thanks,
JJ
try this
http://support.microsoft.com/default...b;en-us;319646
Hilary Cotter
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
"JJ" <joe.jabour@.gmail.com> wrote in message
news:1118069995.044129.200820@.g47g2000cwa.googlegr oups.com...
> Hi,
> I'm trying to program (c#) merge replication with one publisher DB
> (SQL2000) and many subscribers (MSDE). I'm trying to find info and
> examples online, but it seems pretty scarce. If you can recommend a
> site, please let me know.
> Thanks,
> JJ
>
|||Looks good, Thanks!
Merge replication and spids left behind
We are using Merge replication with clients from remote offices (SQL
2000, sp3). Recently, I have had a problem with users who are
replicating, and they shut down their laptops. The connection never
dies, and I end up with major blocking issues related to the
"orphaned" spid. The tables that are blocked are used to filter data
on each client. Since the orphaned spid is blocking, backups will run
forever, and have to be killed, and a SQL management job that
inserts/updates data in these tables has to be killed.
2000, sp3). Recently, I have had a problem with users who are
replicating, and they shut down their laptops. The connection never
dies, and I end up with major blocking issues related to the
"orphaned" spid. The tables that are blocked are used to filter data
on each client. Since the orphaned spid is blocking, backups will run
forever, and have to be killed, and a SQL management job that
inserts/updates data in these tables has to be killed.
If I kill the spid, it shows a rollback at 0% and the status never
changes. The user has disconnected, and there is really nothing to
roll back. How can I get rid of this spid with out restarting SQL
server, or rebooting my server?
Any help would be greatly appreciated.
Thanks,
Amy Mamarshall@.rhtc.net (Amy M) wrote in message news:<119d3885.0408101552.7fe7cd72@.posting.google.com>...
> We are using Merge replication with clients from remote offices (SQL
> 2000, sp3). Recently, I have had a problem with users who are
> replicating, and they shut down their laptops. The connection never
> dies, and I end up with major blocking issues related to the
> "orphaned" spid. The tables that are blocked are used to filter data
> on each client. Since the orphaned spid is blocking, backups will run
> forever, and have to be killed, and a SQL management job that
> inserts/updates data in these tables has to be killed.
> If I kill the spid, it shows a rollback at 0% and the status never
> changes. The user has disconnected, and there is really nothing to
> roll back. How can I get rid of this spid with out restarting SQL
> server, or rebooting my server?
> Any help would be greatly appreciated.
> Thanks,
> Amy M
This KB article might be useful:
http://support.microsoft.com/defaul...kb;en-us;818552
If this doesn't help, you may want to post in
microsoft.public.sqlserver.replication, as your problem seems to be
quite specific.
Simon
Monday, February 20, 2012
Merge replication and long disconnections
We have a system that uses SQL2000 (SP2) with the main database in our main
location. We have been working on setting up replication to a secondary
location, and believe we have solved the relevant issues there. We have
just been asked (read told) to set up replication to another server that
will, by it's nature, be disconnected for extended periods of time (at least
weeks) with no possibility of getting an internet connection during that
time. Can we setup merge replication in this situation, what are the
"gotchas" that we need to design around? We must use merge replication
since some of our fields are text fields.
We are initially looking at setting up the client to be disconnected as a
pull subscription, while the secondary client is a push subscription from
the publisher at the main location. Does this make sense?
All input appreciated.
TIA
Ron L.
Ron,
the most striking thing to get right is @.retention. By default it'll be 14
days which isn't long enough in your case.
Push or Pull is your choice depending on who you want to be in control. If
you know when the remote client will be connected on a regular basis, then
push is still an option, but in your scenario I agree that pull seems more
reasonable.
Regards,
Paul Ibison
|||Paul
Thanks for the pointer. I'll take a look for @.retention.
Ron L.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ecYZkfaFEHA.712@.tk2msftngp13.phx.gbl...
> Ron,
> the most striking thing to get right is @.retention. By default it'll be 14
> days which isn't long enough in your case.
> Push or Pull is your choice depending on who you want to be in control. If
> you know when the remote client will be connected on a regular basis, then
> push is still an option, but in your scenario I agree that pull seems more
> reasonable.
> Regards,
> Paul Ibison
>
location. We have been working on setting up replication to a secondary
location, and believe we have solved the relevant issues there. We have
just been asked (read told) to set up replication to another server that
will, by it's nature, be disconnected for extended periods of time (at least
weeks) with no possibility of getting an internet connection during that
time. Can we setup merge replication in this situation, what are the
"gotchas" that we need to design around? We must use merge replication
since some of our fields are text fields.
We are initially looking at setting up the client to be disconnected as a
pull subscription, while the secondary client is a push subscription from
the publisher at the main location. Does this make sense?
All input appreciated.
TIA
Ron L.
Ron,
the most striking thing to get right is @.retention. By default it'll be 14
days which isn't long enough in your case.
Push or Pull is your choice depending on who you want to be in control. If
you know when the remote client will be connected on a regular basis, then
push is still an option, but in your scenario I agree that pull seems more
reasonable.
Regards,
Paul Ibison
|||Paul
Thanks for the pointer. I'll take a look for @.retention.
Ron L.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ecYZkfaFEHA.712@.tk2msftngp13.phx.gbl...
> Ron,
> the most striking thing to get right is @.retention. By default it'll be 14
> days which isn't long enough in your case.
> Push or Pull is your choice depending on who you want to be in control. If
> you know when the remote client will be connected on a regular basis, then
> push is still an option, but in your scenario I agree that pull seems more
> reasonable.
> Regards,
> Paul Ibison
>
Merge replication and deadlocks
Hi,
I have an application that have a single publisher with 7 anonymous merge
subscriber (SQL2000). Distributor is in my publication server.
I have 7 publications (one db) - most of them synchronizing every half an
hour or so.
However, I have a publication that needs to be as up-to-date in every server
at all times. So, I am running the agent continuosly in each subscriber.
Usually it works ok, but sometimes, when there is a big batch update on the
tables belonging to that publication all merge agents block themselves
trying to process the replication.
I tried using the "Concurrent Merge Processes" setting to limit the number
of processes running, but it seemed to me that processes waiting in line
blocked other processes - in fact I got to a point where *all* my
subscribers said at the same time that they were waiting in the queue
because otherwise they would exceed the limit of concurrent processes.
The "Concurrent Merge Processes" settings is what I consider should fix my
problem - sadly it makes it worse. I could change the profile of the agents
but not sure where I should start on (and it is not easy to know if things
are better or worse either).
Maybe moving the distributor to another server?
Well... maybe you can point to some place where there is information about
replication optimization...
Thanks, Jos Araujo.
you have to edit your agent properties and set StartQueueTimeout to
something like 60.
You might also want to reindex msmerge_contents, tombstone, and genhistory
nightly.
Hilary Cotter
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
"Jos Araujo" <josea@.mrcinc.com> wrote in message
news:uXorChWtFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have an application that have a single publisher with 7 anonymous merge
> subscriber (SQL2000). Distributor is in my publication server.
> I have 7 publications (one db) - most of them synchronizing every half an
> hour or so.
> However, I have a publication that needs to be as up-to-date in every
server
> at all times. So, I am running the agent continuosly in each subscriber.
> Usually it works ok, but sometimes, when there is a big batch update on
the
> tables belonging to that publication all merge agents block themselves
> trying to process the replication.
> I tried using the "Concurrent Merge Processes" setting to limit the number
> of processes running, but it seemed to me that processes waiting in line
> blocked other processes - in fact I got to a point where *all* my
> subscribers said at the same time that they were waiting in the queue
> because otherwise they would exceed the limit of concurrent processes.
> The "Concurrent Merge Processes" settings is what I consider should fix my
> problem - sadly it makes it worse. I could change the profile of the
agents
> but not sure where I should start on (and it is not easy to know if things
> are better or worse either).
> Maybe moving the distributor to another server?
> Well... maybe you can point to some place where there is information about
> replication optimization...
> Thanks, Jos Araujo.
>
|||Thanks! setting the timeout really helped.
Jos.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Oto7FQbtFHA.2076@.TK2MSFTNGP14.phx.gbl...
> you have to edit your agent properties and set StartQueueTimeout to
> something like 60.
> You might also want to reindex msmerge_contents, tombstone, and genhistory
> nightly.
> --
> Hilary Cotter
> 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
> "Jos Araujo" <josea@.mrcinc.com> wrote in message
> news:uXorChWtFHA.2892@.TK2MSFTNGP10.phx.gbl...
> server
> the
> agents
>
I have an application that have a single publisher with 7 anonymous merge
subscriber (SQL2000). Distributor is in my publication server.
I have 7 publications (one db) - most of them synchronizing every half an
hour or so.
However, I have a publication that needs to be as up-to-date in every server
at all times. So, I am running the agent continuosly in each subscriber.
Usually it works ok, but sometimes, when there is a big batch update on the
tables belonging to that publication all merge agents block themselves
trying to process the replication.
I tried using the "Concurrent Merge Processes" setting to limit the number
of processes running, but it seemed to me that processes waiting in line
blocked other processes - in fact I got to a point where *all* my
subscribers said at the same time that they were waiting in the queue
because otherwise they would exceed the limit of concurrent processes.
The "Concurrent Merge Processes" settings is what I consider should fix my
problem - sadly it makes it worse. I could change the profile of the agents
but not sure where I should start on (and it is not easy to know if things
are better or worse either).
Maybe moving the distributor to another server?
Well... maybe you can point to some place where there is information about
replication optimization...
Thanks, Jos Araujo.
you have to edit your agent properties and set StartQueueTimeout to
something like 60.
You might also want to reindex msmerge_contents, tombstone, and genhistory
nightly.
Hilary Cotter
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
"Jos Araujo" <josea@.mrcinc.com> wrote in message
news:uXorChWtFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have an application that have a single publisher with 7 anonymous merge
> subscriber (SQL2000). Distributor is in my publication server.
> I have 7 publications (one db) - most of them synchronizing every half an
> hour or so.
> However, I have a publication that needs to be as up-to-date in every
server
> at all times. So, I am running the agent continuosly in each subscriber.
> Usually it works ok, but sometimes, when there is a big batch update on
the
> tables belonging to that publication all merge agents block themselves
> trying to process the replication.
> I tried using the "Concurrent Merge Processes" setting to limit the number
> of processes running, but it seemed to me that processes waiting in line
> blocked other processes - in fact I got to a point where *all* my
> subscribers said at the same time that they were waiting in the queue
> because otherwise they would exceed the limit of concurrent processes.
> The "Concurrent Merge Processes" settings is what I consider should fix my
> problem - sadly it makes it worse. I could change the profile of the
agents
> but not sure where I should start on (and it is not easy to know if things
> are better or worse either).
> Maybe moving the distributor to another server?
> Well... maybe you can point to some place where there is information about
> replication optimization...
> Thanks, Jos Araujo.
>
|||Thanks! setting the timeout really helped.
Jos.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Oto7FQbtFHA.2076@.TK2MSFTNGP14.phx.gbl...
> you have to edit your agent properties and set StartQueueTimeout to
> something like 60.
> You might also want to reindex msmerge_contents, tombstone, and genhistory
> nightly.
> --
> Hilary Cotter
> 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
> "Jos Araujo" <josea@.mrcinc.com> wrote in message
> news:uXorChWtFHA.2892@.TK2MSFTNGP10.phx.gbl...
> server
> the
> agents
>
Subscribe to:
Posts (Atom)