Access 2000 merge repl problems

Hello,,
for three days i am trying to figure out howto get replication with merge
between sql server en access2K.
Transactional replication is working, but I need a configuration which is
independant of the network, so I need the Merge.
I find all kind of descriptions on the net, but none of them are a
straight-forward solution for the Merge.
Sometimes i get problems with authorization, other times the original MDB
is renamed to <original>_old.mdb or the column names cannot be found (?
)
Please help , i am getting desparate of this.
Any help : very welcome
thanks,
roel
I believe when you are replicating to an access database over the network
you get the renaming phenomena you have experieinced.
Can you also check out this kb article to see if it explains your problem
with missing column names?
http://support.microsoft.com/default...b;en-us;299314
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"roel otten" <roelo@.xs4all.nl> wrote in message
news:41d02b36$0$1580$e4fe514c@.dreader19.news.xs4al l.nl...
> Hello,,
> for three days i am trying to figure out howto get replication with merge
> between sql server en access2K.
> Transactional replication is working, but I need a configuration which is
> independant of the network, so I need the Merge.
> I find all kind of descriptions on the net, but none of them are a
> straight-forward solution for the Merge.
> Sometimes i get problems with authorization, other times the original MDB
> is renamed to <original>_old.mdb or the column names cannot be found (?
> )
> Please help , i am getting desparate of this.
> Any help : very welcome
> thanks,
> roel
>
|||Hello Hillary
thanks for your reaction
problems stay the same :
i have sql server 2k sp3
simple table1 : myid = unique identifier
text = text
i have empty c:\rep.mdb
- i make mydb as oledb / jet4.0 linked served
- i make push subscription
- snapshot starts working, is fine
- merge agent starts:
rep.mdb -> rep_old.mdb
table1 is added
error messages : could not find column MYID
In rep.mdb is table1 with MYID column, text and three added columns for
replication
also some extra tables for replicatin
al these replication items stay empty
after some time (?) the rep_old.mdb is gone and a rep.mdb is created with the
table1 snapshot.
Only changes made in Sql Server survive.
I also checked the microsft solution to change the msrpjt40.dll to the
version they say , but is is older than
the one i have ( i have version 4.10.6912.0 and they recommend 4.10.4320.0 )
any clue ?
everything is welcome
roel otten
In article <O41GyRD7EHA.2592@.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
says...
>
>I believe when you are replicating to an access database over the network
>you get the renaming phenomena you have experieinced.
>Can you also check out this kb article to see if it explains your problem
>with missing column names?
>http://support.microsoft.com/default...b;en-us;299314
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>"roel otten" <roelo@.xs4all.nl> wrote in message
>news:41d02b36$0$1580$e4fe514c@.dreader19.news.xs4a ll.nl...
>
|||any way you could post the schema for the tables you are replicating here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"roel otten" <roelo@.xs4all.nl> wrote in message
news:41d14264$0$8497$e4fe514c@.dreader16.news.xs4al l.nl...
> Hello Hillary
> thanks for your reaction
> problems stay the same :
> i have sql server 2k sp3
> simple table1 : myid = unique identifier
> text = text
> i have empty c:\rep.mdb
>
> - i make mydb as oledb / jet4.0 linked served
> - i make push subscription
> - snapshot starts working, is fine
> - merge agent starts:
> rep.mdb -> rep_old.mdb
> table1 is added
> error messages : could not find column MYID
> In rep.mdb is table1 with MYID column, text and three added columns for
> replication
> also some extra tables for replicatin
> al these replication items stay empty
> after some time (?) the rep_old.mdb is gone and a rep.mdb is created with
the
> table1 snapshot.
> Only changes made in Sql Server survive.
>
> I also checked the microsft solution to change the msrpjt40.dll to the
> version they say , but is is older than
> the one i have ( i have version 4.10.6912.0 and they recommend
4.10.4320.0 )
>
> any clue ?
> everything is welcome
> roel otten
>
> In article <O41GyRD7EHA.2592@.TK2MSFTNGP09.phx.gbl>,
hilary.cotter@.gmail.com[vbcol=seagreen]
> says...
merge[vbcol=seagreen]
is[vbcol=seagreen]
MDB[vbcol=seagreen]
(?
>
|||Hi Hilary
Here is the scripting of the table i want to merge to Access :
CREATE TABLE [dbo].[Table1] (
[MyId] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Tekst] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
I realize thta there is no 'Not for replication' item with the
unique identifier, is it not added by the wizards ?
regards,
roel
In article <ePs1EwO7EHA.2156@.TK2MSFTNGP10.phx.gbl>, hilary.cotter@.gmail.com
says...
>
>any way you could post the schema for the tables you are replicating here?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>"roel otten" <roelo@.xs4all.nl> wrote in message
>news:41d14264$0$8497$e4fe514c@.dreader16.news.xs4a ll.nl...
>the
>4.10.4320.0 )
>hilary.cotter@.gmail.com
>merge
>is
>MDB
>(?
>
|||you will have to change table1 to look like this:
CREATE TABLE [dbo].[Table1] (
[MyId] [uniqueidentifier] NOT NULL ,
[Tekst] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"roel otten" <roelo@.xs4all.nl> wrote in message
news:41d17dc3$0$30525$e4fe514c@.dreader20.news.xs4a ll.nl...
> Hi Hilary
> Here is the scripting of the table i want to merge to Access :
> CREATE TABLE [dbo].[Table1] (
> [MyId] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [Tekst] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
>
> I realize thta there is no 'Not for replication' item with the
> unique identifier, is it not added by the wizards ?
> regards,
> roel
>
>
> In article <ePs1EwO7EHA.2156@.TK2MSFTNGP10.phx.gbl>,
hilary.cotter@.gmail.com[vbcol=seagreen]
> says...
here?[vbcol=seagreen]
with[vbcol=seagreen]
network[vbcol=seagreen]
problem[vbcol=seagreen]
which[vbcol=seagreen]
original
>

Access - SQL Replication

Hi,
I have an Access database that has been replicated over an intranet.
It is a fairly simple database - it has people working on it online and people who work offline and sync into it.
Now we need to sync over the internet.
I enquired in these discussion groups and was adviced to Upsize my database in order to be able to use MS SQL server hosting it at my ISP.
I have upsized the access database from within access 2000.
What do I do next ? How do I put that on to my ISP MS Sql Server?
Do I need to reformat stuff.
I am completely new to sql server. I have programmed MySql and PHP though.
CHEERS
Paula,
I'd advise you to set up replication internally first. There are plenty of
details on the replication setup routines in BOL, but if you describe the
articles involved and if the subscribers are updatable etc - the general
business case - then I'd be better placed to recommend a particlular setup.
Once you have it working internally and understand the security setup and
roles of the various agents then you can contact a hosting provider. There
will be a lot of configuration questions with the provider and it would
probably be much easier if you host it locally, but there are providers who
do offer the service. However, when I looked into it for a recent poster,
the hosting providers seemed to only offer hosting as a subscriber, for
security purposes (eg http://www.1stchoiceinternational.com).
HTH,
Paul Ibison
|||If I am going to implement it internally - would then users conect to the central DB through FTP you mean?
Sorry my english is not good.
Can I just do this with Access Replication then?
"Paul Ibison" wrote:

> Paula,
> I'd advise you to set up replication internally first. There are plenty of
> details on the replication setup routines in BOL, but if you describe the
> articles involved and if the subscribers are updatable etc - the general
> business case - then I'd be better placed to recommend a particlular setup.
> Once you have it working internally and understand the security setup and
> roles of the various agents then you can contact a hosting provider. There
> will be a lot of configuration questions with the provider and it would
> probably be much easier if you host it locally, but there are providers who
> do offer the service. However, when I looked into it for a recent poster,
> the hosting providers seemed to only offer hosting as a subscriber, for
> security purposes (eg http://www.1stchoiceinternational.com).
> HTH,
> Paul Ibison
>
>
|||Paula,
if it's entirely internal, then there's no need for FTP. FTP is used for the
initiation process when replication is carried out across the internet or
non-trusted domains. If you're all on the same domain, then the normal
fileshare method of initialization is fine.
I am not familiar with Access replication so if you need advice on how it is
set up you're best bet is to post in the dedicated access group.
HTH,
Paul Ibison

about transactional replication

Hi,
I have created a transactional replication on SQL Server 2000 with SP3. Only
one database is being published with 70 tables.
But when it begins to replicate data the following errors occured
""The process could not bulk copy into table 'tablename' ""
Violation of PRIMARY KEY constraint 'tablename_PKEY'. Cannot insert
duplicate key in object 'tablename'.
This table has a PKEY and it's not identity and this table also related with
other tables
This question is asked before but i have still no idea to solve this problem
without deleting the relationships of that table.
Please tell the solution step by step,
Best regards...
its hard to say what is going on here. There are a couple of possibilities:
1) data already exists at the subscriber and you are not deleting it before
sending over a new snapshot.
2) you are replicating two or more articles to the same object on the
subscriber.
3) you are getting a different error message than the one posted.
Are you replicating the DRI of these tables, or are you replicating to a
database where the schema already exists? If so you will need to send a
pre-snapshot command which disables all DRI, and a post snapshot command
which re-enables it.
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
"Ugur Ekinci" <ugur@.argeset.com> wrote in message
news:OUdrkjtKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have created a transactional replication on SQL Server 2000 with SP3.
Only
> one database is being published with 70 tables.
> But when it begins to replicate data the following errors occured
> ""The process could not bulk copy into table 'tablename' ""
> Violation of PRIMARY KEY constraint 'tablename_PKEY'. Cannot insert
> duplicate key in object 'tablename'.
> This table has a PKEY and it's not identity and this table also related
with
> other tables
> This question is asked before but i have still no idea to solve this
problem
> without deleting the relationships of that table.
> Please tell the solution step by step,
> Best regards...
>
|||Hi Mr. Hilary,
Yes data already exists at the subscriber, how can i provide to delete
record before replicate it ?
how can I disable DRI and reenable it after replicaiton finished ?
It would be great if you give an example,
Best regards...
"Hilary Cotter" <hilary.cotter@.gmail.com>, haber iletisinde unlar
yazd:ulKrEeuKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> its hard to say what is going on here. There are a couple of
> possibilities:
> 1) data already exists at the subscriber and you are not deleting it
> before
> sending over a new snapshot.
> 2) you are replicating two or more articles to the same object on the
> subscriber.
> 3) you are getting a different error message than the one posted.
> Are you replicating the DRI of these tables, or are you replicating to a
> database where the schema already exists? If so you will need to send a
> pre-snapshot command which disables all DRI, and a post snapshot command
> which re-enables it.
> --
> 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
> "Ugur Ekinci" <ugur@.argeset.com> wrote in message
> news:OUdrkjtKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> Only
> with
> problem
>
|||It's ok, thanks Mr. Hilary,
Since i checked the "delete data in the existing table..." from publication
properties , that error does not occur ,
But I need to know that can we disable DRI with SP_ commands and enable it
again ?
Best regards...
"Hilary Cotter" <hilary.cotter@.gmail.com>, haber iletisinde unlar
yazd:ulKrEeuKFHA.3616@.TK2MSFTNGP09.phx.gbl...
> its hard to say what is going on here. There are a couple of
> possibilities:
> 1) data already exists at the subscriber and you are not deleting it
> before
> sending over a new snapshot.
> 2) you are replicating two or more articles to the same object on the
> subscriber.
> 3) you are getting a different error message than the one posted.
> Are you replicating the DRI of these tables, or are you replicating to a
> database where the schema already exists? If so you will need to send a
> pre-snapshot command which disables all DRI, and a post snapshot command
> which re-enables it.
> --
> 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
> "Ugur Ekinci" <ugur@.argeset.com> wrote in message
> news:OUdrkjtKFHA.2688@.TK2MSFTNGP15.phx.gbl...
> Only
> with
> problem
>

about transaction replication...

Hi everybody,
I am new to SQL replication , I use MS SQL 2000 with
SP3 . I use databases of a financial program which has 360 tables. But I
receive that error when replicating data :
Could not drop object 'dbo.TBLSUBELER' because it is referenced by a FOREIGN
KEY constraint.
(Source: UGUR (Data source); Error number: 3726)
Why this error occurs and what can i do ?
Best regards...
This error is caused because sysdepends is out of sync on your publisher.
All RDBMS's have this problem.
The best thing to do is to pre create your schema, save it to a script and
execute it as part of a pre snapshot command.
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
"Uur Ekinci" <ugur@.argeset.com> wrote in message
news:uHgOmTbHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> I am new to SQL replication , I use MS SQL 2000 with
> SP3 . I use databases of a financial program which has 360 tables. But I
> receive that error when replicating data :
> Could not drop object 'dbo.TBLSUBELER' because it is referenced by a
FOREIGN
> KEY constraint.
> (Source: UGUR (Data source); Error number: 3726)
> Why this error occurs and what can i do ?
> Best regards...
>
|||At what stage during the replication process are you getting this error?
This error is occurring because the specified table is being deleted, but
another table has a foreign key defined on it.
You can get this problem when re-applying the snapshot or running the agent
after the subscription has been reinitialised (which is also re-applying the
snapshot incidentally).
Raj Moloye
"Uur Ekinci" <ugur@.argeset.com> wrote in message
news:uHgOmTbHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> I am new to SQL replication , I use MS SQL 2000 with
> SP3 . I use databases of a financial program which has 360 tables. But I
> receive that error when replicating data :
> Could not drop object 'dbo.TBLSUBELER' because it is referenced by a
> FOREIGN KEY constraint.
> (Source: UGUR (Data source); Error number: 3726)
> Why this error occurs and what can i do ?
> Best regards...
>

about transaction replication initialization

Hi Guys,
In transaction replication, before seting up the replication, do we need to
keep same copy for Publisher and Subscriber. That is mean do we need to
create all objects in Subscriber database which need to replicate from
Publisher in advance or I don't need to create and init snap shot will create
those objects in Subscriber. for example, if I want to replicate a table, do
I neet to create this table in Subscriber ahead? Or init snap shot agent
will do that. Thanks.
By default, there is no requirement to create anything on the subscriber. It
is sometimes beneficial to do a nosync initialization, in which case what
you are saying is pertinent, but this is usually because the snapshot files
are so large and/or the data already exists onthe subscriber so we take
advantage of it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thank you very much. But I have other question is: I have test replication
environment, where it was worked fine. But after I delete one table in
subscriber, it causes error that the table in subscriber is not existed. why
it was not created again automately? Thanks.
"Paul Ibison" wrote:

> By default, there is no requirement to create anything on the subscriber. It
> is sometimes beneficial to do a nosync initialization, in which case what
> you are saying is pertinent, but this is usually because the snapshot files
> are so large and/or the data already exists onthe subscriber so we take
> advantage of it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||It isn't really designed for you to be editing the subscriber's schema in
this way. To reinitialize this table, drop the subscription to the table
then drop the article. Add the article then add the subscription, start the
snapshot agent and it'll be sent over.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Iter,
Schemas are not maintained but rather are modified when the schema at the
publisher changes.
It is assumed schema changes will not be modified at the subscriber.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> Thank you very much. But I have other question is: I have test
> replication environment, where it was worked fine. But after I delete
> one table in subscriber, it causes error that the table in subscriber
> is not existed. why it was not created again automately? Thanks.
> "Paul Ibison" wrote:
|||Thank you guys. If I want to modify the schemas in the publisher, e.g. add
new columns, how can I make changes in subscriber to make them identical?
Thanks
"Simon Sabin" wrote:

> Hello Iter,
> Schemas are not maintained but rather are modified when the schema at the
> publisher changes.
> It is assumed schema changes will not be modified at the subscriber.
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
>
|||It largely depends on what version of SQL Server you are currently using.
There is also a difference between altering a table's existing column and
adding or dropping columns. For the latter on SQL Server 2000 please take a
look at sp_repladdcolumn/sp_repldropcolumn. For SQL Server 2005 or changing
existing columns please take a look at these articles:
http://www.replicationanswers.com/AddColumn.asp
http://www.replicationanswers.com/AlterSchema2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

About those sp_ sprocs.

I find the replication put many sprocs with sp_ prefix in our database. Do
you think that should be changed? I have been told not to use sp_. See
http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html.
Have never really thought about it, but I suppose the reasoning is that it's
a good way of differentiating these stored procs as system ones. If you are
concerned about the performance issues, then you could raise a connect
case...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

About those sp_ sprocs

I find the replication put many sprocs with sp_ prefix in our database. Do you think that should be changed? I have been told not to use sp_. See http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html.

Are you asking:

1. if you can change the names of the procs that replication added

or

2. the procs that you are creating, to aviod the prefix sp_?

|||

I was asking 2. Namely when DBA configures replication, can we have an option to choose sproc naming convention, and prefix sp_ should be warned / avoided in database other than master?

But if we can change those names later, that would also be nice to have.

|||

So you want a warning when anyone else tries to create a proc with prefix sp_?

You could probably do something using the database triggers.

About retore a database which is not publication database

Hi Guys,
If I want to retore a database which is not publication database into a
replication database and I want to keep replication , how to do? Thanks.
I'm not too sure what you mean by 'restoring a database into another
database'. If you mean you want to restore a backup of a subscriber and use
it as a template for another subscriber, then you're talking about a nosync
initialization; please have a look here:
http://www.replicationanswers.com/NoSyncOn2005.asp
If you're using sql server 2000 you can use this article instead:
http://www.replicationanswers.com/NoSyncInitializations.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Sorry I didn't state clearly. I have database that has replication on it and
it is used for publication. Now I want to restore a database on this
database, but the database copy is not publication database, that is mean
this is not replication database backup copy. But I still want to use
replicaton after restore it. Because my case is after restore this database
using database copy that is not replication database, the replication is
gone, and I have to re-create a replication, make me trouble. How to fix it?
thanks.
"Paul Ibison" wrote:

> I'm not too sure what you mean by 'restoring a database into another
> database'. If you mean you want to restore a backup of a subscriber and use
> it as a template for another subscriber, then you're talking about a nosync
> initialization; please have a look here:
> http://www.replicationanswers.com/NoSyncOn2005.asp
> If you're using sql server 2000 you can use this article instead:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||When you restore the published database with a backup of the database that is
NOT replicated, by design the replication information stored in the system
tables is overwritten. So your replication will be gone.
So before you restore the database, generate a replication script which can
be used to restore your replicaiton environment after you've restored.
You will have to modify the repl script to do a nosync (see Paul's
suggestion) to ensure that the snapshots don't get applied all over again.
HTH
Emaniel
"Iter" wrote:
[vbcol=seagreen]
> Sorry I didn't state clearly. I have database that has replication on it and
> it is used for publication. Now I want to restore a database on this
> database, but the database copy is not publication database, that is mean
> this is not replication database backup copy. But I still want to use
> replicaton after restore it. Because my case is after restore this database
> using database copy that is not replication database, the replication is
> gone, and I have to re-create a replication, make me trouble. How to fix it?
> thanks.
>
> "Paul Ibison" wrote:

About repopulate table

Hi Guys,
I have transaction replication setup in my company, and in subscriber there
are some triggers there but not in publication, which is due to business
logic. Now I want to repopulate table's value from publication to subscrition
completely,but I don't want to rerun the snapshot because it will overrite
the triggers in the subscriber. How can do that? Thanks.
Do a no-sync subscription. I think you will want to change the article
properties so that in the snapshot tab, name conflicts section you select
delete all data in table, disable the trigger on the subscriber, run and
deploy the snapshot and then re-enable the trigger.
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
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:ED7B6F2D-4CF7-477D-983A-1AA3740E5A51@.microsoft.com...
> Hi Guys,
> I have transaction replication setup in my company, and in subscriber
> there
> are some triggers there but not in publication, which is due to business
> logic. Now I want to repopulate table's value from publication to
> subscrition
> completely,but I don't want to rerun the snapshot because it will overrite
> the triggers in the subscriber. How can do that? Thanks.
>

About Replication

Hi all,
I am new to SQL Server. I need to know basic details and how to setup the replication process. If any one find some thing pls. reply me
HenryReplication is a big topic. What is the scenario you are thinking about using it ? The following is a good article that will help you:

article (http://www.developer.com/tech/print.php/757741)

If you can provide more information as to the environment and process and can point you in the right direction.

About replication

Hi :
If I want to do the follow :
I create 2 database in localhost
one is dev_db
one is clone_db
Where dev_db have data in the database, but clone_db is just a data
structure but need to synchronize with dev_db schema.
If I add table in dev_db , clone_db will auto add same table structure from
dev_db.
If I change the column (include add, change data type), clone_db will auto
to change.
If I change the script (both view, stored procedure, view, user define
function and trigger), , clone_db will auto to change.
Base on above situation, can replication do this ?
and how to set ?
Thanks for your help
Tom,
there is a DTS 'Copy Objects task' which allows you to copy the schema
without taking the data. There are also tools (Redgate) which'll allow
similar things. To have this automatic is not possible AFAIK using SQL
Server 2000. In SQL 2005 there are ddl triggers that we could take advantage
of, but in SQL 2000 you'd have to have some sort of proactive polling
solution.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

about re-install replication after I restore database

Hi guys,
I am want to re-create publication after I restore database. because I
didn't setup replcaiton using sp_replicationdboption command, all replication
info lost after I did restoring database. When re-create publication with
same publication name, gave me error like Could not drop publication. A
subscription exists to it. but when I run sp_addpublication_snapshot, gave me
error Object 'Web Policy Publication' does not exist or is not a valid object
for this operation.then I coudn't run add article, because the publication is
not existed. How can I fix it. Thanks.
Its hard to restore a publication database on another server. On the same
server you might be able to restore it using the keep_replication switch and
have everything work fine.
After you restore the database and the publications show up you should try
to script them and then recreate the publication to have everything
consistent.
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
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:BA3600A1-F340-4CE5-B24B-0F34E9B0A60A@.microsoft.com...
> Hi guys,
> I am want to re-create publication after I restore database. because I
> didn't setup replcaiton using sp_replicationdboption command, all
> replication
> info lost after I did restoring database. When re-create publication with
> same publication name, gave me error like Could not drop publication. A
> subscription exists to it. but when I run sp_addpublication_snapshot, gave
> me
> error Object 'Web Policy Publication' does not exist or is not a valid
> object
> for this operation.then I coudn't run add article, because the publication
> is
> not existed. How can I fix it. Thanks.
>

About reinitializing ....

This is to do with SQL 2005 merge replication (HTTPS if it matters)

Is there any difference between marking a subscription for reinitalizing via Snapshot Monitor, and having the subscriber mark it for reinitialization ?

Thanks
Bruce

Probably the same. See if what you're doing is the same as in BOL topic "How to: Reinitialize a Subscription (SQL Server Management Studio)".

about MSRepl_commands Table

Hi,
We're running sql2000 with transactional replication. I found that the
system table distribution.msrepl_commands has over 3 million records. So, I
execute the command "EXEC dbo.sp_MSdistribution_cleanup @.min_distretention =
0, @.max_distretention = 0" manually. As the result, the no. of records still
remain 2.5 million records. Why? I checked with distribution clean jobs was
success everytime. How can I minimize the table size?
Thx!
Ron
are you using anonymous subscriptions? If so this might explain what you are
seeing? Also are your distribution agents running frequently? Are any of
them stopped. The clean up normally happens when they run.
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
"555 Rider" <ronchukw@.yahoo.com> wrote in message
news:utuLdwbhFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We're running sql2000 with transactional replication. I found that the
> system table distribution.msrepl_commands has over 3 million records. So,
> I execute the command "EXEC dbo.sp_MSdistribution_cleanup
> @.min_distretention = 0, @.max_distretention = 0" manually. As the result,
> the no. of records still remain 2.5 million records. Why? I checked with
> distribution clean jobs was success everytime. How can I minimize the
> table size?
> Thx!
> Ron
>
|||Yes! I'm using anonymous subscriptions and clean up every 3 hours. The clean
up jobs seems to work success according to the status of SQLServer Agent.
Any things I can do to minimize the size?
Ron
"Hilary Cotter" <hilary.cotter@.gmail.com> glsD:%23sBgbIchFHA.1252@.TK2MSFTNGP09.phx .gbl...
> are you using anonymous subscriptions? If so this might explain what you
> are seeing? Also are your distribution agents running frequently? Are any
> of them stopped. The clean up normally happens when they run.
> --
> 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
> "555 Rider" <ronchukw@.yahoo.com> wrote in message
> news:utuLdwbhFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||query distribution.dbo.MSdistribution_status
Are most the command delivered or waiting to be delivered? If they are
waiting to be delivered you should look at improving the performance of your
distribution agents, like using pull subscriptions and changing the
PollingInterval to 1,
If the commands are mostly delivered, your problem truly is with them being
cleaned up; try to have the distribution clean up agent run every 10
minutes.
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
"555 Rider" <ronchukw@.yahoo.com> wrote in message
news:ex7unWchFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Yes! I'm using anonymous subscriptions and clean up every 3 hours. The
clean
> up jobs seems to work success according to the status of SQLServer Agent.
> Any things I can do to minimize the size?
> Ron
>
> "Hilary Cotter" <hilary.cotter@.gmail.com>
glsD:%23sBgbIchFHA.1252@.TK2MSFTNGP09.phx .gbl...[vbcol=seagreen]
any[vbcol=seagreen]
So,[vbcol=seagreen]
result,[vbcol=seagreen]
with
>

ABout identity column in replication

Hi Guys,
I read the microsoft article about how to handle the identity column in
replication. There is sentence: " If you are using transactional replication
with the immediate-updating Subscribers option, do not use the IDENTITY NOT
FOR REPLICATION design." That means I can not use the IDENTITY NOT FOR
REPLICATION option when I want to use transactional replication with the
immediate-updating Subscribers options, is that correct? That also means I
cannot created identity column in subscriber, right? If I do need to create
identity column in the subscriber, how can I work around? Thanks
This isn't correct. If you have immediate updating you don't need NFR as the
transaction is applied on the publisher before the subscriber.
You can use NFR and identity columns on the subscriber, however, but it is
not necessary.
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
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:43F4ECD0-EDC3-4932-AF29-C2782E66CEDD@.microsoft.com...
> Hi Guys,
> I read the microsoft article about how to handle the identity column in
> replication. There is sentence: " If you are using transactional
> replication
> with the immediate-updating Subscribers option, do not use the IDENTITY
> NOT
> FOR REPLICATION design." That means I can not use the IDENTITY NOT FOR
> REPLICATION option when I want to use transactional replication with the
> immediate-updating Subscribers options, is that correct? That also means I
> cannot created identity column in subscriber, right? If I do need to
> create
> identity column in the subscriber, how can I work around? Thanks

about foreign key conflicts in merge replication

PLS HELP ME. SOS!
Our customer has two fatctories and a marketing center with long distance.
So we have to syncronize data through sql server replication. Because both
publisher and subscriber can modify data, we use merge replication. But when
we tested, foreign key conflicts occured even we only modified data from
subscriber side.
Pls help me, thanks a lot.
Titanicliu,
in merge replication, it can be the case that parent and child generations
are in separate generation batches.
To avoid the error you could set the foreign key to have the NOT FOR
REPLICATION attribute.
You can also increase the -UploadGenerationsPerBatch and
the -DownloadGenerationsPerBatch parameters to their max of 2000 to 'ensure'
the PK and FK rows are processed together.
HTH,
Paul Ibison
|||Hi,Paul
Thanks for your kindly help. I will try it. Thanks again!
BR,
Titanicliu
"Paul Ibison" wrote:

> Titanicliu,
> in merge replication, it can be the case that parent and child generations
> are in separate generation batches.
> To avoid the error you could set the foreign key to have the NOT FOR
> REPLICATION attribute.
> You can also increase the -UploadGenerationsPerBatch and
> the -DownloadGenerationsPerBatch parameters to their max of 2000 to 'ensure'
> the PK and FK rows are processed together.
> HTH,
> Paul Ibison
>
>

About Design Requirements for Replication across different DBMS's...

you can do snapshot and transactional replication to mysql through an odbc
datasource.
SQLCE only supports merge replication.
Are you trying to replicate from SQLCE to MySQL using merge? This isn't
supported by Microsoft.
Not only do you have connection problems but other problems with database
consistency. You replicate to SQLCE using an isapi extension. Your going to
have to write something in Visual C++ to connect the two database.
Addressing your sync statement.
You can use identity columns instead of GUIDs, MySQL does support identity.
There are a lot of problems with you approach. The main problem is how do
you handle conflicts? You are only replicating new records, but what about
updqated records or inserted records?
Suppose you delete a record on the CE device, then you read records on
MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
device.
I think you are trying to roll your own merge replication. Good luck.
"Sky" <forums@.xact-solutions.removethis.com> wrote in message
news:ehtSzCKGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I've been musing about the requirements of making a CE port of an
> application I wrote so that the data (a classic tasks/project set of
tables)
> can be carried on a CE.
> My questions are... I know that CE and MSDE have a replication feature,
but
> what about replicating to other database systems (MySQL in this case).
> I've been trying to think of what needs to be modified in the schema
design
> to allow this to work correctly.
> My first obversations are that
> a) Autoincrement ID columns are probably a dead-end as they will get out
of
> sequence if they are generated on both the CE and the desktop. Therefore a
> GUID Primary Key column seems to be more in order.
> b) The tables all need some form of LastEdited datetime column
> c) I believe that in NET you can connect to two different providers and
the
> following would work (real rough proxycode -- I never can write INNER JOIN
> statements on the fly without trial and error, but here goes):
> Step 1: Get New records on Server:
> SELECT ServerConnection.MyTable.GUID
> FROM ServerConn.MyTable,
> ServerConn.MyTable.GUID
> INNER JOIN
> ON CEConn.MyTable.GUID
> WHERE CEConn.MyTable.GUID=NULL
> These we write to the CE client.
> Step 2: Reverse Step 1 to write any NEW records on CE to Server
> Step 3: Then get any records on server that were edited later than the
CE's:
> SELECT ServerConnection.MyTable.GUID
> FROM ServerConn.MyTable, CEConn.MyTable
> WHERE ServerConn.MyTable.GUID=CEConn.MyTable.GUID
> AND (ServerConn.MyTable.LastEdited > CEConn.MyTable.LastEdited)
> Step 4: Reverse the process to write any new records from the CE to the
> server.
>
> I think that about covers it...Any changes made on the CE done at a
earlier
> date than changes also done on the server are crushed by the Server who
has
> higher priority...
>
> What I would like to know is whether anybody has dealt with this kind of
> mess before and can give pointers on how to do it.
> For example -- does anybody have a fair idea of how Palm Pilot sychronizes
> their records?
> Is there an ODBC schema field that exists per row, cross-platform, that is
> the equivalent of DateLastModified so that I don't have to add it to each
> table?
> As you can see -- I'm at the preliminary stages of this, and any advice,
> tips, articles, code samples that you can point me to would be greatly
> appreciated.
> Thank you!
>
>
Thanks for the heads up about the Deleted records. Hum.
Obviously due to your statement
<<<Are you trying to replicate from SQLCE to MySQL using merge? This isn't
supported by Microsoft.>>
I am going to have to roll my own...which I agree is not going to be easy.
But must. I can't stand it anymore not being able to synch between different
DBMS's and want to find out where I can find a good article/code example as
to how someone goes about doing it.
It has been done before -- eg: Palm, Outlook CE, etc.
So it's not impossible. Just need help to get started.
For example -- once again, stabbing in the dark -- maybe a sys table
needs to be created called Deleted
GUID : TableName : ForeignGUID : DateDeleted
So that when a Delete FROM 'task' WHERE ID='...' one has to log it
in the Deleted table...
although this works only when deleting records one at a time (would get out
of whack if the statement were simply DELETE FROM task where Subject='xxx'.)
Or all delete statements in the program have to be modified to delete the
fields in three steps (get the ID of what you want to delete first, write
those to the deleted table, then delete the records...)
Not easy. At all.
;-(
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OI%236$fLGEHA.2980@.TK2MSFTNGP09.phx.gbl...
> you can do snapshot and transactional replication to mysql through an odbc
> datasource.
> SQLCE only supports merge replication.
> Are you trying to replicate from SQLCE to MySQL using merge? This isn't
> supported by Microsoft.
> Not only do you have connection problems but other problems with database
> consistency. You replicate to SQLCE using an isapi extension. Your going
to
> have to write something in Visual C++ to connect the two database.
> Addressing your sync statement.
> You can use identity columns instead of GUIDs, MySQL does support
identity.
> There are a lot of problems with you approach. The main problem is how do
> you handle conflicts? You are only replicating new records, but what about
> updqated records or inserted records?
> Suppose you delete a record on the CE device, then you read records on
> MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
> device.
> I think you are trying to roll your own merge replication. Good luck.
>
> "Sky" <forums@.xact-solutions.removethis.com> wrote in message
> news:ehtSzCKGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> tables)
> but
> design
> of
a
> the
JOIN
> CE's:
> earlier
> has
sychronizes
is
each
>
|||Hi Hilary: addendum...
Just came across some info at:
http://www.xpinneret.com/cwu09-01.html
which was about the Palm pilot, but it did clear up some issues.
I still would need an identity column, a DateLastModified, but I have to add
a byte column.
The palm pilots was simply 4 bytes:
dirty (ie: modified)
secret
locked
deleted
In other words, one simply modifies the SQL statements to "SELECT * from
table where id=... AND deleted=false (I havn't yet figured out how to write
the bit checking part in cross platform sql, but you get the point).
And instead of using a traditional DELETE statement, making it an UPDATE
statement that modifies the bit. Quite easy for that step.
Then we are back to synchronization (the article didn't cover that ;-( ) but
I think I am starting to get a less hazy view of what we're talking about...
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OI%236$fLGEHA.2980@.TK2MSFTNGP09.phx.gbl...
> you can do snapshot and transactional replication to mysql through an odbc
> datasource.
> SQLCE only supports merge replication.
> Are you trying to replicate from SQLCE to MySQL using merge? This isn't
> supported by Microsoft.
> Not only do you have connection problems but other problems with database
> consistency. You replicate to SQLCE using an isapi extension. Your going
to
> have to write something in Visual C++ to connect the two database.
> Addressing your sync statement.
> You can use identity columns instead of GUIDs, MySQL does support
identity.
> There are a lot of problems with you approach. The main problem is how do
> you handle conflicts? You are only replicating new records, but what about
> updqated records or inserted records?
> Suppose you delete a record on the CE device, then you read records on
> MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
> device.
> I think you are trying to roll your own merge replication. Good luck.
>
> "Sky" <forums@.xact-solutions.removethis.com> wrote in message
> news:ehtSzCKGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> tables)
> but
> design
> of
a
> the
JOIN
> CE's:
> earlier
> has
sychronizes
is
each
>