merge replication w PocketPC and trigger order

Hi
We've got a problen in a customer with SQL Server 2000 SP3 and merge
replication with PocketPC
We have some triggers FOR UPDATE on the same field used to distribute in
replication settings, so I suspect sometimes our triggers didn't work, and
sometimes the first trigger that has been executed were the replication
trigger.
Can I use the sp_settriggerorder to set the order to execute, setting the
replication trihgger as last in execution?
Thanks
Yes you can, I normally make them execute first though.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Ricardo Snchez" <Ricardo Snchez@.discussions.microsoft.com> wrote in
message news:2BA27F4E-0454-4DC0-9DC2-46CD14C7E368@.microsoft.com...
> Hi
> We've got a problen in a customer with SQL Server 2000 SP3 and merge
> replication with PocketPC
> We have some triggers FOR UPDATE on the same field used to distribute in
> replication settings, so I suspect sometimes our triggers didn't work, and
> sometimes the first trigger that has been executed were the replication
> trigger.
> Can I use the sp_settriggerorder to set the order to execute, setting the
> replication trihgger as last in execution?
> Thanks
sql

Merge Replication increases the bulk insert time dramatically

You have triggers firing which populate the
MSmerge_contents table. You also have the merge agent
which tries to read this table. So, there are 2
additional features. You can't remove the triggers -
they're mandatory so try disabling the merge agent while
the bulk upload takes place. If this still takes a while,
try the same bulk load on an identical copy of the table
without the triggers - just to confirm that it is the
triggers and not the higher quantity of data that you now
have in the table, as placing rows according to the PK
value will by necessity take longer.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I checked, and we didn't have FIRE_TRIGGERS in our bulk insert commands. The
data was still being replicated, however.
Can you programatically disable the merge agent and then re-enable it during
bulk inserts? If so, can you point me to the right commands?
When we turn off replication, the insertion times remain around 3 seconds.
"Paul Ibison" wrote:

> You have triggers firing which populate the
> MSmerge_contents table. You also have the merge agent
> which tries to read this table. So, there are 2
> additional features. You can't remove the triggers -
> they're mandatory so try disabling the merge agent while
> the bulk upload takes place. If this still takes a while,
> try the same bulk load on an identical copy of the table
> without the triggers - just to confirm that it is the
> triggers and not the higher quantity of data that you now
> have in the table, as placing rows according to the PK
> value will by necessity take longer.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||There's something I don't understand here. When you did
the bulk insert without firing the triggers, how is it
possible that the data could be merged? Perhaps when you
say the data was still being replicated, you mean that
the merge agent was running? Anyway, What are the times:
(a) no replication
(b) replication and no firing of triggers and the merge
agent on a schedule which means it is not currently
running
(c) replication and firing of triggers and the merge
agent on a schedule which means it is not currently
running
(d) replication and firing of triggers and the merge
agent currently running
Rgds,
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul.
I do not understand either. However, the data is on both servers.
Here are some times:
a)No replication: average 1.5 seconds daily
d)Continuous Replication: 3,4,14,29,118 (averages of daily values over 5 days)
I will have to test the other two options.
I understand that adding RGUIDs will increase the time it takes to insert
data, but is an exponential increase expected?
"Paul Ibison" wrote:

> There's something I don't understand here. When you did
> the bulk insert without firing the triggers, how is it
> possible that the data could be merged? Perhaps when you
> say the data was still being replicated, you mean that
> the merge agent was running? Anyway, What are the times:
> (a) no replication
> (b) replication and no firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (c) replication and firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (d) replication and firing of triggers and the merge
> agent currently running
> Rgds,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi Paul,
It turns out our MSmerge_contents table was over 2.5 million records.
And it's not automatically clearing up. We are inserting data (not using
bulk insert) every five minutes, so this is a critical concern for us.
Could this be the problem? How can we tell it to automatically clean up
after data is replicated?
"Paul Ibison" wrote:

> There's something I don't understand here. When you did
> the bulk insert without firing the triggers, how is it
> possible that the data could be merged? Perhaps when you
> say the data was still being replicated, you mean that
> the merge agent was running? Anyway, What are the times:
> (a) no replication
> (b) replication and no firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (c) replication and firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (d) replication and firing of triggers and the merge
> agent currently running
> Rgds,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

merge replication corruption (system triggers and views)

All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:

Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?

-mikeI found another change.. we added a linked server using sp_addlinkedserver.. Any thoughts?|||Does your merge subset filter clauses or join filter clauses contain views that contain WITH CHECK OPTION, pointing to remote table?|||No filters are set for the publication.|||

ok, then you have to trace your steps to see exactly what changes were made that would cause this, and see if you can back them out one by one.

any idea what the linked server would have to do with regards to the views, triggers, or any of the published tables? are you making changes from a remote machine?

Merge replication conflicts on Identity Columns

Hi all,
I've set up a merge replication using sql server 2005.
All settings for "Copy Use Triggers" were set to false
All Identity ranges were set to be handled automatically.
There is a table - "Stock" - which has an ident field. An insert
trigger on this table inserts a new record into another table -
"barcodes" - which itself has its own ident field.
When a new "stock" record is inserted on the subscriber I get a merge
conflict which says:
"A row insert at 'xxx.YYYYYYYYY' could not be propagated to
'zzz.YYYYYYYYY'. This failure can be caused by a constraint violation.
Explicit value must be specified for identity column in table
'barcodes' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION identity
column."
It suggested I use sp_adjustpublisheridentityrange, which I did on
both tables, but still the conflict won't resolve.
Any suggestions will be most gratefully received
Thanks
Ken
This should resolve itself the next time the merge agent runs. It is
possibly that you have entered so much data on the subscriber that you
identity range is completely out of whack.
Issue a dbcc checkident(''tablename') on your subscriber and note the value
for the current identity value and current column value. If the current
identity value is greater than your current column value set the current
identity value to be the current column value, i.e.
dbcc checkident(''tablename',reseed, 14)
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
<kengillett@.gmail.com> wrote in message
news:1184849805.324095.134260@.w3g2000hsg.googlegro ups.com...
> Hi all,
> I've set up a merge replication using sql server 2005.
> All settings for "Copy Use Triggers" were set to false
> All Identity ranges were set to be handled automatically.
> There is a table - "Stock" - which has an ident field. An insert
> trigger on this table inserts a new record into another table -
> "barcodes" - which itself has its own ident field.
> When a new "stock" record is inserted on the subscriber I get a merge
> conflict which says:
> "A row insert at 'xxx.YYYYYYYYY' could not be propagated to
> 'zzz.YYYYYYYYY'. This failure can be caused by a constraint violation.
> Explicit value must be specified for identity column in table
> 'barcodes' either when IDENTITY_INSERT is set to ON or when a
> replication user is inserting into a NOT FOR REPLICATION identity
> column."
> It suggested I use sp_adjustpublisheridentityrange, which I did on
> both tables, but still the conflict won't resolve.
> Any suggestions will be most gratefully received
> Thanks
> Ken
>

Merge replication and triggers?

Hello,
I hope someone can help me answer some questions about replication, to solve
my problems in a project. I'm involved in a webproject which uses a
replicated SQL Server 2000 database. The source database (which is created
by a 3rd party, so maintanance is not in our hands) contains triggers to
perform inserts into
child tables when something is inserted into a main table. Now we used merge
replication to get a copy on the server and keep its data in sync with our
local source db.
The problem is the following:
The installation of the Merge replication is done by another company and
they want to remove the triggers from the web database. They claim that
triggers are a problem for the replication. That creates a problem for our
webapplication since it's based on the logic of the triggers. So if they are
removed, the application
will no longer work properly.
I would like to know if someone has some experience doing something like we
do and if the triggers are a problem in the replication.
I see two solutions:
1. We make the triggers work with replication: problem solved
2. We remove the triggers on the webdatabase and implement their logic in
code: This involves a lot of additional work for us and creates a
maintanance problem when the trigger logic changes. Since that might happen
without notice, the website might stop working at a new release and data
might
become inconsisten. Not a pleasant thought.
I would like to hear how others dealt with this kind of problems
TIA,
Jeroen
In my experience having triggers should not in itself stop replication. The
only proviso to this is occasion the SQL Server parser can get a bit odd when
creating triggers using the replication process, which causes an error, but
one that can be rectified by hand.
As long as the triggers are set to 'Not For Replication' they work correctly.
Can I sugest that you ask for more details and how the triggers are causing
a problem, under what circumstances ect, perhaps they know something about
the database that you do not.
Peter
"Do not awake the sleeping dragon for you are crunchy and taste good with
ketchup".
Peter The Spate
"Jeroen" wrote:

> Hello,
> I hope someone can help me answer some questions about replication, to solve
> my problems in a project. I'm involved in a webproject which uses a
> replicated SQL Server 2000 database. The source database (which is created
> by a 3rd party, so maintanance is not in our hands) contains triggers to
> perform inserts into
> child tables when something is inserted into a main table. Now we used merge
> replication to get a copy on the server and keep its data in sync with our
> local source db.
> The problem is the following:
> The installation of the Merge replication is done by another company and
> they want to remove the triggers from the web database. They claim that
> triggers are a problem for the replication. That creates a problem for our
> webapplication since it's based on the logic of the triggers. So if they are
> removed, the application
> will no longer work properly.
> I would like to know if someone has some experience doing something like we
> do and if the triggers are a problem in the replication.
> I see two solutions:
> 1. We make the triggers work with replication: problem solved
> 2. We remove the triggers on the webdatabase and implement their logic in
> code: This involves a lot of additional work for us and creates a
> maintanance problem when the trigger logic changes. Since that might happen
> without notice, the website might stop working at a new release and data
> might
> become inconsisten. Not a pleasant thought.
> I would like to hear how others dealt with this kind of problems
> TIA,
> Jeroen
>
>
|||Hi Peter,
Thanks for answering. Good to hear that triggers should not be any problem
in itself. I will have to check that the triggers have the 'Not for
replication' option set. At this moment I do not have more details about the
problems that occured while setting up the replication, since another
company did that. I will contact them to see exactly what problems they
encountered while installing it.
But, as I read in your answer, my first solution should be possible if the
triggers have the correct options set, am I correct?
Thanks for your help,
Jeroen
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft. com> wrote in message
news:12FF5C8D-99BC-4929-B791-0EDE4E7A8604@.microsoft.com...[vbcol=seagreen]
> In my experience having triggers should not in itself stop replication.
> The
> only proviso to this is occasion the SQL Server parser can get a bit odd
> when
> creating triggers using the replication process, which causes an error,
> but
> one that can be rectified by hand.
> As long as the triggers are set to 'Not For Replication' they work
> correctly.
> Can I sugest that you ask for more details and how the triggers are
> causing
> a problem, under what circumstances ect, perhaps they know something about
> the database that you do not.
> Peter
> "Do not awake the sleeping dragon for you are crunchy and taste good with
> ketchup".
> Peter The Spate
>
> "Jeroen" wrote:
|||I would ask the other company for more details on what the problem with
replication and triggers is.
Merge replication has problems with triggers only when you update text and
image data type columns (refer to the section on Planning for Merge
Replication n BOL). As Peter had mentioned you might want to use the Not For
Replication clause on user triggers depending on what these triggers do -
For instance for audit triggers you might want them to have them fire if you
want to track changes made by the replication process.
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
"Jeroen" <Noname@.noname.com> wrote in message
news:OcdnLxHJFHA.2576@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I hope someone can help me answer some questions about replication, to
solve
> my problems in a project. I'm involved in a webproject which uses a
> replicated SQL Server 2000 database. The source database (which is created
> by a 3rd party, so maintanance is not in our hands) contains triggers to
> perform inserts into
> child tables when something is inserted into a main table. Now we used
merge
> replication to get a copy on the server and keep its data in sync with our
> local source db.
> The problem is the following:
> The installation of the Merge replication is done by another company and
> they want to remove the triggers from the web database. They claim that
> triggers are a problem for the replication. That creates a problem for our
> webapplication since it's based on the logic of the triggers. So if they
are
> removed, the application
> will no longer work properly.
> I would like to know if someone has some experience doing something like
we
> do and if the triggers are a problem in the replication.
> I see two solutions:
> 1. We make the triggers work with replication: problem solved
> 2. We remove the triggers on the webdatabase and implement their logic in
> code: This involves a lot of additional work for us and creates a
> maintanance problem when the trigger logic changes. Since that might
happen
> without notice, the website might stop working at a new release and data
> might
> become inconsisten. Not a pleasant thought.
> I would like to hear how others dealt with this kind of problems
> TIA,
> Jeroen
>