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
>

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 Merge Join Component

Hi, I'm using a Merge Join Component of Inner Join type to retrieve from the right pipeline some records to append to the ones coming from left pipeline according to the join citerias defined on the compnent.

Is there any way to know which are the records coming from the left pipeline that doesn't match the join criterias?

In the following I'll try to do an axample.

LF pipeline:

Column0 Column1 Column2

1 aaaa aa11

2 bbbb bb11

3 cccc cc11

4 dddd dd11

RT pipeline:

ColumnA ColumnB

1 aa22

4 dd22

On exiting from the MergeJoin, defining “Column0” for LT as join key and “ColumnA” for RT and as output data all the columns of the LT pipeline and the only ColumnB from the RT pipeline it should be obtained the following records:

Column0 Column1 Column2 ColumnB

1 aaaa aa11 aa22

4 dddd dd11 dd22

and the records from the LT pipeline:

2 bbbb bb11

3 cccc cc11

shouldn't go in the output from the Merge Join Component.

What I need to know is which are these last lines because I need to manage them.

Thanks!

If you need to get both matches and mismatches; shouldn't you be using an left or full outer join instead? then you could place a conditional split to redirect and manage rows that did not match.

BTW, Have you loked at the Looukup transformation instead?

You could have a lookup based on the 2 set of row (assuming is an OLE DB data source) and then use the error output to redirect the rows that don't match. That way you get 2 outputs; the matches and the mismatches.

|||

Hi,

I read your answer and I think I need more details about it.

When you say:

“….shouldn't you be using an left or full outer join instead?”

I tell you:

If I use a left outer join, on output I have both matches and mismatches records. Can you tell me on what I have to do the conditional split? Can you show me any example to let me understand better?

When you say:

“…BTW, Have you loked at the Looukup transformation instead?”

I tell you:

In the package both the two sets of rows (LT pipeline and RT pipeline) doesn't came from a datasource but they are a result of an intermediate elaboration of data read at the beginning of package.

Thanks again!

|||When you use the LEFT OUTER JOIN, take columnB and test if it is NULL or not. If it is NULL then you are looking at a record that didn't have a match.|||

Thanks,

I'll try to do that!

|||Actually, in thinking about it, the better thing to do is to take COLUMNA and pass it through the merge join transformation. Check that for NULL, not COLUMNB.|||

SabAlo wrote:

Hi,

I read your answer and I think I need more details about it.

When you say:

“….shouldn't you be using an left or full outer join instead?”

I tell you:

If I use a left outer join, on output I have both matches and mismatches records. Can you tell me on what I have to do the conditional split? Can you show me any example to let me understand better?

just add a conditional split after the merge join to split the rows. You can evaluate if the the columnX is null or not; if it is null means there was not a match.

SabAlo wrote:

When you say:

“…BTW, Have you loked at the Looukup transformation instead?”

I tell you:

In the package both the two sets of rows (LT pipeline and RT pipeline) doesn't came from a datasource but they are a result of an intermediate elaboration of data read at the beginning of package.

Thanks again!

Fair enough.

|||

Hi,

I followed the suggestion to consider the columnA in the output results and it is working! I test the its value and it is really null if it doesn't match the merge criteria.

As regard the testing of the other column (B), my problem is that could contain null value and should be imppossible to distinguish one record from the other.

Anyway, thanks for the hint!!

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
>