Replication problem

Hi.

I am using SQL Server 2005 at the back-end, with a database published for merge replication; Windows Mobile-based handheld devices running a .NET 2.0 CF application, and SQL Mobile 2005.

Intermittently, when initialising the subscription on a device, the app cycles through the tables multiple times, or even endlessly.

Can anyone shed some light on why this is happening?

Try to check the Replication Monitor (server side) for status, including number of records submitted. There are some issues with merge replication in the SQL Server 2005 RTM, that have been fixed in SQL Server 2005 SP2.|||

Thanks for the response.

I checked the replication monitor as a matter of course - no problems there, everything looks fine.

Interesting what you say about SP2 - the box in question is running RTM, so maybe updating it will help. Is there anywhere that goes into detail on what these issues that you mention were, and if/how they have been addressed in SP2?

|||

There are various merge/SQL Compact fixes, all documented at: http://support.microsoft.com/default.aspx?scid=kb;en-us;921896
Amongst others:

919949 (http://support.microsoft.com/kb/919949/) FIX: The sp_MSinitDynamicSubscriber stored procedure is called more than one time by using the same parameters in SQL Server 2005

|||

Erik,

Thanks for that. I am recommending to my client that they upgrade to SP2, and will post back here with the results.

|||

My client is hoping to install SP2 in a couple of weeks. In the meantime, they applied the hotfix, but the problem still exists.

They applied the hotfix to the server running the instance of SQL 2005, but not to the machine with the SQL Mobile server tools (and SQL 2005 client components). Is this correct?

I am hoping that, despite the failure of the hotfix to resolve the issue, the full SP2 update will make a difference...

|||

Erik,

SP2 has now been applied to both servers, and the problem is now resolved.

Thanks very much for your help.

|||

We are facing the same problem... so suggested client to apply for Sp2.... still the problem pesrsists,,,,

Any idea as how to resolve it... please let me know if anything need to be changed after applying Sp2...

Waiting for an answer..

|||It is important to apply SP2 on all servers, including IIS (web) servers (replication components)|||

How to apply SQL server 2005 Service pack 2 on IIS web servers...

I really didnt understand this..can u please telll me what is it

|||If you are running IIS on a separate server, you need to update the replciation components to SP2, see this for info http://msdn2.microsoft.com/en-us/library/ms172937.aspx|||

I will update the replication componets on IIS servers, but what abt the "SQLCSA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

I will update the replication componets on IIS servers, but what abt the "SQLCESA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

Thank You Erik...

My Problem solved after applying SP2

GOKI

sql

Replication problem

Hi.

I am using SQL Server 2005 at the back-end, with a database published for merge replication; Windows Mobile-based handheld devices running a .NET 2.0 CF application, and SQL Mobile 2005.

Intermittently, when initialising the subscription on a device, the app cycles through the tables multiple times, or even endlessly.

Can anyone shed some light on why this is happening?

Try to check the Replication Monitor (server side) for status, including number of records submitted. There are some issues with merge replication in the SQL Server 2005 RTM, that have been fixed in SQL Server 2005 SP2.|||

Thanks for the response.

I checked the replication monitor as a matter of course - no problems there, everything looks fine.

Interesting what you say about SP2 - the box in question is running RTM, so maybe updating it will help. Is there anywhere that goes into detail on what these issues that you mention were, and if/how they have been addressed in SP2?

|||

There are various merge/SQL Compact fixes, all documented at: http://support.microsoft.com/default.aspx?scid=kb;en-us;921896
Amongst others:

919949 (http://support.microsoft.com/kb/919949/) FIX: The sp_MSinitDynamicSubscriber stored procedure is called more than one time by using the same parameters in SQL Server 2005

|||

Erik,

Thanks for that. I am recommending to my client that they upgrade to SP2, and will post back here with the results.

|||

My client is hoping to install SP2 in a couple of weeks. In the meantime, they applied the hotfix, but the problem still exists.

They applied the hotfix to the server running the instance of SQL 2005, but not to the machine with the SQL Mobile server tools (and SQL 2005 client components). Is this correct?

I am hoping that, despite the failure of the hotfix to resolve the issue, the full SP2 update will make a difference...

|||

Erik,

SP2 has now been applied to both servers, and the problem is now resolved.

Thanks very much for your help.

|||

We are facing the same problem... so suggested client to apply for Sp2.... still the problem pesrsists,,,,

Any idea as how to resolve it... please let me know if anything need to be changed after applying Sp2...

Waiting for an answer..

|||It is important to apply SP2 on all servers, including IIS (web) servers (replication components)|||

How to apply SQL server 2005 Service pack 2 on IIS web servers...

I really didnt understand this..can u please telll me what is it

|||If you are running IIS on a separate server, you need to update the replciation components to SP2, see this for info http://msdn2.microsoft.com/en-us/library/ms172937.aspx|||

I will update the replication componets on IIS servers, but what abt the "SQLCSA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

I will update the replication componets on IIS servers, but what abt the "SQLCESA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

Thank You Erik...

My Problem solved after applying SP2

GOKI

Replication problem

Hi.

I am using SQL Server 2005 at the back-end, with a database published for merge replication; Windows Mobile-based handheld devices running a .NET 2.0 CF application, and SQL Mobile 2005.

Intermittently, when initialising the subscription on a device, the app cycles through the tables multiple times, or even endlessly.

Can anyone shed some light on why this is happening?

Try to check the Replication Monitor (server side) for status, including number of records submitted. There are some issues with merge replication in the SQL Server 2005 RTM, that have been fixed in SQL Server 2005 SP2.|||

Thanks for the response.

I checked the replication monitor as a matter of course - no problems there, everything looks fine.

Interesting what you say about SP2 - the box in question is running RTM, so maybe updating it will help. Is there anywhere that goes into detail on what these issues that you mention were, and if/how they have been addressed in SP2?

|||

There are various merge/SQL Compact fixes, all documented at: http://support.microsoft.com/default.aspx?scid=kb;en-us;921896
Amongst others:

919949 (http://support.microsoft.com/kb/919949/)

FIX: The sp_MSinitDynamicSubscriber stored procedure is called more than one time by using the same parameters in SQL Server 2005

|||

Erik,

Thanks for that. I am recommending to my client that they upgrade to SP2, and will post back here with the results.

|||

My client is hoping to install SP2 in a couple of weeks. In the meantime, they applied the hotfix, but the problem still exists.

They applied the hotfix to the server running the instance of SQL 2005, but not to the machine with the SQL Mobile server tools (and SQL 2005 client components). Is this correct?

I am hoping that, despite the failure of the hotfix to resolve the issue, the full SP2 update will make a difference...

|||

Erik,

SP2 has now been applied to both servers, and the problem is now resolved.

Thanks very much for your help.

|||

We are facing the same problem... so suggested client to apply for Sp2.... still the problem pesrsists,,,,

Any idea as how to resolve it... please let me know if anything need to be changed after applying Sp2...

Waiting for an answer..

|||It is important to apply SP2 on all servers, including IIS (web) servers (replication components)|||

How to apply SQL server 2005 Service pack 2 on IIS web servers...

I really didnt understand this..can u please telll me what is it

|||If you are running IIS on a separate server, you need to update the replciation components to SP2, see this for info http://msdn2.microsoft.com/en-us/library/ms172937.aspx|||

I will update the replication componets on IIS servers, but what abt the "SQLCSA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

I will update the replication componets on IIS servers, but what abt the "SQLCESA30.dll" contained in the virtual directory... HOw to update this dll with Service pack 2....

Please correct me if i am using the wrong concepts.. I am new to these concepts..

|||

Thank You Erik...

My Problem solved after applying SP2

GOKI

Replication problem

Hi folks; i have added a column into a published table using SP_REPLADDCOLUMN.
I get the following message:
Warning: The table 'MY_TABLE' has been created but its maximum row size (8814) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Warning: The table 'aonflict_MBDB_PUBLICATION_PTL_PATIENT_CLAIM' has been created but its maximum row size (10801) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

MY_TABLE had 40 columns.
Is there any maximum limit of allowed columns in a published table?
Can anyone explain what are the concequences!I dont think the message you are receiving is related to replication

The 8060 byte limit is a universal limit for the data size of a row. It relates to the maximum amount of data that can be stored on a page.

Replication performing UPDATES as INSERTS and DELETES on the subscriber

I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.

Why does it do this?

How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?

Thanks

Cut paste from a different thread, see if this helps:

>> If you can limit the update to unique columns as singleton (each update affects one row) , SQL2000 will replicate it as update. Otherwise for multi-row updates to unique columns you may consider replication of ‘proc exec’ instead.

>> You should also look at the parameter @.schema_option for sp_addarticle. There’s an option that marks FKs at the subscriber as Not For Replication, this may be what you’re looking for. Enabling this should work if you don’t have any other fancy business-triggers at the subscriber that are breaking due to the delete/insert.

|||

What's happening is called a deferred update. See kb 238254 for more info. This is a by design behavior.

Is this causing any problems in your scenario? Are subscriber-side triggers breaking? Are you getting referential integrity errors?

Two common workarounds are to replicate the update via proc exec, or enable schema option 0x20000 in sp_addarticle, which does not enforce RI constraints if raised by replication agent.

Replication on Backup

Hey all...here s my problem, let s see if u can help me please ! :
I have a DB with some tables published in X server, i made a backup of that
database, and restore that backup on another server ( Y ) , but it s still
beeing locked for the replication service, but the Y server doesnt have that
service active... so.. when i want to alter a table, i had the "YOU CANT
DROP THE TABLE BECAUSE IT S BEEING USING FOR REPLICATION.. " ( or something
like that ) .. how can i drop all suscriptions and publications services
from that database ?
tnx!
Try sp_removedbreplication.
If this doesn't remove everything you want, there is a stored procedure to
do this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanx Paul !! .. so, after that, i have to delete rowID fields and i have
the original DB right?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uU6$xoqGFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Try sp_removedbreplication.
> If this doesn't remove everything you want, there is a stored procedure to
> do this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Is that a Enterprise Manager's BUG ?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uU6$xoqGFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Try sp_removedbreplication.
> If this doesn't remove everything you want, there is a stored procedure to
> do this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Do you mean the RowGuids? They'll only be there if you're using some sort of
updating subscriber or merge replication. They'll need removing manually,
alson with any associated check constraints if you have automatic range
management set up.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I don't know if I'd call it a bug as such, just a little messy perhaps.
Several things may be left behind - check constraints, rowguids, conflict
tables etc. In some cases there may be user code that depends on these
objects eg the extra replication guid columns, in which case always
automatically removing them would be a disaster. Anyway, there are some
procedures knocking around which'll remove these extra bits (Hilary has
posted up a version on tis newsgroup a few times). Also, you'll like SQL
Server 2005 - there is the option to do a 'hard' version of
sp_removedbreplication which automatically removes all this replication
objects.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication of a view

Is it possible to replicate a view, and have it published to a table instead of a view?I am not sure exactly what you mean.

Do you want the records in the view to be appended to an existing table or do you want to create a new table?

TO append use INSERT INTO

To create a new table from the view:

Inserting Rows Using SELECT INTO
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list, for example:

SELECT Shippers.*, Link.Address, Link.City,
Link.Region, Link.PostalCode
INTO NewShippers
FROM Shippers
JOIN LinkServer.DB.dbo.Shippers AS Link
ON (Shippers.ShipperID = Link.ShipperID)

SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table containing data selected from a linked server.

Replication Monitor

experiencing some very odd behavior in Enterprise Manager.
I have a process that performs the following:
1.) Restores a published database from Server A onto Server B. The
databases
are identical and they are both published.
2.) I run a script that then deletes the subscriptions to the
publication on
Server B. I do this via SP_DROPSUBSCRIPTION.
3.) I run a script that then deletes the publication. I do this via
SP_DROPPUBLICATION.
4.) Deletes the distribution database.
5.) Deletes the old jobs.
6.) Creates the publication from pregenerated scripts.
7.) Adds the subscriber.
8.) Adds the subscription.
9.) Generates the snapshot and applies it.
What's happening is that I'm seeing the job name that's assigned to the
distribution job increment by 1 each time this process is run. Also,
I'm seeing ten instances of the publication showing up in the tree view.
When I look in SYSPUBLICATIONS, there is only ONE row!!! Shouldn't
Replication Monitor clean itself up?
Everything appears to be rtunning just fine, transactions being
replicated, etc. I'm just wondering is there something I'm missing in my
steps.
Plus, after two or three months of this, might not look so good having 90
publications showing up in the tree view.
Thanks!
first off run a sp_MSload_replication_status. This can occasionally correct
these problems. Secondly, it looks like your replication metadata is messed
up. It is normal for the publications to increment their names with every
new publication. However, to determine how to fix your error I need to know
which node you are getting the error in?
The Publication node in the replication folder? The Publishers node in the
Replication Monitor folder?
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
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:FE4B9FD8-5948-45AF-A3F8-8F9867CFBC23@.microsoft.com...
> experiencing some very odd behavior in Enterprise Manager.
>
> I have a process that performs the following:
>
> 1.) Restores a published database from Server A onto Server B. The
> databases
> are identical and they are both published.
> 2.) I run a script that then deletes the subscriptions to the
> publication on
> Server B. I do this via SP_DROPSUBSCRIPTION.
> 3.) I run a script that then deletes the publication. I do this via
> SP_DROPPUBLICATION.
> 4.) Deletes the distribution database.
> 5.) Deletes the old jobs.
> 6.) Creates the publication from pregenerated scripts.
> 7.) Adds the subscriber.
> 8.) Adds the subscription.
> 9.) Generates the snapshot and applies it.
>
> What's happening is that I'm seeing the job name that's assigned to the
> distribution job increment by 1 each time this process is run. Also,
> I'm seeing ten instances of the publication showing up in the tree view.
> When I look in SYSPUBLICATIONS, there is only ONE row!!! Shouldn't
> Replication Monitor clean itself up?
> Everything appears to be rtunning just fine, transactions being
> replicated, etc. I'm just wondering is there something I'm missing in my
> steps.
> Plus, after two or three months of this, might not look so good having 90
> publications showing up in the tree view.
>
> Thanks!
>
>
|||Hilary:
I'm seeing this in the publishers node under Replication Monitor...
When would I run the sp_MSload_Replication_Status? I just ran it and it
killed my replication. I'm guessing you don't want to run that proc after
replication has been established...
Thanks!
"Hilary Cotter" wrote:

> first off run a sp_MSload_replication_status. This can occasionally correct
> these problems. Secondly, it looks like your replication metadata is messed
> up. It is normal for the publications to increment their names with every
> new publication. However, to determine how to fix your error I need to know
> which node you are getting the error in?
> The Publication node in the replication folder? The Publishers node in the
> Replication Monitor folder?
> --
> 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
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:FE4B9FD8-5948-45AF-A3F8-8F9867CFBC23@.microsoft.com...
>
>
|||What do you mean by it "killed" off replication? I should fix transient
errors. Are you saying that some agents are no longer showing up in the
snapshot/distribution/log reader agent folders?
If so, I think this is caused by scripting out replication and running the
same script in a different database without modifying the snapshot, log
reader, or distribution agent names, and without modifying the publication
name. I think you will find that the agents just don't show up, but
everything works fine.
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
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:B69FA4F3-EFA7-40B2-A709-9B08335CE455@.microsoft.com...[vbcol=seagreen]
> Hilary:
> I'm seeing this in the publishers node under Replication Monitor...
> When would I run the sp_MSload_Replication_Status? I just ran it and it
> killed my replication. I'm guessing you don't want to run that proc after
> replication has been established...
> Thanks!
> "Hilary Cotter" wrote:
correct[vbcol=seagreen]
messed[vbcol=seagreen]
every[vbcol=seagreen]
know[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
view.[vbcol=seagreen]
my[vbcol=seagreen]
90[vbcol=seagreen]
|||I stand corrected...everything came back.
But I'm still seeing the multiple publications under the publishers tab of
the replication monitor...
"Hilary Cotter" wrote:

> What do you mean by it "killed" off replication? I should fix transient
> errors. Are you saying that some agents are no longer showing up in the
> snapshot/distribution/log reader agent folders?
> If so, I think this is caused by scripting out replication and running the
> same script in a different database without modifying the snapshot, log
> reader, or distribution agent names, and without modifying the publication
> name. I think you will find that the agents just don't show up, but
> everything works fine.
> --
> 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
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:B69FA4F3-EFA7-40B2-A709-9B08335CE455@.microsoft.com...
> correct
> messed
> every
> know
> the
> the
> view.
> my
> 90
>
>
|||...also, the scripts that are being used to recreate the replication topology
were generated on the Server B - so the agent names, databases, subscription
names are all correct for that particular server...
"A. Robinson" wrote:
[vbcol=seagreen]
> I stand corrected...everything came back.
> But I'm still seeing the multiple publications under the publishers tab of
> the replication monitor...
> "Hilary Cotter" wrote:

Replication Monitor

I'm experiencing some very odd behavior in Enterprise Manager.

I have a process that performs the following:

1.) Restores a published database from Server A onto Server B. The
databases
are identical and they are both published.
2.) I run a script that then deletes the subscriptions to the
publication on
Server B. I do this via SP_DROPSUBSCRIPTION.
3.) I run a script that then deletes the publication. I do this via
SP_DROPPUBLICATION.
4.) Deletes the distribution database.
5.) Deletes the old jobs.
6.) Creates the publication from pregenerated scripts.
7.) Adds the subscriber.
8.) Adds the subscription.
9.) Generates the snapshot and applies it.

What's happening is that I'm seeing the job name that's assigned to the

distribution job increment by 1 each time this process is run. Also,
I'm
seeing ten instances of the publication showing up in the tree view.

Everything appears to be rtunning just fine, transactions being
replicated,
etc. I'm just wondering is there something I'm missing in my steps.
Plus,
after two or three months of this, might not look so good having 90
publications showing up in the tree view.

Thanks!You might get a better response in
microsoft.public.sqlserver.replication

Simon

replication insert from subscriber

:confused:

I want to call a stored procedure to do the insert into my published arcticle/table. Is this possible? I know you can call a stored procedure to insert into the subscriber arctiles.

The problem is the published database has a generated primary key (max +1 )that is calculated via a stored procedure during all inserts. I would bring the stored procedure over to the subscriber but that would not help since the values in the target table will not be 'current' because the subscriber database is only updated nightly.

Thoughts?What replication is it? Snapshot, Trx, Merge? What type? Push, Pull, Push RAA? Be more specific and someone will help.|||I am using merge replication with 'push'|||Manually generated PK based on INT datatype is not a good idea in merge replication scenario. For that you need GUID or a combination of INT and another field that would uniquely distinguish the physical location.|||Hi all!
I have just worked with Replication data, i can replication data between two server in local by program (script, VB) but i can't do that with a server in local and server on internet. Can you send me source (script, VB) do that?. Thanks!