Merge Repliction - Run Stored Procedure when merge agent starts

I have database on SQL Server 2000 set up with a merge publication.
This publication is configured with a number of dynamic filters to
reduce the amount of data sent to each client. Each client has an
anonymous pull subscription. The merge process can be triggered by the
windows sync manager and my application.

To improve performance I have created some helper tables to hold the
mapping between user login and primary keys of selected entities.

For the replicated data to be correct the contents of the helper tables
needs to be up to date.

I need to fire off a stored procedure on the publisher before
replication starts to verify that this data is up to date. I can not
see any documented way of doing this however I have been experimenting
with some unorthodox systems.

Firstly has anyone any ideas?

I have been considering adding a trigger to some of the tables used by
the Microsoft replication code - yes I know this is very nasty.

My problems arise because executing this stored procedure will cause
some data to be updated. In updating data we could create a new
generation in the database. I must therefore run my stored procedure
before any the Microsoft code makes any generation checks / updates.

Anyone done anything similar, Anyone have any better ideas?

Any comments would be gratefully received.(tedd_n_alex@.yahoo.com) writes:
> My problems arise because executing this stored procedure will cause
> some data to be updated. In updating data we could create a new
> generation in the database. I must therefore run my stored procedure
> before any the Microsoft code makes any generation checks / updates.
> Anyone done anything similar, Anyone have any better ideas?

Not being very good at replication, I can only recommend you to visit
microsoft.public.sqlserver.replication. There are some guys that knows
replicataion really well there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

merge replication without rowguid

hi,
I have a database that has Primary Key constraints on every table. Yet
when I run the replication wizard and try to configure merge
replication the wizard notifies me that it will create a rowguid on
the tables even though they already have a primary key.
anybody know how to avoid the rowguid column from being created?
regards,
Doron
what is the big deal with having rowguid column?
I am running the replication for 3 years with that column without any
prolem!
"DoronA" <doronal@.msn.com> wrote in message
news:OMPe$RgwEHA.824@.TK2MSFTNGP11.phx.gbl...
> hi,
> I have a database that has Primary Key constraints on every table. Yet
> when I run the replication wizard and try to configure merge
> replication the wizard notifies me that it will create a rowguid on
> the tables even though they already have a primary key.
> anybody know how to avoid the rowguid column from being created?
> regards,
> Doron
>
|||If your primary key is a unqueidentifier, then you can mark it as the
RowGuid column using ALTER TABLE, e.g.
ALTER TABLE dbo.MyTable
ALTER COLUMN MyPK ADD ROWGUIDCOL
Otherwise, you have to have a separate RowGuid column, as it's how
replication tracks the global identity of rows.
Paul
"DoronA" <doronal@.msn.com> wrote in message
news:OMPe$RgwEHA.824@.TK2MSFTNGP11t .phx.gbl...
> hi,
> I have a database that has Primary Key constraints on every table. Yet
> when I run the replication wizard and try to configure merge
> replication the wizard notifies me that it will create a rowguid on
> the tables even though they already have a primary key.
> anybody know how to avoid the rowguid column from being created?
> regards,
> Doron
>

merge replication without rowguid

hi,
I have a database that has Primary Key constraints on every table. Yet
when I run the replication wizard and try to configure merge
replication the wizard notifies me that it will create a rowguid on
the tables even though they already have a primary key.
anybody know how to avoid the rowguid column from being created?

regards,
Dorondoron_almog@.msn.com (Doron) wrote in message news:<995517bc.0411031605.30cf6318@.posting.google.com>...
> hi,
> I have a database that has Primary Key constraints on every table. Yet
> when I run the replication wizard and try to configure merge
> replication the wizard notifies me that it will create a rowguid on
> the tables even though they already have a primary key.
> anybody know how to avoid the rowguid column from being created?
> regards,
> Doron

Unless all of the replicas are read-only, how would you prevent the
same PK being allocated to two different rows in two different
replicas?

The solution you are being offered is to have rowguid be the primary
key - what's your proposed solution to the problem?

Merge Replication when connecting from Mobile Device

Hi,
I'm trying to set up a merge replication application on a production server
and I have run into problems.
I'm trying to use SQL server Mobile to connect to a SQL Server 2000
publisher but I get errors when I try to syncronize.
I've tested the application in my development enviroment where the security
settings are much more open ant there everything works fine so I suspect the
error is connected to the higher security in the production enviroment.
I've setup Merge replication on the server box and configured IIS and when I
test the connection to the server agent from IE (both from my desktop and
mobile device) by using the URL:
https://myserver/myvirualdirectory/sqlcesa30.dll and supply my credentials I
get the "agent ok" message.
The problem occurs when I try to syncronize my mobile database to the
publication. I get the following error message:
A request to send data to the computer running IIS has failed. For more
information, see HRESULT.
When I check the HRESULT value in the exception it is: -2146233087
What does this error message mean?
How can I parse this value?
Thank you for any suggestions
Regards
Henrik
That error message is 2146233087 (80131501)': No server registered for
CLSID. It sounds like one of your replication components or replisapi dll's
is not registered correctly.
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
"Henrik" <henrik_@.community.nospam> wrote in message
news:C5F499A3-8965-4D80-AA3B-EB9CFD79861A@.microsoft.com...
> Hi,
> I'm trying to set up a merge replication application on a production
> server
> and I have run into problems.
> I'm trying to use SQL server Mobile to connect to a SQL Server 2000
> publisher but I get errors when I try to syncronize.
> I've tested the application in my development enviroment where the
> security
> settings are much more open ant there everything works fine so I suspect
> the
> error is connected to the higher security in the production enviroment.
> I've setup Merge replication on the server box and configured IIS and when
> I
> test the connection to the server agent from IE (both from my desktop and
> mobile device) by using the URL:
> https://myserver/myvirualdirectory/sqlcesa30.dll and supply my credentials
> I
> get the "agent ok" message.
> The problem occurs when I try to syncronize my mobile database to the
> publication. I get the following error message:
> A request to send data to the computer running IIS has failed. For more
> information, see HRESULT.
> When I check the HRESULT value in the exception it is: -2146233087
> What does this error message mean?
> How can I parse this value?
> Thank you for any suggestions
> Regards
> Henrik
|||Thank you for your reply
Could you please explain further what you mean. Is there a problem with the
installation of the different tools?
Where can I find a list of error messages to map a HRESULT to an error
description?
Thank you
/ Henrik
"Hilary Cotter" wrote:

> That error message is 2146233087 (80131501)': No server registered for
> CLSID. It sounds like one of your replication components or replisapi dll's
> is not registered correctly.
> --
> 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
>
> "Henrik" <henrik_@.community.nospam> wrote in message
> news:C5F499A3-8965-4D80-AA3B-EB9CFD79861A@.microsoft.com...
>
>
|||Hello Henrik,
I would like to suggest you to check whether you have use any firewall in
the production server.
842907SQL Server CE 1.0 and later version client programs may not be able
to connect to IIS on computers that are running Windows XP Service Pack 2
(SP2)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;842907
Since this issue is related with SQL Server Mobile which is not supported
in the SQL newsgroup, I would like to suggest you to post it in the
microsoft.public.sqlserver.ce newsgroup.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Thank you for your reply.
I've been trying to find a SQL Server CE newsgroup but there is none!?
Or am I more than semi-blind?
I've searched the list at:
http://msdn2.microsoft.com/en-us/subscriptions/aa974230.aspx
Could you please give me directions
"Wei Lu [MSFT]" wrote:

> Hello Henrik,
> I would like to suggest you to check whether you have use any firewall in
> the production server.
> 842907SQL Server CE 1.0 and later version client programs may not be able
> to connect to IIS on computers that are running Windows XP Service Pack 2
> (SP2)
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;842907
> Since this issue is related with SQL Server Mobile which is not supported
> in the SQL newsgroup, I would like to suggest you to post it in the
> microsoft.public.sqlserver.ce newsgroup.
> Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
|||Hello Henrik,
The SQL CE newsgroup is not a managed newsgroup. You could access from this
url:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlser
ver.ce&lang=en&cr=US
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Merge Replication to multiple servers losing data

Hi,
We are using merge replication between four servers (1 publisher & 3
subscribers). The same articles (tables) are in each publication. I run the
application which changes data in some tables and adds records in another
table. The inserted data is immediately updated via trigger. After running
the application all expected data is present. If I manually force
replication to each subscriber sequentially, all expected data is present.
If I run replication between the servers at the same time, the table to
which data was added will lose some data. The data lost was not the data
that was just added. We are running SQL Server 2000 SP3a on all servers. Any
ideas?
tia,
Paul
Look at the 'view conflict' at replication monitor...
"PaulW" <MSNewsGroup@.Digi-Sol.com>
news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are using merge replication between four servers (1 publisher & 3
> subscribers). The same articles (tables) are in each publication. I run
> the application which changes data in some tables and adds records in
> another table. The inserted data is immediately updated via trigger.
> After running the application all expected data is present. If I manually
> force replication to each subscriber sequentially, all expected data is
> present. If I run replication between the servers at the same time, the
> table to which data was added will lose some data. The data lost was not
> the data that was just added. We are running SQL Server 2000 SP3a on all
> servers. Any ideas?
> tia,
> Paul
>
|||There are no recorded conflicts. This table only has data inserted, then
updated through a trigger. We did view the transaction log. The only entries
with the table were the inserts we initiated followed by a delete/insert for
the trigger update.
Paul
"Grigoris Tsolakidis" <gcholakidis@.spam_remove.hotmail.com> wrote in message
news:uhRQsHGmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Look at the 'view conflict' at replication monitor...
> "PaulW" <MSNewsGroup@.Digi-Sol.com>
> news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
>
sql

merge replication script problem

Hi all:
i set up merge replication with row and column filter in sql 2000 and
it works fine, so i generate the script and try to run this script in
another computer, i alway got errors like "table name does not exist etc",
those tables definately exist in DB.
i worked it out after i delete all column fiters in script, does any
one know what's the problem?
Cheers
nick
could you post your table schema and your publication script?
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
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:euoHn5JCFHA.936@.TK2MSFTNGP12.phx.gbl...
> Hi all:
> i set up merge replication with row and column filter in sql 2000
and
> it works fine, so i generate the script and try to run this script in
> another computer, i alway got errors like "table name does not exist etc",
> those tables definately exist in DB.
> i worked it out after i delete all column fiters in script, does any
> one know what's the problem?
>
> Cheers
> nick
>

Merge Replication Initial Snapshot

SQL 2000.
I have a merge replication system running in a 24/7 environment that
cannot be brought offline or locked up. When i try to run my snapshot
agent, it locks up the primary DB (there are many tables in this that
are very large, like hundreds of millions of rows). is there a way
around the initial snapshot, or a way to speed it up? I have read in
BOL that it may be possible with a backup and removable storage, or
using BCP.
Has anyone been able to get merge replication working without locking
up the db or bringing it offline? If so, how?
Thanks in advance!!
No, AFAIK - there is no way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"colbycrane" <gstarnes@.gmail.com> wrote in message
news:1104268201.081080.154550@.z14g2000cwz.googlegr oups.com...
> SQL 2000.
> I have a merge replication system running in a 24/7 environment that
> cannot be brought offline or locked up. When i try to run my snapshot
> agent, it locks up the primary DB (there are many tables in this that
> are very large, like hundreds of millions of rows). is there a way
> around the initial snapshot, or a way to speed it up? I have read in
> BOL that it may be possible with a backup and removable storage, or
> using BCP.
> Has anyone been able to get merge replication working without locking
> up the db or bringing it offline? If so, how?
> Thanks in advance!!
>
|||Backup and restore can be used for a nosync initialization, but the data
shouldn't be changed on the publisher while this takes place so effectively
it's the same. In transactional replication there is the option to have
concurrent snapshot bgeneration, but this is not possible for merge. So, if
you must use merge, essentially you'll need some system downtime.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||But the problem with a backup restore is that the rowguid column still has
to be added, preferably before the backup is done. This involves an alter
table and locking.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OqLd1pZ7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Backup and restore can be used for a nosync initialization, but the data
> shouldn't be changed on the publisher while this takes place so
> effectively
> it's the same. In transactional replication there is the option to have
> concurrent snapshot bgeneration, but this is not possible for merge. So,
> if
> you must use merge, essentially you'll need some system downtime.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Good point
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||We employ a fairly complicated ETL routine to add rows to tables without
locking the db. This can also be used to migrate the data to the
subscriber db. Heres how it works:
Say tableA needs a new column (tableA has 60m rows). We create TableB,
which is TableA + the new column. We insert / select with (nolock) to
migrate the data. This may introduce dirty reads, thus bad data to
tableB so, at the same time, we put an on insert, update, delete trigger
on table that feeds any changes to TableC. After the inital insert /
select is finished, we run a daemon that takes from TableC and applies
it to tableA. Ultimately, we run the daemon repeatedly until TableC
only has a few records (6 second delay is acceptable), so we then put
into a transaction the final 'reconciliation' to lock tableA, push the
final changes from tableC to tableB, do an sp_rename, and off we go...
If we were able to do this, would the initialization of Merge
replication still read all the rows out of TableA, thus still locking
it?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Greg,
If I read you correctly, in your example, you're saying that you have a
method of making an identical copy of a table without taking a shared lock
on it, or at least the lock used at the final step is minimal and probably
involves a few rows. If you can make such a copy on the subscriber then you
could indeed do a noinit initialization. The guid column with rowguid
attribute could be added using your current methodology. During
initialization, you need to be sure that there are no changes to the data at
the publisher, but this could last as little as 30 secs.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)