I've broken my SQL Server 2K - Please help

HI,
Having spent some time playing around with replication across the net, I
decided to start from scratch with some hints and tips on other sites BUT I
started to remove the replication in the wrong order and it now wont allow me
to disable replication.
The message I get is;
error 3724 cannot drop procedure dbo.sp_selxxxx because its being used for
replication
Any ideas?
Thanks
Pls try using sp_removedbreplication to get rid of any remaining system
objects.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
I put that SP into the Analyzer and the same message still appears!
What now!
Thanks
TIM
"Paul Ibison" wrote:

> Pls try using sp_removedbreplication to get rid of any remaining system
> objects.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Hi Paul,
Dont worry, have sorted it now.
Thanks for that.
TIM
Also just one other question.....
When I set up the distibutor/publisher on the main server I then setup a
subscriber PUSH to my other server. Do I need to setup a PULL/PUSH on the
subscriber as well or just that one well alone.
Thanks
""confused"" wrote:
[vbcol=seagreen]
> Hi Paul,
> I put that SP into the Analyzer and the same message still appears!
> What now!
> Thanks
> TIM
> "Paul Ibison" wrote:
|||Did you get the same message when running the proc or after running it and
when trying to delete the proc? Presumably the proc is in the same database?
Anyway, please try dropping them from Query analyser directly:
drop procedure dbo.[sp_sel_EE1F8F95DC214CA5ED44BBA96EA645E7_pal]
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

I've been told that SQL Server replication is buggy and unreliable

Hi, I've recently been told that SQL Server replication is buggy and unreliable. I find this very hard to believe. I'm working with a subcontractor in another state that is hosting a large DB for us. I want to replicate a read-only copy of the DB over
the internetl to our local site. They also tell me that it is difficult to recover when there is a problem with replication. I again find this hard to believe. The worst case it to re-snapshot.
I've been working with SQL Server since 2002, but not replication. I've found SQL Server to be very reliable and nice to work with.
I would greatly appreciate honest feedback.
Thanks very much,
Griff
Griff,
I've used it for 4 years and found it to be robust. I teach replication to
financial staff in London and it is widely used by insurance companies and
banks, which might make you feel more confident about its usefulness.
To be honest, almost always what I initially thought was a limitation in the
technology actually turned out to be me using the wrong implementation, or
not being aware of existing workarounds.
For someone getting started, BOL (and Hilary's upcoming books) should
provide the foundation needed to make correct choices. For troubleshooting
I'd advise anyone starting out to implement replication in as many different
ways as possible, see when you've broken it and then research this newsgroup
thoroughly.
HTH,
Paul Ibison
Also, I keep a scratchpad of errors/solutions I've seen, or read about and
tested at www.replicationanswers.com.
|||Griff,
I've used it for 4 years and found it to be robust. I teach replication to
financial staff in London and it is widely used by insurance companies and
banks, which might make you feel more confident about its usefulness.
To be honest, almost always what I initially thought was a limitation in the
technology actually turned out to be me using the wrong implementation, or
not being aware of existing workarounds.
For someone getting started, BOL (and Hilary's upcoming books) should
provide the foundation needed to make correct choices. For troubleshooting
I'd advise anyone starting out to implement replication in as many different
ways as possible, see when you've broken it and then research this newsgroup
thoroughly.
HTH,
Paul Ibison
Also, I keep a scratchpad of errors/solutions I've seen, or read about and
tested at www.replicationanswers.com.
|||Replication is not buggy per se.
the problem with replication is that it is depenedent on often reliable
links. Sometimes a LAN connection which is good enough for day to day use,
will turn out to be too unreliable or unstable for replication.
Replication is resilient to many errors, but not a poor connection.
Its akin to someone taking a gravel road and complaining about the car.
Another problem with replication is that it is a complex product that few
dba's really understand well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eI8AqChXEHA.384@.TK2MSFTNGP10.phx.gbl...
> Griff,
> I've used it for 4 years and found it to be robust. I teach replication to
> financial staff in London and it is widely used by insurance companies and
> banks, which might make you feel more confident about its usefulness.
> To be honest, almost always what I initially thought was a limitation in
the
> technology actually turned out to be me using the wrong implementation, or
> not being aware of existing workarounds.
> For someone getting started, BOL (and Hilary's upcoming books) should
> provide the foundation needed to make correct choices. For troubleshooting
> I'd advise anyone starting out to implement replication in as many
different
> ways as possible, see when you've broken it and then research this
newsgroup
> thoroughly.
> HTH,
> Paul Ibison
> Also, I keep a scratchpad of errors/solutions I've seen, or read about and
> tested at www.replicationanswers.com.
>
|||Replication is not buggy per se.
the problem with replication is that it is depenedent on often reliable
links. Sometimes a LAN connection which is good enough for day to day use,
will turn out to be too unreliable or unstable for replication.
Replication is resilient to many errors, but not a poor connection.
Its akin to someone taking a gravel road and complaining about the car.
Another problem with replication is that it is a complex product that few
dba's really understand well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eI8AqChXEHA.384@.TK2MSFTNGP10.phx.gbl...
> Griff,
> I've used it for 4 years and found it to be robust. I teach replication to
> financial staff in London and it is widely used by insurance companies and
> banks, which might make you feel more confident about its usefulness.
> To be honest, almost always what I initially thought was a limitation in
the
> technology actually turned out to be me using the wrong implementation, or
> not being aware of existing workarounds.
> For someone getting started, BOL (and Hilary's upcoming books) should
> provide the foundation needed to make correct choices. For troubleshooting
> I'd advise anyone starting out to implement replication in as many
different
> ways as possible, see when you've broken it and then research this
newsgroup
> thoroughly.
> HTH,
> Paul Ibison
> Also, I keep a scratchpad of errors/solutions I've seen, or read about and
> tested at www.replicationanswers.com.
>

issues with replication on a cluster?

OK please forgive me. I dont normally cross post, or ask the same question
twice, but Im in quite a pickle here.
Ive been using replication in a non clustered environment for years now. Ive
never seen extreme slowness with the initial snapshot of replication like Im
seeing currently. And to top it off my boxes are the fastest Ive ever used.
Dual 3.0 Zeons.
4 gigs Ram.
1 Gigabit network.
1 way, Transactional, Continuous, Remote Distributor (on the Subscriber), 25
gig database.
Im getting better snapshot performance on two old, 512 mb ram development
boxes. Could this be a Clustering issue?
SQL2K SP3
TIA, ChrisR
I recall having something similar happen on one of our cluster servers.
IIRC, we stopped the snapshot and restarted it, and got better performance.
I'll have to confirm this with the other DBA who was working on this
problem, but won't be able to do this till Tuesday.
"ChrisR" <bla@.noemail.com> wrote in message
news:%230IneMsFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OK please forgive me. I dont normally cross post, or ask the same question
> twice, but Im in quite a pickle here.
> Ive been using replication in a non clustered environment for years now.
> Ive
> never seen extreme slowness with the initial snapshot of replication like
> Im
> seeing currently. And to top it off my boxes are the fastest Ive ever
> used.
> Dual 3.0 Zeons.
> 4 gigs Ram.
> 1 Gigabit network.
>
> 1 way, Transactional, Continuous, Remote Distributor (on the Subscriber),
> 25
> gig database.
> Im getting better snapshot performance on two old, 512 mb ram development
> boxes. Could this be a Clustering issue?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Hi
Never seen that, but I have seen general performance drop when moving to a
cluster as the SAN was not as optimal as everyone thought.
The initial snapshot is very IO intensive.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <bla@.noemail.com> wrote in message
news:#0IneMsFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OK please forgive me. I dont normally cross post, or ask the same question
> twice, but Im in quite a pickle here.
> Ive been using replication in a non clustered environment for years now.
Ive
> never seen extreme slowness with the initial snapshot of replication like
Im
> seeing currently. And to top it off my boxes are the fastest Ive ever
used.
> Dual 3.0 Zeons.
> 4 gigs Ram.
> 1 Gigabit network.
>
> 1 way, Transactional, Continuous, Remote Distributor (on the Subscriber),
25
> gig database.
> Im getting better snapshot performance on two old, 512 mb ram development
> boxes. Could this be a Clustering issue?
> --
> SQL2K SP3
> TIA, ChrisR
>

issues with replication on a cluster?

OK please forgive me. I dont normally cross post, or ask the same question
twice, but Im in quite a pickle here.
Ive been using replication in a non clustered environment for years now. Ive
never seen extreme slowness with the initial snapshot of replication like Im
seeing currently. And to top it off my boxes are the fastest Ive ever used.
Dual 3.0 Zeons.
4 gigs Ram.
1 Gigabit network.
1 way, Transactional, Continuous, Remote Distributor (on the Subscriber), 25
gig database.
Im getting better snapshot performance on two old, 512 mb ram development
boxes. Could this be a Clustering issue?
SQL2K SP3
TIA, ChrisR
I recall having something similar happen on one of our cluster servers.
IIRC, we stopped the snapshot and restarted it, and got better performance.
I'll have to confirm this with the other DBA who was working on this
problem, but won't be able to do this till Tuesday.
"ChrisR" <bla@.noemail.com> wrote in message
news:%230IneMsFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OK please forgive me. I dont normally cross post, or ask the same question
> twice, but Im in quite a pickle here.
> Ive been using replication in a non clustered environment for years now.
> Ive
> never seen extreme slowness with the initial snapshot of replication like
> Im
> seeing currently. And to top it off my boxes are the fastest Ive ever
> used.
> Dual 3.0 Zeons.
> 4 gigs Ram.
> 1 Gigabit network.
>
> 1 way, Transactional, Continuous, Remote Distributor (on the Subscriber),
> 25
> gig database.
> Im getting better snapshot performance on two old, 512 mb ram development
> boxes. Could this be a Clustering issue?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Hi
Never seen that, but I have seen general performance drop when moving to a
cluster as the SAN was not as optimal as everyone thought.
The initial snapshot is very IO intensive.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisR" <bla@.noemail.com> wrote in message
news:#0IneMsFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> OK please forgive me. I dont normally cross post, or ask the same question
> twice, but Im in quite a pickle here.
> Ive been using replication in a non clustered environment for years now.
Ive
> never seen extreme slowness with the initial snapshot of replication like
Im
> seeing currently. And to top it off my boxes are the fastest Ive ever
used.
> Dual 3.0 Zeons.
> 4 gigs Ram.
> 1 Gigabit network.
>
> 1 way, Transactional, Continuous, Remote Distributor (on the Subscriber),
25
> gig database.
> Im getting better snapshot performance on two old, 512 mb ram development
> boxes. Could this be a Clustering issue?
> --
> SQL2K SP3
> TIA, ChrisR
>

Issues with removing replication

SQL Server 2005 Standard replicating to an identical server.

My issue:

When we go into the database to remove a large number of rows, it says we are unable to delete since the database is in replication. Is there a way to pause, or stop replication temporarily? I stopped the log reader but it still gave me the same error.

The only way I have found around this is to delete the publication and re-create it when I'm ready. Now though, I'm unable to delete the publication. SQL Server Management Studio will freeze if I either try to delete something from the UI or command line. This happens with objects in SQL Server as well as rows and such. It will just freeze and I have to kill the process.

Please Help!

Can this be caused by a long running transaction which is not completed yet?

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

|||i don't think so since i tried deleting a single row and it sat there for 16 minutes. i had to kill it since no one could connect to the database. but before this situation deleting a couple hundred rows would take about 1 second.|||

Can you try to connect to "master" using 'sa' and do sp_who (sp_who2) and see "who" is accessing that database?

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

|||i did
USE master
EXEC sp_who

i returned 37 processes. i don't know how to display it on the forum.|||

Are you trying to use TRUNCATE? This isn't allowed as indicated by the error message. You'll have to use explicit DELETE command. If this isn't what you were doing, the post the command you were executing and the error message you received.

|||If you want to remove replication, and you have limited resources on your box, try stopping sql server agent service first, then do the uninstall. Depending on how many rows you have in MSrepl_commands, it may take a while.|||

Before you post the result from sp_who2, can you check the "blkby" value where "DBName" is equal to the target database? I just try to understand if any process to that database is blocked by some other processes?

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

|||

if u want to remove replication what u should do is

(a) Press right button on Replicaiton

(b) Select Disable Publication and distribution

(c) Go step by step

you can remove replication and there by Distribution and publicaiton also.

Madhu

|||

Greg Y wrote:

Are you trying to use TRUNCATE? This isn't allowed as indicated by the error message. You'll have to use explicit DELETE command. If this isn't what you were doing, the post the command you were executing and the error message you received.

It

doesn't matter which command I try. And even if I try to delete

something from the GUI it fails. It's not limited to data either. If I

try to delete an object, or try to disable replication I get the same

result; SQL lockup.

|||

Madhu K Nair wrote:

if u want to remove replication what u should do is

(a) Press right button on Replicaiton

(b) Select Disable Publication and distribution

(c) Go step by step

you can remove replication and there by Distribution and publicaiton also.

Madhu

I've tried this. SQL locks up. This only happens on 1 server. I've tried uninstalling and re-installing sql server 2005. i still get the same result.|||

Did you try stopping the sql server agent service first like I mentioned above? I also mentioned you might have hardware constraints, how much memory/cpu/disk spindles do you have? Also what kind of replication are you trying to remove, merge or transactional? How many publications, how many rows exist in distribution.dbo.MSrepl_commands table, how many rows exist in [published db].dbo.MSmerge_contents?

|||

Greg Y wrote:

Did you try stopping the sql server agent service first like I mentioned above? I also mentioned you might have hardware constraints, how much memory/cpu/disk spindles do you have? Also what kind of replication are you trying to remove, merge or transactional? How many publications, how many rows exist in distribution.dbo.MSrepl_commands table, how many rows exist in [published db].dbo.MSmerge_contents?

i'm actually unable to try stopping the service until this weekend. it's our main production server so i can't interrupt service. the only hardware constraints we may run into is HDD read/write. we only have a pair of 15K SCSI drives in RAID1. we plan on upgrading to 8 drives in RAID10. but other than that the servers are 4 way Intel dual cores with 8 physical cores and 8 logical cores, and 16GB of RAM.

It's transactional replication with a single publication. i'm not sure how to find the number of rows in the locations you asked for though?|||

You can query the tables I mentioned above.

Also, I highly suggest running permon to debug OS performance bottleneck/issues. If publisher/distributor are on the same machine, and given you only have two disks, I will guess that disk might be bottlenecking. But you need to do some investigation on your part first, start with perfmon.

|||

Greg Y wrote:

You can query the tables I mentioned above.

Also, I highly suggest running permon to debug OS performance bottleneck/issues. If publisher/distributor are on the same machine, and given you only have two disks, I will guess that disk might be bottlenecking. But you need to do some investigation on your part first, start with perfmon.

We've done performance monitoring analysis for other reasons and the disks weren't even a bottleneck. They are the only part of the system that was really stressed, but no issues. We even had a consulting firm come in and spend a couple months scripting out a load test which went really well.

Is it possible that corrupt data in the database could be causing this issue?

issues deploying snapshot for merge replication.

I'm having issues setting up merge replication and the errors I'm getting are inconsistent but lead me to believe there is a bigger underlying issue than what they actually indicate.

Issue: unable to initialise subscriber with snapshot for merge replication. Fails for various reasons regardless of environment and setup. Previously this has deployed fine but now it is not.

Publisher is SQL 2005 (9.0.2047), Subscribers are SQLExpress or SQL Dev

The various errors I get are as follows:

These come mixed together generally... Unable to INSERT ... cannot insert NULL into column rowguid. [This error comes up on different tables for different snapshots, including ones with no records] bcp (Bulk Copy) error 20253 batch send failed Unspecified error Failed to send batch after max errors end of file reached, terminator missing or field data incomplete

Actions I have taken so far, all to no effect:

Adjusted Agent Profile to increase timeout values and decrease packet sizes Took new snapshots (tables where errors occured changed but still ame errors) Created a local subscription database on the server to remove any network related issues (using merge sync over VPN) Deleted and recreated Publication Created second publication with different articles on same database to find same errors in different locations again

I've tried reinitializing subscriptions (before subscribers were dropped and I tried recreating) but the DROP command fails due to FK constraints so if I reinitialize any subscriptions they will fail to be applied. Have tried editing the properties to just delete data and not drop tables but that still failed.

I've tried setting up subscriber without snapshot - created publication and snapshot, backed up db, copied to subscriber and restored there. See this article: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=689428&SiteID=1 This bloke suffered same results and the response from MS was to change the help files, not functionality. Doesn't help us dealing with subscribers via satellite AND unable to deploy snapshots...

I've checked the service pack releases to see if any issues like this are addressed and found nothing relevent.

My gut feeling is that the snapshot is not being generated correctly for some reason - system stored procedure errors perhaps.

So far my experience with replication has been good, with the odd issue or frustrating limitation in older versions, but this is really killing me...

Barry

(Sorry that author is "Anonymous" - must have messed that up)

Hey Barry it sounds like you have some problems there.

I would check a few things, maybe you have already done it but perhaps not.

Check that the snapshot is not getting corrupted by something when it is getting transferred accross the network / internet. You could try copying it via FTP and applying manually to the subscriber and see if that helps. Could also look into compressed snapshots for that too. Do a CRC check on the snapshot file and make sure it matches on both ends. You can get a util for that here: http://www.kmrconsulting.com/free_download.html

For that table with the null problems check that the default value for rowguid is set to newid() or newsequential() even though there is no data in there i suspect replication is doing some testing when it sets up the subscription to make sure things are running.

It sounds like your failure is with the bcp so perhaps after the tables are extracted try adding a few triggers on the troublesome tables that will record what replication is trying to insert or update. That will at least give you more info on what it is trying to do on the table.

Also take a look at the procedures that get run when the subscriber is applying the snapshot. Try running them one by one manually and issolate the problem more.

Good Luck!

Martin

issues deploying snapshot for merge replication.

I'm having issues setting up merge replication and the errors I'm getting are inconsistent but lead me to believe there is a bigger underlying issue than what they actually indicate.

Issue: unable to initialise subscriber with snapshot for merge replication. Fails for various reasons regardless of environment and setup. Previously this has deployed fine but now it is not.

Publisher is SQL 2005 (9.0.2047), Subscribers are SQLExpress or SQL Dev

The various errors I get are as follows:

These come mixed together generally... Unable to INSERT ... cannot insert NULL into column rowguid. [This error comes up on different tables for different snapshots, including ones with no records] bcp (Bulk Copy) error 20253 batch send failed Unspecified error Failed to send batch after max errors end of file reached, terminator missing or field data incomplete

Actions I have taken so far, all to no effect:

Adjusted Agent Profile to increase timeout values and decrease packet sizes Took new snapshots (tables where errors occured changed but still ame errors) Created a local subscription database on the server to remove any network related issues (using merge sync over VPN) Deleted and recreated Publication Created second publication with different articles on same database to find same errors in different locations again

I've tried reinitializing subscriptions (before subscribers were dropped and I tried recreating) but the DROP command fails due to FK constraints so if I reinitialize any subscriptions they will fail to be applied. Have tried editing the properties to just delete data and not drop tables but that still failed.

I've tried setting up subscriber without snapshot - created publication and snapshot, backed up db, copied to subscriber and restored there. See this article: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=689428&SiteID=1 This bloke suffered same results and the response from MS was to change the help files, not functionality. Doesn't help us dealing with subscribers via satellite AND unable to deploy snapshots...

I've checked the service pack releases to see if any issues like this are addressed and found nothing relevent.

My gut feeling is that the snapshot is not being generated correctly for some reason - system stored procedure errors perhaps.

So far my experience with replication has been good, with the odd issue or frustrating limitation in older versions, but this is really killing me...

Barry

(Sorry that author is "Anonymous" - must have messed that up)

Hey Barry it sounds like you have some problems there.

I would check a few things, maybe you have already done it but perhaps not.

Check that the snapshot is not getting corrupted by something when it is getting transferred accross the network / internet. You could try copying it via FTP and applying manually to the subscriber and see if that helps. Could also look into compressed snapshots for that too. Do a CRC check on the snapshot file and make sure it matches on both ends. You can get a util for that here: http://www.kmrconsulting.com/free_download.html

For that table with the null problems check that the default value for rowguid is set to newid() or newsequential() even though there is no data in there i suspect replication is doing some testing when it sets up the subscription to make sure things are running.

It sounds like your failure is with the bcp so perhaps after the tables are extracted try adding a few triggers on the troublesome tables that will record what replication is trying to insert or update. That will at least give you more info on what it is trying to do on the table.

Also take a look at the procedures that get run when the subscriber is applying the snapshot. Try running them one by one manually and issolate the problem more.

Good Luck!

Martin

Issues about deploy Business Logic Handler to a web server for web replication.

Pocket PC 2003, SQL Compact Edition, SQL2005, IIS6.0

I implemented a business logic handler to deal with conflicts. When I deploy it on the SQL server which is also the web replication server, the logic handler seems working fine. However, if I deploy this handler to another web server. The logic handler failed to be loaded.

My enviroment settings are desribed as below.

Machine A, distributor, with database and publication. The business logic handler is deployed at C:\Program Files\Microsoft SQL Server\90\COM\BusinessLogicHandler.dll. It's registered by using sp_registercustomresolver. The @.assembly is specified as @.assembly=C:\Program Files\Microsoft SQL Server\90\COM\BusinessLogicHandler.dll';

Machine B, IIS server. The same business logic handler is deployed at C:\Program Files\Microsoft SQL Server\90\COM\BusinessLogicHandler.dll on the Machine B itself.

When I ran the web replication, the Merge Agent reported the error as below.

Error loading custom assembly "C:\Program Files\Microsoft SQL Server\90\COM\BusinessLogicHandler.dll", Error: "Could not load file or assembly 'C:\\Program Files\\Microsoft SQL Server\\90\\COM\\BusinessLogicHandler.dll' or one of its dependencies. The given assembly name or codebase was invalid.

It seemed that the Merge Agent had trouble to find my logic handler because the path reported in the error log has two backward slashes. I have no idea where did that came from. I am not sure if that's the cause of the error. Without business logic handler. I had successfully finished web replication of Machine B to sync with Machine A. If I setup web replication directly on Machine A with business logic handler, I can successfully sync as well.

Does anyone has any idea about how to correctly deploy business logic handler on a web server?

Thanks,

Nigel

It seems that if I sign the dll and install it in the global assembly of Mahine B. The problem went away. Does it mean we should deploy our assembly in the global assembly instead of the path "@.assembly" of distributor when we deploy our business logic handle to a IIS web replication server? The SQL2005 document does not specify how we should deploy the business logic handler for the web synchronization server. It would be nice if it could MS can provide more detail in business logic handler deploymont. However, it still seems to me it's a bug for the Merge Agent not be able to find the business logic handler in my previous post. Still don't know where the backward slashes come from.

If anyone has more details about this issue, please advise.

Thanks,

Nigel

Issue with Identity Columns after replication Changes

Dear all,

We have an application that use merge replication between MSDE and Devices with SQL CE.

Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:

- drop current replication

- recreate our replication

After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.

- Have you ever encounter this type of issue?

- What can create it?

- Is our approach fully inappropriate?

Please let me know if you need more information,

Best regards,

I have a client (SQL2000<->SqlCE) that is experiencing similar problems. We have been unable to determine a cause as yet.

Symptoms:

-"duplicate key on insert" errors during replication for one table (two subscribers appearing to be sharing the same number range)

-"duplicate key on insert" error with a back-end process inserting data into a publisher table (diff to the one above)

The tables are using automatic identity range management and have been synchronising happily for a long time prior to this.

Does anyone know of any actions that might cause the automatic identity range management to go screwy like this?

|||You can try re-adjusting your identity ranges, look at sp_adjustpublisheridentityrange. Otherwise i'm not sure what could have caused the issue, maybe someone manually reset the identity seed on the dbs?

Issue with Identity Columns after replication Changes

Dear all,

We have an application that use merge replication between MSDE and Devices with SQL CE.

Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:

- drop current replication

- recreate our replication

After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.

- Have you ever encounter this type of issue?

- What can create it?

- Is our approach fully inappropriate?

Please let me know if you need more information,

Best regards,

I have a client (SQL2000<->SqlCE) that is experiencing similar problems. We have been unable to determine a cause as yet.

Symptoms:

-"duplicate key on insert" errors during replication for one table (two subscribers appearing to be sharing the same number range)

-"duplicate key on insert" error with a back-end process inserting data into a publisher table (diff to the one above)

The tables are using automatic identity range management and have been synchronising happily for a long time prior to this.

Does anyone know of any actions that might cause the automatic identity range management to go screwy like this?

|||You can try re-adjusting your identity ranges, look at sp_adjustpublisheridentityrange. Otherwise i'm not sure what could have caused the issue, maybe someone manually reset the identity seed on the dbs?

Issue in Merge Replication

Hi Kevin,
I am setting up a SQL Server Database Merge Replication between Publisher
(P) & Subscriber (S) using EXCHANGE_TYPE '1' (Data only needs to be merged
from Subscriber to Publisher - Upload). Publisher priority is maximaum by
default.
However, I am facing this issue:
Under normal scenario, if a row of Data is changed at Subscriber, it is
updated at the Publisher since there is no change at Publisher.
But when same Row of Data in Publication (Table x) is updated at the
Publisher and also at the Subscriber (conflict situation), the row do not
particicipate in Replication when the agent runs and then it never
participates in Replication. Means the Data at Subscriber and Publisher
reamins different for that row. SQL Server do show any conflict that I can
resolve. Kindly help me to find out root cause and how to fix this!
Database : SQL Server 2000 SP3a
OS: Win2003 (No Service Pack)
Hi All,
I found the solution myself . Basically by changing the conflict resolver to
"Subscriber will always win the conflict" resolves the problem.
Cheers!
Vikas
"Vikas Kohli" wrote:

> Hi Kevin,
> I am setting up a SQL Server Database Merge Replication between Publisher
> (P) & Subscriber (S) using EXCHANGE_TYPE '1' (Data only needs to be merged
> from Subscriber to Publisher - Upload). Publisher priority is maximaum by
> default.
> However, I am facing this issue:
> Under normal scenario, if a row of Data is changed at Subscriber, it is
> updated at the Publisher since there is no change at Publisher.
> But when same Row of Data in Publication (Table x) is updated at the
> Publisher and also at the Subscriber (conflict situation), the row do not
> particicipate in Replication when the agent runs and then it never
> participates in Replication. Means the Data at Subscriber and Publisher
> reamins different for that row. SQL Server do show any conflict that I can
> resolve. Kindly help me to find out root cause and how to fix this!
> Database : SQL Server 2000 SP3a
> OS: Win2003 (No Service Pack)

ISSCEStatusReporting interface - replication progress in C++

Hi,

I'm not sure if my previous post was in the right forum so, please accept my apologies if I'm doing "double" post.

I have created 3 years ago C++ class wrapper -MFC like- for SQL Server CE 2.0. I migrated those two months ago to support SQL Mobile.

Today, I would like to get notifications from Replication progression but I couldn't figure how to override ISSCEStatusReporting interface.

If anyone could give hint or sample code, it will be greatly appreciated.

Thanks in advance,

Regards,
Fabien.

The merge process will call methods on an interface to an object instance that you have provided to it (ISSCEStatusReporting*).

If may help if you are familiar with COM Events, I find that process and ISSCEStatusReporting to be similar in principle (if not in exact implementation). I only mention this as I was working on COM Events elsewhere before my work on this, and the two seemed to gel in my mind.

The merge instance (your pointer to ISSCEMerge) expects a COM pointer. ISSCEStatusReporting is the interface it 'expects' from that pointer and call on.

So: Create a basic C++ class, deriving it from ISSCEStatusReporting.
Because ISSCEStatusReporting is abstract, the new class must implement all abstract methods - QueryInterface, AddRef, Release as well as your handler methods for OnStartTableUpload, OnStartTableDownload and OnSynchronization. Your "On..." handler methods will do whatever you need or want them to do (log info, fire event, etc), but the first three should operate like they always do for COM objects, if only for correct operation if not actually out of necessity.

Simple example is below:
Caveats: It's worked for me on WM5 device (no emulator) building with VS2005. I'm still in testing though. I think it may be possible to Cancel with these events (return a HRESULT other than S_OK?!) but I'm not going that far. My own usage of the code below is a little different as the class I use to manage the merge is itself the one providing the ISSCEStatusReporting interface. Because of this I've 'played' with the AddRefs.
Notes: It's useful to add TRACE statements or your equivalent while testing this, just to monitor what's going on. Be aware that this class will destroy itself when its ref count == 0, which should occur when you release your merge instance. Monitor TRACE output to find out? Let me know how you get on, hope this helps.

class myReporter : public ISSCEStatusReporting
{
private:
// reference counter
DWORD m_dwRefCount;
public:
// implement c'tor, d'tor, etc. and your own methods
// however this object is created, ensure dwRefCount == 0 at start
myReporter(...)
: dwRefCount(0)
{
...
}
virtual ~myReporter()
{
TRACE(L"~myReporter object being destroyed\n");
ASSERT(0 == dwRefCount); // should be no lingering references
}
// ISSCEStatusReporting
public:
// called during sync by SQL Server Mobile merge instance
STDMETHOD(OnStartTableUpload)(const WCHAR *wszTableName)
{
// do what you want here
TRACE(L"myReporter ::OnStartTableUpload: Table '%s'\n", wszTableName);
return S_OK;
}

// called during sync by SQL Server Mobile merge instance
STDMETHOD(OnStartTableDownload)(const WCHAR *wszTableName);
{
// do what you want here
TRACE(L"myReporter ::OnStartTableDownload: Table '%s'\n", wszTableName);
return S_OK;
}

// called during sync by SQL Server Mobile merge instance
STDMETHOD(OnSynchronization)(DWORD dwPrecentCompleted);
{
// do what you want here
TRACE(L"myReporter ::OnSynchronization: %2d%% complete\n", dwPrecentCompleted);
return S_OK;
}

// basic COM support (we're cheating a bit, but it will do)
STDMETHOD(QueryInterface)(REFIID iid, void **ppvObject);
{
if (iid == __uuidof(ISSCEStatusReporting))
{
// we've been asked for an interface of type ISSCEStatusReporting
// which we support
TRACE(L"myReporter ::QueryInterface for ISSCEStatusReporting\n");
m_dwRefCount++;
*ppvObject = (void *)this;
return S_OK;
}

if (iid == __uuidof(IUnknown))
{
// we've been asked for an interface of type ISSCEStatusReporting
// which we support
TRACE(L"myReporter ::QueryInterface for IUnknown\n");
m_dwRefCount++;
*ppvObject = (void *)this;
return S_OK;
}

TRACE(L"myReporter ::QueryInterface for unsupported interface\n");
return E_NOINTERFACE;
}

// basic COM support (we're cheating a bit, but it will do)
STDMETHOD_(ULONG, AddRef)();
{
TRACE(L"myReporter ::AddRef()\n");
m_dwRefCount++;
return m_dwRefCount;
}

// basic COM support (we're cheating a bit, but it will do)
STDMETHOD_(ULONG, Release)();
{
TRACE(L"myReporter ::Release()\n");
ULONG l;

l = m_dwRefCount--;

if ( 0 == m_dwRefCount)
{
TRACE(L"myReporter ::Release() m_dwRefCount == 0 \n");
delete this;
}

return l;
}
};

// Usage
myReporter *rep = new myReporter();
rep->AddRef();
hr = pIMerge->put_StatusReportingHandler(rep);
// you should see another 'AddRef' TRACE, maybe the QueryInterface too
// run merge
// when finished:
rep->Release();

|||

Thanks you very much with that sample!

It will help me to compare what I finally succeed to get working yesterday afternoon and your great sample, with good trace and practices.

I was having a leak somewhere and I'm sure that I will nbe able to correct it with your help.

I was not such aware of AddRef when you do not get this class by QueryInterface...

Big thanks for my project,
Cheers, Fabien.