Hello guys,
I'm new to the forum and to MS SQL 2K.
I'm trying to a merge similar rows in a table into a single row and put them in a new table.
Example:-
This is my input table
TableA
ID A B C
--------
1 jk kl bj
2 sd we op
3 io po kl
1 ui gh ew
2 kl re op
1 qw kj nn
My output table should look like this
TableB
ID A1 B1 C1 A2 B2 C2 A3 B3 C3
----------------
1 jk kl bj ui gh ew qw kj nn
2 sd we op kl re op
3 io po kl
Please help me on how to create my output.
Thanks in advance,
Sid.You want help violating the rules of normalization? Should I buy you a carton of cigarettes while I'm at it? Neither activity is healthy.
Seriously, if you must do this it is important to know whether the number of records for an ID is fixed or not. If it can be any number then you are not going to be able to define the columns on your output table ahead of time and you are left with a messy dynamic query task. If there is a limit on the number of records per ID then your problem is merely a moderately difficult cross-tab query.|||normalization or not
this is a good exercise to displace data
kinda like playing scales before you actually play a song on an instrument
i will be working on this tonight|||I'll check this query today ...|||giving up
I don't understand to purpose of this query|||i now have a headache|||There's not much point in pursuing this without further clarification from coolhandsid, so save the Tylenol.|||I've got
Table1
ID | A | B | C
1 | 2 | 3 | 4
2 | 9 | 4 | 5
3 | 22| 53 94
I want this result
Table1
ID X Y Z
1 | 81 | April | NULL
Y | 12 | Dog | Sheep
12.3 | Cherry | Spain | 3|||I've got broccolli and I want lobster. I can't make one out of the other either.|||Remeber the MASH episode (when they used to be good) when they made the spam lamb for the turkish troops?|||Originally posted by blindman
You want help violating the rules of normalization? Should I buy you a carton of cigarettes while I'm at it? Neither activity is healthy.
I would not want you doing the first ... but you can certainly buy that carton of cigs for me ...|||Thanks for all your help :),
I figured it out , it can be down by a DTS package or a cross-tab query.
Sid.
Originally posted by Karolyn
I've got
Table1
ID | A | B | C
1 | 2 | 3 | 4
2 | 9 | 4 | 5
3 | 22| 53 94
I want this result
Table1
ID X Y Z
1 | 81 | April | NULL
Y | 12 | Dog | Sheep
12.3 | Cherry | Spain | 3
Showing posts with label merge. Show all posts
Showing posts with label merge. Show all posts
Monday, March 19, 2012
Monday, March 12, 2012
Help needed to merge a few database - same tables
Hi all,
I am new to SQL and have to support application that uses MS SQL 2K.
I have to merge a few database , (with the same tables structures -
each
one include identification database Id) to one database file with the
same tables structures.
The merge should run on a daily bases ( all the new data on each table
will be from the last day until current day (one of the field in the
tables).
In addition if during the merge for some reason it "fail" a
rollback procedure is needed to the situation before the merge with
these tables.
I appreciate if anyone can help me with SQL script that will do the
work.
Or direct me how I should do it.
Thanks
RGI think you need transactional replication for this. It will replicate
transactions within a transactional context, with the commit or rollback
feature you require.
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
<ran_gl@.hotmail.com> wrote in message
news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
> Hi all,
> I am new to SQL and have to support application that uses MS SQL 2K.
> I have to merge a few database , (with the same tables structures -
> each
> one include identification database Id) to one database file with the
> same tables structures.
> The merge should run on a daily bases ( all the new data on each table
> will be from the last day until current day (one of the field in the
> tables).
> In addition if during the merge for some reason it "fail" a
> rollback procedure is needed to the situation before the merge with
> these tables.
> I appreciate if anyone can help me with SQL script that will do the
> work.
> Or direct me how I should do it.
> Thanks
> RG
>|||Hi, Hillary
How are your doing?
I was thinking about SNAPSHOT replication instead of transactional ,because
the OP said he needs it on daily bases.
Well, I think it is easy to handle , however I'm not sure about ROLLBACK in
case of the failure.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>I think you need transactional replication for this. It will replicate
>transactions within a transactional context, with the commit or rollback
>feature you require.
> --
> 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
>
> <ran_gl@.hotmail.com> wrote in message
> news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
>|||Shalom Uri!
Snapshot will work as well. But it will mean you have to transfer all the
data and there is no transactional consistency. So in his case all the data
will move or none of it (if the snapshot fails). With transactional
replication, transactions are read from the tlog, decomposed into
constituent commands, and then these commands are applied within a
transactional context on the subscriber. Should a command fail, the entire
transaction is rolled back.
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
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uIdwdc2nGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi, Hillary
> How are your doing?
> I was thinking about SNAPSHOT replication instead of transactional
> ,because the OP said he needs it on daily bases.
> Well, I think it is easy to handle , however I'm not sure about ROLLBACK
> in case of the failure.
>
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>
I am new to SQL and have to support application that uses MS SQL 2K.
I have to merge a few database , (with the same tables structures -
each
one include identification database Id) to one database file with the
same tables structures.
The merge should run on a daily bases ( all the new data on each table
will be from the last day until current day (one of the field in the
tables).
In addition if during the merge for some reason it "fail" a
rollback procedure is needed to the situation before the merge with
these tables.
I appreciate if anyone can help me with SQL script that will do the
work.
Or direct me how I should do it.
Thanks
RGI think you need transactional replication for this. It will replicate
transactions within a transactional context, with the commit or rollback
feature you require.
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
<ran_gl@.hotmail.com> wrote in message
news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
> Hi all,
> I am new to SQL and have to support application that uses MS SQL 2K.
> I have to merge a few database , (with the same tables structures -
> each
> one include identification database Id) to one database file with the
> same tables structures.
> The merge should run on a daily bases ( all the new data on each table
> will be from the last day until current day (one of the field in the
> tables).
> In addition if during the merge for some reason it "fail" a
> rollback procedure is needed to the situation before the merge with
> these tables.
> I appreciate if anyone can help me with SQL script that will do the
> work.
> Or direct me how I should do it.
> Thanks
> RG
>|||Hi, Hillary
How are your doing?
I was thinking about SNAPSHOT replication instead of transactional ,because
the OP said he needs it on daily bases.
Well, I think it is easy to handle , however I'm not sure about ROLLBACK in
case of the failure.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>I think you need transactional replication for this. It will replicate
>transactions within a transactional context, with the commit or rollback
>feature you require.
> --
> 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
>
> <ran_gl@.hotmail.com> wrote in message
> news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
>|||Shalom Uri!
Snapshot will work as well. But it will mean you have to transfer all the
data and there is no transactional consistency. So in his case all the data
will move or none of it (if the snapshot fails). With transactional
replication, transactions are read from the tlog, decomposed into
constituent commands, and then these commands are applied within a
transactional context on the subscriber. Should a command fail, the entire
transaction is rolled back.
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
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uIdwdc2nGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi, Hillary
> How are your doing?
> I was thinking about SNAPSHOT replication instead of transactional
> ,because the OP said he needs it on daily bases.
> Well, I think it is easy to handle , however I'm not sure about ROLLBACK
> in case of the failure.
>
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>
Help needed to merge a few database - same tables
Hi all,
I am new to SQL and have to support application that uses MS SQL 2K.
I have to merge a few database , (with the same tables structures -
each
one include identification database Id) to one database file with the
same tables structures.
The merge should run on a daily bases ( all the new data on each table
will be from the last day until current day (one of the field in the
tables).
In addition if during the merge for some reason it "fail" a
rollback procedure is needed to the situation before the merge with
these tables.
I appreciate if anyone can help me with SQL script that will do the
work.
Or direct me how I should do it.
Thanks
RGI think you need transactional replication for this. It will replicate
transactions within a transactional context, with the commit or rollback
feature you require.
--
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
<ran_gl@.hotmail.com> wrote in message
news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
> Hi all,
> I am new to SQL and have to support application that uses MS SQL 2K.
> I have to merge a few database , (with the same tables structures -
> each
> one include identification database Id) to one database file with the
> same tables structures.
> The merge should run on a daily bases ( all the new data on each table
> will be from the last day until current day (one of the field in the
> tables).
> In addition if during the merge for some reason it "fail" a
> rollback procedure is needed to the situation before the merge with
> these tables.
> I appreciate if anyone can help me with SQL script that will do the
> work.
> Or direct me how I should do it.
> Thanks
> RG
>|||Hi, Hillary
How are your doing?
I was thinking about SNAPSHOT replication instead of transactional ,because
the OP said he needs it on daily bases.
Well, I think it is easy to handle , however I'm not sure about ROLLBACK in
case of the failure.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>I think you need transactional replication for this. It will replicate
>transactions within a transactional context, with the commit or rollback
>feature you require.
> --
> 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
>
> <ran_gl@.hotmail.com> wrote in message
> news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
>> Hi all,
>> I am new to SQL and have to support application that uses MS SQL 2K.
>> I have to merge a few database , (with the same tables structures -
>> each
>> one include identification database Id) to one database file with the
>> same tables structures.
>> The merge should run on a daily bases ( all the new data on each table
>> will be from the last day until current day (one of the field in the
>> tables).
>> In addition if during the merge for some reason it "fail" a
>> rollback procedure is needed to the situation before the merge with
>> these tables.
>> I appreciate if anyone can help me with SQL script that will do the
>> work.
>> Or direct me how I should do it.
>> Thanks
>> RG
>|||Shalom Uri!
Snapshot will work as well. But it will mean you have to transfer all the
data and there is no transactional consistency. So in his case all the data
will move or none of it (if the snapshot fails). With transactional
replication, transactions are read from the tlog, decomposed into
constituent commands, and then these commands are applied within a
transactional context on the subscriber. Should a command fail, the entire
transaction is rolled back.
--
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
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uIdwdc2nGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi, Hillary
> How are your doing?
> I was thinking about SNAPSHOT replication instead of transactional
> ,because the OP said he needs it on daily bases.
> Well, I think it is easy to handle , however I'm not sure about ROLLBACK
> in case of the failure.
>
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>>I think you need transactional replication for this. It will replicate
>>transactions within a transactional context, with the commit or rollback
>>feature you require.
>> --
>> 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
>>
>> <ran_gl@.hotmail.com> wrote in message
>> news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
>> Hi all,
>> I am new to SQL and have to support application that uses MS SQL 2K.
>> I have to merge a few database , (with the same tables structures -
>> each
>> one include identification database Id) to one database file with the
>> same tables structures.
>> The merge should run on a daily bases ( all the new data on each table
>> will be from the last day until current day (one of the field in the
>> tables).
>> In addition if during the merge for some reason it "fail" a
>> rollback procedure is needed to the situation before the merge with
>> these tables.
>> I appreciate if anyone can help me with SQL script that will do the
>> work.
>> Or direct me how I should do it.
>> Thanks
>> RG
>>
>
I am new to SQL and have to support application that uses MS SQL 2K.
I have to merge a few database , (with the same tables structures -
each
one include identification database Id) to one database file with the
same tables structures.
The merge should run on a daily bases ( all the new data on each table
will be from the last day until current day (one of the field in the
tables).
In addition if during the merge for some reason it "fail" a
rollback procedure is needed to the situation before the merge with
these tables.
I appreciate if anyone can help me with SQL script that will do the
work.
Or direct me how I should do it.
Thanks
RGI think you need transactional replication for this. It will replicate
transactions within a transactional context, with the commit or rollback
feature you require.
--
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
<ran_gl@.hotmail.com> wrote in message
news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
> Hi all,
> I am new to SQL and have to support application that uses MS SQL 2K.
> I have to merge a few database , (with the same tables structures -
> each
> one include identification database Id) to one database file with the
> same tables structures.
> The merge should run on a daily bases ( all the new data on each table
> will be from the last day until current day (one of the field in the
> tables).
> In addition if during the merge for some reason it "fail" a
> rollback procedure is needed to the situation before the merge with
> these tables.
> I appreciate if anyone can help me with SQL script that will do the
> work.
> Or direct me how I should do it.
> Thanks
> RG
>|||Hi, Hillary
How are your doing?
I was thinking about SNAPSHOT replication instead of transactional ,because
the OP said he needs it on daily bases.
Well, I think it is easy to handle , however I'm not sure about ROLLBACK in
case of the failure.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>I think you need transactional replication for this. It will replicate
>transactions within a transactional context, with the commit or rollback
>feature you require.
> --
> 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
>
> <ran_gl@.hotmail.com> wrote in message
> news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
>> Hi all,
>> I am new to SQL and have to support application that uses MS SQL 2K.
>> I have to merge a few database , (with the same tables structures -
>> each
>> one include identification database Id) to one database file with the
>> same tables structures.
>> The merge should run on a daily bases ( all the new data on each table
>> will be from the last day until current day (one of the field in the
>> tables).
>> In addition if during the merge for some reason it "fail" a
>> rollback procedure is needed to the situation before the merge with
>> these tables.
>> I appreciate if anyone can help me with SQL script that will do the
>> work.
>> Or direct me how I should do it.
>> Thanks
>> RG
>|||Shalom Uri!
Snapshot will work as well. But it will mean you have to transfer all the
data and there is no transactional consistency. So in his case all the data
will move or none of it (if the snapshot fails). With transactional
replication, transactions are read from the tlog, decomposed into
constituent commands, and then these commands are applied within a
transactional context on the subscriber. Should a command fail, the entire
transaction is rolled back.
--
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
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uIdwdc2nGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi, Hillary
> How are your doing?
> I was thinking about SNAPSHOT replication instead of transactional
> ,because the OP said he needs it on daily bases.
> Well, I think it is easy to handle , however I'm not sure about ROLLBACK
> in case of the failure.
>
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23NETQF2nGHA.4728@.TK2MSFTNGP03.phx.gbl...
>>I think you need transactional replication for this. It will replicate
>>transactions within a transactional context, with the commit or rollback
>>feature you require.
>> --
>> 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
>>
>> <ran_gl@.hotmail.com> wrote in message
>> news:1152013871.628536.225820@.p79g2000cwp.googlegroups.com...
>> Hi all,
>> I am new to SQL and have to support application that uses MS SQL 2K.
>> I have to merge a few database , (with the same tables structures -
>> each
>> one include identification database Id) to one database file with the
>> same tables structures.
>> The merge should run on a daily bases ( all the new data on each table
>> will be from the last day until current day (one of the field in the
>> tables).
>> In addition if during the merge for some reason it "fail" a
>> rollback procedure is needed to the situation before the merge with
>> these tables.
>> I appreciate if anyone can help me with SQL script that will do the
>> work.
>> Or direct me how I should do it.
>> Thanks
>> RG
>>
>
help needed on merge replication
i have a merge replication. it runs well, the subscriber disconnect from the
publisher about 5 days, then i reconnect it to publisher. theoretically
speaking, the changes made during these 5 days made on both publisher and
subscriber will merge into one copy. but customer complain about data missing
on the subscriber. I run Red Gate data compare on publisher and subscriber
database, and find there are different records, missing records and
additional records. Any one know why this happened, and what is the way to
synchronize?
Thanks a lot.
Has the merge agent been run after the 5 days of disconnect?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"green hand" <green hand@.discussions.microsoft.com> wrote in message
news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>i have a merge replication. it runs well, the subscriber disconnect from
>the
> publisher about 5 days, then i reconnect it to publisher. theoretically
> speaking, the changes made during these 5 days made on both publisher and
> subscriber will merge into one copy. but customer complain about data
> missing
> on the subscriber. I run Red Gate data compare on publisher and subscriber
> database, and find there are different records, missing records and
> additional records. Any one know why this happened, and what is the way to
> synchronize?
> Thanks a lot.
|||Thanks Mahesh.
The problem is the merge agent was restarted after the 5 days of
discounnect. And has been alway connected till now.
"Mahesh [MSFT]" wrote:
> Has the merge agent been run after the 5 days of disconnect?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "green hand" <green hand@.discussions.microsoft.com> wrote in message
> news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>
>
|||Is the merge agent failing or succeeding?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:A0E71256-C581-45A3-A888-8E181D4D6A7E@.microsoft.com...[vbcol=seagreen]
> Thanks Mahesh.
> The problem is the merge agent was restarted after the 5 days of
> discounnect. And has been alway connected till now.
> "Mahesh [MSFT]" wrote:
|||Check to see if there are conflicts in the conflict viewer. Also check to
see if you have compenstate_for_errors set to false. This setting will not
resolve some conflicts, but rather leave dml which originates on the
conflict loser in place. For example a pk violation will not be resolved
with the winning publisher's pk value, but the subscriber's one.
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
"green hand" <green hand@.discussions.microsoft.com> wrote in message
news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>i have a merge replication. it runs well, the subscriber disconnect from
>the
> publisher about 5 days, then i reconnect it to publisher. theoretically
> speaking, the changes made during these 5 days made on both publisher and
> subscriber will merge into one copy. but customer complain about data
> missing
> on the subscriber. I run Red Gate data compare on publisher and subscriber
> database, and find there are different records, missing records and
> additional records. Any one know why this happened, and what is the way to
> synchronize?
> Thanks a lot.
|||Thanks Hilary,
I checked the conflict viewer, there is a conflict and the publish win. Does
these explain the data inconsistence between publisher and discriber? I
failed to find where compenstate_for_errors is, for I am an new DBA, could u
give me some hint? should I set it to false or true?
"Hilary Cotter" wrote:
> Check to see if there are conflicts in the conflict viewer. Also check to
> see if you have compenstate_for_errors set to false. This setting will not
> resolve some conflicts, but rather leave dml which originates on the
> conflict loser in place. For example a pk violation will not be resolved
> with the winning publisher's pk value, but the subscriber's one.
> --
> 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
>
> "green hand" <green hand@.discussions.microsoft.com> wrote in message
> news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>
>
|||It might. If compenstate for errors is set to true all databases will be
consistent. If not they won't. The compenstate for errors parameter is part
of sp_addmergearticle. Use sp_helpmergearticle to verify if this is set to
true or false.
Also consult http://support.microsoft.com/kb/828637 for more info.
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
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:8CB5F530-B61D-404B-AC8B-3888509B13CE@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary,
> I checked the conflict viewer, there is a conflict and the publish win.
> Does
> these explain the data inconsistence between publisher and discriber? I
> failed to find where compenstate_for_errors is, for I am an new DBA, could
> u
> give me some hint? should I set it to false or true?
> "Hilary Cotter" wrote:
|||Hilary,
Thanks very much for help!
I found compensate_for_errors in a property of sp4. currently the publisher
and two subscribes are all under sp3. It seams the only way is to apply sp4
on all the 3 box. and then set it to ture. Am I right?
Thanks
"Hilary Cotter" wrote:
> It might. If compenstate for errors is set to true all databases will be
> consistent. If not they won't. The compenstate for errors parameter is part
> of sp_addmergearticle. Use sp_helpmergearticle to verify if this is set to
> true or false.
> Also consult http://support.microsoft.com/kb/828637 for more info.
> --
> 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
>
> "green hand" <greenhand@.discussions.microsoft.com> wrote in message
> news:8CB5F530-B61D-404B-AC8B-3888509B13CE@.microsoft.com...
>
>
|||That is correct.
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
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:A5E1B1DF-36A1-43FD-8F7A-87B46CB2E66A@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thanks very much for help!
> I found compensate_for_errors in a property of sp4. currently the
> publisher
> and two subscribes are all under sp3. It seams the only way is to apply
> sp4
> on all the 3 box. and then set it to ture. Am I right?
> Thanks
> "Hilary Cotter" wrote:
publisher about 5 days, then i reconnect it to publisher. theoretically
speaking, the changes made during these 5 days made on both publisher and
subscriber will merge into one copy. but customer complain about data missing
on the subscriber. I run Red Gate data compare on publisher and subscriber
database, and find there are different records, missing records and
additional records. Any one know why this happened, and what is the way to
synchronize?
Thanks a lot.
Has the merge agent been run after the 5 days of disconnect?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"green hand" <green hand@.discussions.microsoft.com> wrote in message
news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>i have a merge replication. it runs well, the subscriber disconnect from
>the
> publisher about 5 days, then i reconnect it to publisher. theoretically
> speaking, the changes made during these 5 days made on both publisher and
> subscriber will merge into one copy. but customer complain about data
> missing
> on the subscriber. I run Red Gate data compare on publisher and subscriber
> database, and find there are different records, missing records and
> additional records. Any one know why this happened, and what is the way to
> synchronize?
> Thanks a lot.
|||Thanks Mahesh.
The problem is the merge agent was restarted after the 5 days of
discounnect. And has been alway connected till now.
"Mahesh [MSFT]" wrote:
> Has the merge agent been run after the 5 days of disconnect?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "green hand" <green hand@.discussions.microsoft.com> wrote in message
> news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>
>
|||Is the merge agent failing or succeeding?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:A0E71256-C581-45A3-A888-8E181D4D6A7E@.microsoft.com...[vbcol=seagreen]
> Thanks Mahesh.
> The problem is the merge agent was restarted after the 5 days of
> discounnect. And has been alway connected till now.
> "Mahesh [MSFT]" wrote:
|||Check to see if there are conflicts in the conflict viewer. Also check to
see if you have compenstate_for_errors set to false. This setting will not
resolve some conflicts, but rather leave dml which originates on the
conflict loser in place. For example a pk violation will not be resolved
with the winning publisher's pk value, but the subscriber's one.
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
"green hand" <green hand@.discussions.microsoft.com> wrote in message
news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>i have a merge replication. it runs well, the subscriber disconnect from
>the
> publisher about 5 days, then i reconnect it to publisher. theoretically
> speaking, the changes made during these 5 days made on both publisher and
> subscriber will merge into one copy. but customer complain about data
> missing
> on the subscriber. I run Red Gate data compare on publisher and subscriber
> database, and find there are different records, missing records and
> additional records. Any one know why this happened, and what is the way to
> synchronize?
> Thanks a lot.
|||Thanks Hilary,
I checked the conflict viewer, there is a conflict and the publish win. Does
these explain the data inconsistence between publisher and discriber? I
failed to find where compenstate_for_errors is, for I am an new DBA, could u
give me some hint? should I set it to false or true?
"Hilary Cotter" wrote:
> Check to see if there are conflicts in the conflict viewer. Also check to
> see if you have compenstate_for_errors set to false. This setting will not
> resolve some conflicts, but rather leave dml which originates on the
> conflict loser in place. For example a pk violation will not be resolved
> with the winning publisher's pk value, but the subscriber's one.
> --
> 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
>
> "green hand" <green hand@.discussions.microsoft.com> wrote in message
> news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>
>
|||It might. If compenstate for errors is set to true all databases will be
consistent. If not they won't. The compenstate for errors parameter is part
of sp_addmergearticle. Use sp_helpmergearticle to verify if this is set to
true or false.
Also consult http://support.microsoft.com/kb/828637 for more info.
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
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:8CB5F530-B61D-404B-AC8B-3888509B13CE@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary,
> I checked the conflict viewer, there is a conflict and the publish win.
> Does
> these explain the data inconsistence between publisher and discriber? I
> failed to find where compenstate_for_errors is, for I am an new DBA, could
> u
> give me some hint? should I set it to false or true?
> "Hilary Cotter" wrote:
|||Hilary,
Thanks very much for help!
I found compensate_for_errors in a property of sp4. currently the publisher
and two subscribes are all under sp3. It seams the only way is to apply sp4
on all the 3 box. and then set it to ture. Am I right?
Thanks
"Hilary Cotter" wrote:
> It might. If compenstate for errors is set to true all databases will be
> consistent. If not they won't. The compenstate for errors parameter is part
> of sp_addmergearticle. Use sp_helpmergearticle to verify if this is set to
> true or false.
> Also consult http://support.microsoft.com/kb/828637 for more info.
> --
> 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
>
> "green hand" <greenhand@.discussions.microsoft.com> wrote in message
> news:8CB5F530-B61D-404B-AC8B-3888509B13CE@.microsoft.com...
>
>
|||That is correct.
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
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:A5E1B1DF-36A1-43FD-8F7A-87B46CB2E66A@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thanks very much for help!
> I found compensate_for_errors in a property of sp4. currently the
> publisher
> and two subscribes are all under sp3. It seams the only way is to apply
> sp4
> on all the 3 box. and then set it to ture. Am I right?
> Thanks
> "Hilary Cotter" wrote:
Wednesday, March 7, 2012
Help Merge Replication
Hi to all,
I'm writing an application with Comact Framework 2.0 and I'd like to use merge replication to synchronize data among the sever and 25 Pocket PC.
The DataBase contains static tables (for configuration) and dynamic tables that I want to publish.
Configuration tables are small so no problem to publish them. The problem is with dynamic table and that's my first question:
One of these dynamic tables contains 150,000 records and data inside it (I can't use filter because all data must be "seen" to all POcket PC) should be published on each Pocket PC so: Is it possible to use merge replication with such big table?
Or is there another method to treat this kind of problem?
Should I use merge replication only for configuration tables?
If it's possible to use merge replication, what's the best hardware configuration for my server?
For example:
1) One PC for IIS and one for SQL Server with 2 CPU
2) or two PC in NLB so that request from Pocket PC's to IIS are balanced?
I'musing SQL Server 2005 SP1 and Windows CE 5.0
TIA.
Flavio
there's no table size limit for a table to be published by merge replication, 150k rows should not be a problem. Regarding your hardware configuration, it's up to you as it will depend if you're getting bottlenecks or not. Test with one configuration to see if there are any bottlenecks that can be relieved by the other configuration.Help me: About Sql server Internet merge Replication
i am working on merge replication over internet
i configure each and everything as Paul told in artical
i configure TCP\IP port at client network utility as 1433, also server at
server network utility as 1433
also FTP port at publisher properties is 21
but it is not working
it show error as "sql server not exist or access denied"
i configure client computer using client network utility,
server computer by server network utility
also proper publication and subcriber and ftp root
please solve my problem
need u'r kind help
thanking you
amy
I'm still pretty new at some of this replication stuff. But I went and
pinged the subscriber from the publisher and vice versa. Then I set up
Aliases through the configuration tool using TCP\IP and setting the alias
name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
know if this is helpful, but it sounds like to me that your servers just
can't see one another and an alias, using TCP/IP or even a linkedserver may
help.
Good Luck!!
Scott E. Hunley (MCAD)
Measure Twice, Cut Once!!!
"amy" wrote:
> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy
|||thanks for reply
please tell me how to set up
Aliases through the configuration tool using TCP\IP
i done pinging from subsciber from publisher and also publisher to subsciber
thanking you
amy
"Scott Hunley" wrote:
[vbcol=seagreen]
> I'm still pretty new at some of this replication stuff. But I went and
> pinged the subscriber from the publisher and vice versa. Then I set up
> Aliases through the configuration tool using TCP\IP and setting the alias
> name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
> know if this is helpful, but it sounds like to me that your servers just
> can't see one another and an alias, using TCP/IP or even a linkedserver may
> help.
> Good Luck!!
> --
> Scott E. Hunley (MCAD)
> Measure Twice, Cut Once!!!
>
> "amy" wrote:
|||If you were able to ping the machines successfully from one another then
that's a good sign. Now, in the Configuration Tool:
1. Right-Click the Alias node on the tree.
2. Enter the Name of the server you are communicating with (make sure these
are exactly the same.
3. Enter a port number, by default SQL uses 1433
4. choose TCP/IP
5. Enter the IP address of the server your communication with.
That should give you a way of communicating to the server.
Try that and let me know.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"amy" wrote:
[vbcol=seagreen]
> thanks for reply
> please tell me how to set up
> Aliases through the configuration tool using TCP\IP
> i done pinging from subsciber from publisher and also publisher to subsciber
> --
> thanking you
> amy
>
> "Scott Hunley" wrote:
|||I got the same error all morning while working with my Replication problems.
I got past this error by going into the properties for the publication and
adding my IUSR_MACHINENAME account to the publication access list. It started
working like a champ. This is with SQL 2005, I'm not sure of how to do it
with SQL 2000.
Hope this helps.
"amy" wrote:
> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy
i configure each and everything as Paul told in artical
i configure TCP\IP port at client network utility as 1433, also server at
server network utility as 1433
also FTP port at publisher properties is 21
but it is not working
it show error as "sql server not exist or access denied"
i configure client computer using client network utility,
server computer by server network utility
also proper publication and subcriber and ftp root
please solve my problem
need u'r kind help
thanking you
amy
I'm still pretty new at some of this replication stuff. But I went and
pinged the subscriber from the publisher and vice versa. Then I set up
Aliases through the configuration tool using TCP\IP and setting the alias
name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
know if this is helpful, but it sounds like to me that your servers just
can't see one another and an alias, using TCP/IP or even a linkedserver may
help.
Good Luck!!
Scott E. Hunley (MCAD)
Measure Twice, Cut Once!!!
"amy" wrote:
> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy
|||thanks for reply
please tell me how to set up
Aliases through the configuration tool using TCP\IP
i done pinging from subsciber from publisher and also publisher to subsciber
thanking you
amy
"Scott Hunley" wrote:
[vbcol=seagreen]
> I'm still pretty new at some of this replication stuff. But I went and
> pinged the subscriber from the publisher and vice versa. Then I set up
> Aliases through the configuration tool using TCP\IP and setting the alias
> name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
> know if this is helpful, but it sounds like to me that your servers just
> can't see one another and an alias, using TCP/IP or even a linkedserver may
> help.
> Good Luck!!
> --
> Scott E. Hunley (MCAD)
> Measure Twice, Cut Once!!!
>
> "amy" wrote:
|||If you were able to ping the machines successfully from one another then
that's a good sign. Now, in the Configuration Tool:
1. Right-Click the Alias node on the tree.
2. Enter the Name of the server you are communicating with (make sure these
are exactly the same.
3. Enter a port number, by default SQL uses 1433
4. choose TCP/IP
5. Enter the IP address of the server your communication with.
That should give you a way of communicating to the server.
Try that and let me know.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"amy" wrote:
[vbcol=seagreen]
> thanks for reply
> please tell me how to set up
> Aliases through the configuration tool using TCP\IP
> i done pinging from subsciber from publisher and also publisher to subsciber
> --
> thanking you
> amy
>
> "Scott Hunley" wrote:
|||I got the same error all morning while working with my Replication problems.
I got past this error by going into the properties for the publication and
adding my IUSR_MACHINENAME account to the publication access list. It started
working like a champ. This is with SQL 2005, I'm not sure of how to do it
with SQL 2000.
Hope this helps.
"amy" wrote:
> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy
Subscribe to:
Posts (Atom)