Can this be solved with replication.

Hi,
I have 5 offices accross the US. At the HQ we have a legacy system (unix)
with customer data. I want the customer data to be replicated to the offices
SQL servers (MSDE) automatically. The legacy system does not support
replication so I was thinking of running a DTS task nightly to copy the
entire table (there is no time stamp on legacy ststem table) to a SQL server
at the HQ and then replicate but what I am not sure of is I have to truncate
the customer table from the HQ SQL Server before running the DTS task. How
would this affect the replication to the offices? I am dealing with 8000 rows
here.
A truncate table does not get replicated, so if this were your loading
strategy, it would work once and then subsequent cycles would throw a pile
of errors. However, you could accomplish this with snapshot replication as
long as you can overwrite each subscriber each night.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:C1BE2F0C-0342-404D-B1F7-1345027F44EE@.microsoft.com...
> Hi,
> I have 5 offices accross the US. At the HQ we have a legacy system (unix)
> with customer data. I want the customer data to be replicated to the
> offices
> SQL servers (MSDE) automatically. The legacy system does not support
> replication so I was thinking of running a DTS task nightly to copy the
> entire table (there is no time stamp on legacy ststem table) to a SQL
> server
> at the HQ and then replicate but what I am not sure of is I have to
> truncate
> the customer table from the HQ SQL Server before running the DTS task. How
> would this affect the replication to the offices? I am dealing with 8000
> rows
> here.
|||How long would the snapshot take for 5 sites? Also can this be done at a
scheduled time or can I have the DTS execute the replication after the new
customer data has been imported?
Thanks
"Michael Hotek" wrote:

> A truncate table does not get replicated, so if this were your loading
> strategy, it would work once and then subsequent cycles would throw a pile
> of errors. However, you could accomplish this with snapshot replication as
> long as you can overwrite each subscriber each night.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:C1BE2F0C-0342-404D-B1F7-1345027F44EE@.microsoft.com...
>
>
|||Both. DTS can kick it off and it can also be scheduled. As for how long,
no idea. That would depend upon thevolume of data, connectivity between
each site, amount of available network bandwidth, if the servers are doing
anything else, and lots of other factors.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2735F953-D4F3-41F2-A3B9-69AF71A132AF@.microsoft.com...[vbcol=seagreen]
> How long would the snapshot take for 5 sites? Also can this be done at a
> scheduled time or can I have the DTS execute the replication after the new
> customer data has been imported?
> Thanks
> "Michael Hotek" wrote:
|||8000 rows is not that much (assuming the rows are not 'extra-wide').
An snapshot for that is probably smaller than 1mb or so (depending on your
structure, of course). So, the snapshot should be built really fast, and
downloaded in a few minutes (tops).
BTW: the snapshot is taken at the publisher, and it is only one unless you
are using dynamic snapshot/filters (merge).
You can have the DTS execute the snapshot after finishing. Of course, the
data in the sites is going to be wiped out every single time you do this.
That shouldn't be a problem (since it seems to be what you want).
Jos.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:2735F953-D4F3-41F2-A3B9-69AF71A132AF@.microsoft.com...[vbcol=seagreen]
> How long would the snapshot take for 5 sites? Also can this be done at a
> scheduled time or can I have the DTS execute the replication after the new
> customer data has been imported?
> Thanks
> "Michael Hotek" wrote:
|||In these situations, I dump the table from the foreign system into sql
(publisher) periodically, then *compare the table* to an identical,
replicated table, except for the rowguid of course, then only delete,ins,
upd are replicated
I've made generic compare sp's to handle any number of tables
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23sOTAz$KGHA.3424@.TK2MSFTNGP12.phx.gbl...
> 8000 rows is not that much (assuming the rows are not 'extra-wide').
> An snapshot for that is probably smaller than 1mb or so (depending on your
> structure, of course). So, the snapshot should be built really fast, and
> downloaded in a few minutes (tops).
> BTW: the snapshot is taken at the publisher, and it is only one unless you
> are using dynamic snapshot/filters (merge).
> You can have the DTS execute the snapshot after finishing. Of course, the
> data in the sites is going to be wiped out every single time you do this.
> That shouldn't be a problem (since it seems to be what you want).
> Jos.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:2735F953-D4F3-41F2-A3B9-69AF71A132AF@.microsoft.com...
>
|||Thanks lads!
"Chris" wrote:

> Hi,
> I have 5 offices accross the US. At the HQ we have a legacy system (unix)
> with customer data. I want the customer data to be replicated to the offices
> SQL servers (MSDE) automatically. The legacy system does not support
> replication so I was thinking of running a DTS task nightly to copy the
> entire table (there is no time stamp on legacy ststem table) to a SQL server
> at the HQ and then replicate but what I am not sure of is I have to truncate
> the customer table from the HQ SQL Server before running the DTS task. How
> would this affect the replication to the offices? I am dealing with 8000 rows
> here.
|||Hi,
The Snapshot replications works fine. Thanks again!
"Chris" wrote:

> Hi,
> I have 5 offices accross the US. At the HQ we have a legacy system (unix)
> with customer data. I want the customer data to be replicated to the offices
> SQL servers (MSDE) automatically. The legacy system does not support
> replication so I was thinking of running a DTS task nightly to copy the
> entire table (there is no time stamp on legacy ststem table) to a SQL server
> at the HQ and then replicate but what I am not sure of is I have to truncate
> the customer table from the HQ SQL Server before running the DTS task. How
> would this affect the replication to the offices? I am dealing with 8000 rows
> here.
|||Hi,
How do I set snapshot to overwrite the the subscriber? Or this does
automatically?
Thanks
"Michael Hotek" wrote:

> A truncate table does not get replicated, so if this were your loading
> strategy, it would work once and then subsequent cycles would throw a pile
> of errors. However, you could accomplish this with snapshot replication as
> long as you can overwrite each subscriber each night.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:C1BE2F0C-0342-404D-B1F7-1345027F44EE@.microsoft.com...
>
>

can this b done by replication ?

hi all,
I have a single sql server, and two databases with similar
table names and structures for some 10-15 tables.
I want replicate the data from one db to another to keep
them in sync.
Can I do this using replication ?
Is replication possible between two db's of a single
server ?
I tried already it gives an error 18483 could not connect
to server cause distributor_admin is not defined as a
remote login at the server ?
thanks in advance
You can replicate to the same server. TO fix your distributor_admin problem
disable replication and re enable it.
This normally fixes this problem.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"San" <anonymous@.discussions.microsoft.com> wrote in message
news:007201c4dc8a$e52018a0$a401280a@.phx.gbl...
> hi all,
> I have a single sql server, and two databases with similar
> table names and structures for some 10-15 tables.
> I want replicate the data from one db to another to keep
> them in sync.
> Can I do this using replication ?
> Is replication possible between two db's of a single
> server ?
> I tried already it gives an error 18483 could not connect
> to server cause distributor_admin is not defined as a
> remote login at the server ?
> thanks in advance

Can subscriber know the results of Merge rep?

We have two notebooks doing Merge replication (PULL) from the distributor.
We use the Active-X merge from ACCESS.
Can the subscriber know the results of the replication? I now go to EM on
the publisher and look at the Merge Agents to see if there were conflicts,
then to the conflict viewer. Is there a way I can know from the subscriber
doing the Pull?
Thanks,
Steve
Use the status event.
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
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:7A88B570-396C-4FB6-BE96-12F60CD99AE6@.microsoft.com...
> We have two notebooks doing Merge replication (PULL) from the distributor.
> We use the Active-X merge from ACCESS.
> Can the subscriber know the results of the replication? I now go to EM on
> the publisher and look at the Merge Agents to see if there were conflicts,
> then to the conflict viewer. Is there a way I can know from the
> subscriber
> doing the Pull?
> Thanks,
> Steve
|||Hi,
I am having trouble making it work in VBA behind ACCESS. In VB, looks like
you would use:
Private WithEvents mobjMerge As SQLMERGXLib.SQLMerge
in VBA, it would bd
DIM mobjMerge As SQLMERGXLib.SQLMerge.
I cannot get the WithEvents in anywhere where it will pass the compiler.
any thoughts.
"Hilary Cotter" wrote:

> Use the status event.
> --
> 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
> "SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
> news:7A88B570-396C-4FB6-BE96-12F60CD99AE6@.microsoft.com...
>
>

can sqlsvr2005 replicate tables to sqlsvr2000?

Hello,
I have replication in place between 2 sqlsvr2000 DBs. Everything is working
OK. I am looking to upgrade the publishing sqlsvr from sqlsvr2000 to
sqlsvr2005. The subscription sever will remain as a sqlsvr2000 server
because I don't own that one. If I were to go ahead an perform the upgrade
on my end, will I still be able to replicate my table to the sqlsvr2000
subscriber?
Thanks,
Rich
Absolutely. The only caveat is that if you are using merge replication the
snapshot will have to be sent again.
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
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEE16A4E-CCF9-4D6A-9A4C-4F4A69FDBD24@.microsoft.com...
> Hello,
> I have replication in place between 2 sqlsvr2000 DBs. Everything is
> working
> OK. I am looking to upgrade the publishing sqlsvr from sqlsvr2000 to
> sqlsvr2005. The subscription sever will remain as a sqlsvr2000 server
> because I don't own that one. If I were to go ahead an perform the
> upgrade
> on my end, will I still be able to replicate my table to the sqlsvr2000
> subscriber?
> Thanks,
> Rich
|||Thanks. The folks over my way are a little reluctant to move forward with
the upgrade - but hey! We have an MSDN subscription and I already downloaded
all of sqlsrv2005 enterprise. They were trying to use the Replication angle.
Glad that won't be an issue.
Rich
"Hilary Cotter" wrote:

> Absolutely. The only caveat is that if you are using merge replication the
> snapshot will have to be sent again.
> --
> 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
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEE16A4E-CCF9-4D6A-9A4C-4F4A69FDBD24@.microsoft.com...
>
>

Can sp_changearticle help when needing to change the primary key?

We need to add a new column to a table that is part of a multi-table
publication replicated via transactional replication. We've read (and
re-read) the sp_changearticle page in Books Online without fully
understanding how we might be able to use this command to help with our task.
Can someone please provide two important answers -- 1) can we use
sp_changearticle to make this kind of change to our publication? 2) can you
offer an example of how sp_changearticle is coded for such purposes.
Actually, we'd be interested in seeing how sp_changearticle is coded in
general, even if it cannot be used for our particular task.
Thanks,
Barry Spiegel
barry.spiegel@.eds.com
2) sp_changearticle 'pubs', 'jobs','description','this is the new
description'
1) no, you use use sp_repladdcolumn like this:
sp_repladdcolumn 'jobs','intcol','int not null default(1)'
This column will be modified in all publications and their subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Barry Spiegel" <Barry Spiegel@.discussions.microsoft.com> wrote in message
news:732D9DB5-C5A6-4A65-AEB7-37384C791401@.microsoft.com...
> We need to add a new column to a table that is part of a multi-table
> publication replicated via transactional replication. We've read (and
> re-read) the sp_changearticle page in Books Online without fully
> understanding how we might be able to use this command to help with our
task.
> Can someone please provide two important answers -- 1) can we use
> sp_changearticle to make this kind of change to our publication? 2) can
you
> offer an example of how sp_changearticle is coded for such purposes.
> Actually, we'd be interested in seeing how sp_changearticle is coded in
> general, even if it cannot be used for our particular task.
> Thanks,
> Barry Spiegel
> barry.spiegel@.eds.com
>

can replication works for a Data Warehouse ?

Hi ,
I am trying to set replication for an exsiting Data Center (let's call it
DC) from the various databases in Server1
my problem :
1) i set up the publication with the snapshot to keep existing data in the
table(there's already data) if same table name is found , however, the
synchronization failed becoz of duplicate index/key
ques : shldn't it just ignore those duplicates ?
and even if there's new data for that table it couldn't replicate over due
to the duplicates
2) i have also tried to use this option "to delete those matching with row
filter" but i realised that those that are the same record it would be
deleted and re-replicated over but those in the destination that not in the
source table have been deleted
3) i finally tried using this option "to delete data and re-create the table
and it works but the issue here is i have many databases to be replicated
over to the data warhouse , i shldn't be forced to purposely removed a table
with data to somewhere else and then after replicate is successful then copy
that data over
could any one kindly advise
tks & rdgs
Yes replication can be used in this scenario. Snapshot replication performs
a period refresh of all of the data. It sounds like transactional
replication might be a better option for you (incremental changes).
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
> Hi ,
> I am trying to set replication for an exsiting Data Center (let's call it
> DC) from the various databases in Server1
> my problem :
> 1) i set up the publication with the snapshot to keep existing data in the
> table(there's already data) if same table name is found , however, the
> synchronization failed becoz of duplicate index/key
> ques : shldn't it just ignore those duplicates ?
> and even if there's new data for that table it couldn't replicate over due
> to the duplicates
> 2) i have also tried to use this option "to delete those matching with row
> filter" but i realised that those that are the same record it would be
> deleted and re-replicated over but those in the destination that not in
> the
> source table have been deleted
> 3) i finally tried using this option "to delete data and re-create the
> table
> and it works but the issue here is i have many databases to be replicated
> over to the data warhouse , i shldn't be forced to purposely removed a
> table
> with data to somewhere else and then after replicate is successful then
> copy
> that data over
> could any one kindly advise
> tks & rdgs
|||Hi,
If Replication does work for a Data Warehouse , how shld i set it up as i
have tried the settings below but it somehow did not work for me
appreciate any advise
tks & rdgs
"Jerry Spivey" wrote:

> Yes replication can be used in this scenario. Snapshot replication performs
> a period refresh of all of the data. It sounds like transactional
> replication might be a better option for you (incremental changes).
> HTH
> Jerry
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
>
>
|||Replication can be a difficult. If I were you I would step back from the
whole data warehouse thing and implement both snapshot and transactional
replication on a test box and become familiar with the varioius offerings,
options, agents, icons and settings (BOL is pretty good at explaining
replication types). Once you have a better understand of how replication
works and what are the basic and advanced feature sets of repliction, I
think you'll be able to determine which replication type (if any - maybe DTS
would work better or BULK INSERT) would be best for your scenario.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A7FEFCA8-8244-45D7-B664-6A5656121451@.microsoft.com...[vbcol=seagreen]
> Hi,
> If Replication does work for a Data Warehouse , how shld i set it up as i
> have tried the settings below but it somehow did not work for me
> appreciate any advise
> tks & rdgs
> "Jerry Spivey" wrote:

can replication works for a Data Warehouse ?

Hi ,
I am trying to set replication for an exsiting Data Center (let's call it
DC) from the various databases in Server1
my problem :
1) i set up the publication with the snapshot to keep existing data in the
table(there's already data) if same table name is found , however, the
synchronization failed becoz of duplicate index/key
ques : shldn't it just ignore those duplicates ?
and even if there's new data for that table it couldn't replicate over due
to the duplicates
2) i have also tried to use this option "to delete those matching with row
filter" but i realised that those that are the same record it would be
deleted and re-replicated over but those in the destination that not in the
source table have been deleted
3) i finally tried using this option "to delete data and re-create the table
and it works but the issue here is i have many databases to be replicated
over to the data warhouse , i shldn't be forced to purposely removed a table
with data to somewhere else and then after replicate is successful then copy
that data over
could any one kindly advise
tks & rdgsYes replication can be used in this scenario. Snapshot replication performs
a period refresh of all of the data. It sounds like transactional
replication might be a better option for you (incremental changes).
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
> Hi ,
> I am trying to set replication for an exsiting Data Center (let's call it
> DC) from the various databases in Server1
> my problem :
> 1) i set up the publication with the snapshot to keep existing data in the
> table(there's already data) if same table name is found , however, the
> synchronization failed becoz of duplicate index/key
> ques : shldn't it just ignore those duplicates ?
> and even if there's new data for that table it couldn't replicate over due
> to the duplicates
> 2) i have also tried to use this option "to delete those matching with row
> filter" but i realised that those that are the same record it would be
> deleted and re-replicated over but those in the destination that not in
> the
> source table have been deleted
> 3) i finally tried using this option "to delete data and re-create the
> table
> and it works but the issue here is i have many databases to be replicated
> over to the data warhouse , i shldn't be forced to purposely removed a
> table
> with data to somewhere else and then after replicate is successful then
> copy
> that data over
> could any one kindly advise
> tks & rdgs|||Hi,
If Replication does work for a Data Warehouse , how shld i set it up as i
have tried the settings below but it somehow did not work for me
appreciate any advise
tks & rdgs
"Jerry Spivey" wrote:
> Yes replication can be used in this scenario. Snapshot replication performs
> a period refresh of all of the data. It sounds like transactional
> replication might be a better option for you (incremental changes).
> HTH
> Jerry
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
> > Hi ,
> >
> > I am trying to set replication for an exsiting Data Center (let's call it
> > DC) from the various databases in Server1
> >
> > my problem :
> >
> > 1) i set up the publication with the snapshot to keep existing data in the
> > table(there's already data) if same table name is found , however, the
> > synchronization failed becoz of duplicate index/key
> >
> > ques : shldn't it just ignore those duplicates ?
> >
> > and even if there's new data for that table it couldn't replicate over due
> > to the duplicates
> >
> > 2) i have also tried to use this option "to delete those matching with row
> > filter" but i realised that those that are the same record it would be
> > deleted and re-replicated over but those in the destination that not in
> > the
> > source table have been deleted
> >
> > 3) i finally tried using this option "to delete data and re-create the
> > table
> > and it works but the issue here is i have many databases to be replicated
> > over to the data warhouse , i shldn't be forced to purposely removed a
> > table
> > with data to somewhere else and then after replicate is successful then
> > copy
> > that data over
> >
> > could any one kindly advise
> >
> > tks & rdgs
>
>|||Replication can be a difficult. If I were you I would step back from the
whole data warehouse thing and implement both snapshot and transactional
replication on a test box and become familiar with the varioius offerings,
options, agents, icons and settings (BOL is pretty good at explaining
replication types). Once you have a better understand of how replication
works and what are the basic and advanced feature sets of repliction, I
think you'll be able to determine which replication type (if any - maybe DTS
would work better or BULK INSERT) would be best for your scenario.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A7FEFCA8-8244-45D7-B664-6A5656121451@.microsoft.com...
> Hi,
> If Replication does work for a Data Warehouse , how shld i set it up as i
> have tried the settings below but it somehow did not work for me
> appreciate any advise
> tks & rdgs
> "Jerry Spivey" wrote:
>> Yes replication can be used in this scenario. Snapshot replication
>> performs
>> a period refresh of all of the data. It sounds like transactional
>> replication might be a better option for you (incremental changes).
>> HTH
>> Jerry
>> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
>> news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
>> > Hi ,
>> >
>> > I am trying to set replication for an exsiting Data Center (let's call
>> > it
>> > DC) from the various databases in Server1
>> >
>> > my problem :
>> >
>> > 1) i set up the publication with the snapshot to keep existing data in
>> > the
>> > table(there's already data) if same table name is found , however, the
>> > synchronization failed becoz of duplicate index/key
>> >
>> > ques : shldn't it just ignore those duplicates ?
>> >
>> > and even if there's new data for that table it couldn't replicate over
>> > due
>> > to the duplicates
>> >
>> > 2) i have also tried to use this option "to delete those matching with
>> > row
>> > filter" but i realised that those that are the same record it would be
>> > deleted and re-replicated over but those in the destination that not in
>> > the
>> > source table have been deleted
>> >
>> > 3) i finally tried using this option "to delete data and re-create the
>> > table
>> > and it works but the issue here is i have many databases to be
>> > replicated
>> > over to the data warhouse , i shldn't be forced to purposely removed a
>> > table
>> > with data to somewhere else and then after replicate is successful then
>> > copy
>> > that data over
>> >
>> > could any one kindly advise
>> >
>> > tks & rdgs
>>

can replication works for a Data Warehouse ?

Hi ,
I am trying to set replication for an exsiting Data Center (let's call it
DC) from the various databases in Server1
my problem :
1) i set up the publication with the snapshot to keep existing data in the
table(there's already data) if same table name is found , however, the
synchronization failed becoz of duplicate index/key
ques : shldn't it just ignore those duplicates ?
and even if there's new data for that table it couldn't replicate over due
to the duplicates
2) i have also tried to use this option "to delete those matching with row
filter" but i realised that those that are the same record it would be
deleted and re-replicated over but those in the destination that not in the
source table have been deleted
3) i finally tried using this option "to delete data and re-create the table
and it works but the issue here is i have many databases to be replicated
over to the data warhouse , i shldn't be forced to purposely removed a table
with data to somewhere else and then after replicate is successful then copy
that data over
could any one kindly advise
tks & rdgsYes replication can be used in this scenario. Snapshot replication performs
a period refresh of all of the data. It sounds like transactional
replication might be a better option for you (incremental changes).
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
> Hi ,
> I am trying to set replication for an exsiting Data Center (let's call it
> DC) from the various databases in Server1
> my problem :
> 1) i set up the publication with the snapshot to keep existing data in the
> table(there's already data) if same table name is found , however, the
> synchronization failed becoz of duplicate index/key
> ques : shldn't it just ignore those duplicates ?
> and even if there's new data for that table it couldn't replicate over due
> to the duplicates
> 2) i have also tried to use this option "to delete those matching with row
> filter" but i realised that those that are the same record it would be
> deleted and re-replicated over but those in the destination that not in
> the
> source table have been deleted
> 3) i finally tried using this option "to delete data and re-create the
> table
> and it works but the issue here is i have many databases to be replicated
> over to the data warhouse , i shldn't be forced to purposely removed a
> table
> with data to somewhere else and then after replicate is successful then
> copy
> that data over
> could any one kindly advise
> tks & rdgs|||Hi,
If Replication does work for a Data Warehouse , how shld i set it up as i
have tried the settings below but it somehow did not work for me
appreciate any advise
tks & rdgs
"Jerry Spivey" wrote:

> Yes replication can be used in this scenario. Snapshot replication perfor
ms
> a period refresh of all of the data. It sounds like transactional
> replication might be a better option for you (incremental changes).
> HTH
> Jerry
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:91170637-D104-4CCD-AEC9-F2458A0C282A@.microsoft.com...
>
>|||Replication can be a difficult. If I were you I would step back from the
whole data warehouse thing and implement both snapshot and transactional
replication on a test box and become familiar with the varioius offerings,
options, agents, icons and settings (BOL is pretty good at explaining
replication types). Once you have a better understand of how replication
works and what are the basic and advanced feature sets of repliction, I
think you'll be able to determine which replication type (if any - maybe DTS
would work better or BULK INSERT) would be best for your scenario.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:A7FEFCA8-8244-45D7-B664-6A5656121451@.microsoft.com...[vbcol=seagreen]
> Hi,
> If Replication does work for a Data Warehouse , how shld i set it up as i
> have tried the settings below but it somehow did not work for me
> appreciate any advise
> tks & rdgs
> "Jerry Spivey" wrote:
>

Can replication handle existing database?

Hello:
I have the following situatioin. A few months ago, I setup a secondary SQL
server with the same schema and data as the primary. But that server was
shut down for a few months.
Currently, I am thinking to set up replication (transaction). Since the
database is rather huge, I think I will try transaction replication. Do you
think it will work so that the secondary server will be in sync with the
primary server? This is the first time I am doing it.
Thanks,
Q
If the data is now out of sync, this'll need attending to first. You could
use Redgate's DataCompare to synchronize the subscriber with the publisher
then after that do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Can replication be ran on a MS sharepoint database?

Hello,

I was curious if you can have merge and/or snapshot replication setup on a MS sharepoint database? Thanks.

John

I don't think that those will work for the following reason.

At a recent MS course, we asked our instructor if it was possible to change the content database in SP central Admin to a secondary database server during a crisis. They advised that this might be a problem because the SP configuration database creates hard-coded entries referencing the database server name that it resides on. They suspsected that these entries would cause problems in the long run, if not right away.

We decided to test this out by creating a virtual environment that mimicked our production system. We then copied the database to a seperate physical machine. To try and avoid the configuration database problems, we created a new virtual site on the VM that mirrored our existing front end and pointed it to the new database to recreate the configuration database.

Everything worked okay for about 3 hours then we began to see timeouts due to maxpoolsize being reached. When we checked the event log we also found that the server was STILL trying to connect to the original database even though everything was on the new server. Eventually, it became impossible to access any of the sites in the virtual environment.

I imagine that these entries in the configuration database would cause the same problem if we were to replicate from one server to another. We're now thinking of using log shipping or clustering to try and provide the redundancy we're looking for.

|||

Jaydog,

Thanks for your reply.

John

|||Hey John,

Looks like I was premature with my first response. We tried the same thing one more time, except that we shut down the old SQL Server before clearing the old configuration database and allowing SharePoint to rebuild it. So far, it looks like everything is up and running.

We're giving it a few days to see if there are any adverse effects and then we will be trying to get merge replication up and running. I'll post again once there is more to report.|||

Jaydog,

I look forward to what you find out.

John

|||

Jaydog,

I was curious if you had an update on this?

John

|||

Jaydog,

I would also be interested in finding out how that went.

JBW

Can replication be ran on a MS sharepoint database?

Hello,

I was curious if you can have merge and/or snapshot replication setup on a MS sharepoint database? Thanks.

John

I don't think that those will work for the following reason.

At a recent MS course, we asked our instructor if it was possible to change the content database in SP central Admin to a secondary database server during a crisis. They advised that this might be a problem because the SP configuration database creates hard-coded entries referencing the database server name that it resides on. They suspsected that these entries would cause problems in the long run, if not right away.

We decided to test this out by creating a virtual environment that mimicked our production system. We then copied the database to a seperate physical machine. To try and avoid the configuration database problems, we created a new virtual site on the VM that mirrored our existing front end and pointed it to the new database to recreate the configuration database.

Everything worked okay for about 3 hours then we began to see timeouts due to maxpoolsize being reached. When we checked the event log we also found that the server was STILL trying to connect to the original database even though everything was on the new server. Eventually, it became impossible to access any of the sites in the virtual environment.

I imagine that these entries in the configuration database would cause the same problem if we were to replicate from one server to another. We're now thinking of using log shipping or clustering to try and provide the redundancy we're looking for.

|||

Jaydog,

Thanks for your reply.

John

|||Hey John,

Looks like I was premature with my first response. We tried the same thing one more time, except that we shut down the old SQL Server before clearing the old configuration database and allowing SharePoint to rebuild it. So far, it looks like everything is up and running.

We're giving it a few days to see if there are any adverse effects and then we will be trying to get merge replication up and running. I'll post again once there is more to report.
|||

Jaydog,

I look forward to what you find out.

John

|||

Jaydog,

I was curious if you had an update on this?

John

|||

Jaydog,

I would also be interested in finding out how that went.

JBW

Can replication be ran on a MS sharepoint database?

Hello,

I was curious if you can have merge and/or snapshot replication setup on a MS sharepoint database? Thanks.

John

I don't think that those will work for the following reason.

At a recent MS course, we asked our instructor if it was possible to change the content database in SP central Admin to a secondary database server during a crisis. They advised that this might be a problem because the SP configuration database creates hard-coded entries referencing the database server name that it resides on. They suspsected that these entries would cause problems in the long run, if not right away.

We decided to test this out by creating a virtual environment that mimicked our production system. We then copied the database to a seperate physical machine. To try and avoid the configuration database problems, we created a new virtual site on the VM that mirrored our existing front end and pointed it to the new database to recreate the configuration database.

Everything worked okay for about 3 hours then we began to see timeouts due to maxpoolsize being reached. When we checked the event log we also found that the server was STILL trying to connect to the original database even though everything was on the new server. Eventually, it became impossible to access any of the sites in the virtual environment.

I imagine that these entries in the configuration database would cause the same problem if we were to replicate from one server to another. We're now thinking of using log shipping or clustering to try and provide the redundancy we're looking for.

|||

Jaydog,

Thanks for your reply.

John

|||Hey John,

Looks like I was premature with my first response. We tried the same thing one more time, except that we shut down the old SQL Server before clearing the old configuration database and allowing SharePoint to rebuild it. So far, it looks like everything is up and running.

We're giving it a few days to see if there are any adverse effects and then we will be trying to get merge replication up and running. I'll post again once there is more to report.|||

Jaydog,

I look forward to what you find out.

John

|||

Jaydog,

I was curious if you had an update on this?

John

|||

Jaydog,

I would also be interested in finding out how that went.

JBW

can Password change affecting replication

Hi all,
Wew have a need to change the sa password of all our servers. How can change
in the sa password affect the replication.
RegarDs,
JerriN
It all depends It will have an impact if you are using SQL authentication
for any of the agents. If it is a typical setup, the replication agents will
run under the impersonation of the SQL Server agent's login, which is
typically the same login that SQL Server itself uses, so there should be no
impact at all.
HTH,
Paul Ibison

can only set up snapshot & merge replication

Hi ,
I found that i could only set up a db in the server as either a
snapshot/merger replication with the transactional replication greyed out
any reason why i am not able to make it a transactional replication ?
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
are you running msde?
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
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:587958988abb4@.uwe...
> Hi ,
> I found that i could only set up a db in the server as either a
> snapshot/merger replication with the transactional replication greyed out
>
> any reason why i am not able to make it a transactional replication ?
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200512/1
|||... or personal edition?
Paul Ibison
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23lC3Vey%23FHA.3068@.TK2MSFTNGP09.phx.gbl...
> are you running msde?
> --
> 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
> "maxzsim via droptable.com" <u14644@.uwe> wrote in message
> news:587958988abb4@.uwe...
>
|||Hi ,
i am running sql server 2000 standard edition
rdgs
Paul Ibison wrote:[vbcol=seagreen]
>... or personal edition?
>Paul Ibison
>[quoted text clipped - 6 lines]
Message posted via http://www.droptable.com
|||Is there an error when you run this:
sp_dboption @.dbname = 'database'
, @.optname = 'published'
, @.optvalue = 'true'
Or are you possibly referring to the articles being greyed out (this is
because of a lack of a Primary key).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
there's no error and i got "Published" returned
tks & rdgs
Paul Ibison wrote:
>Is there an error when you run this:
>sp_dboption @.dbname = 'database'
> , @.optname = 'published'
> , @.optvalue = 'true'
>Or are you possibly referring to the articles being greyed out (this is
>because of a lack of a Primary key).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
>http://www.nwsu.com/0974973602p.html)
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Please can you run the following code in your database. All you need to do
is a find and replace - change Region to a table name that has a PK. If
there are any messages, please post them back.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
-- Adding the transactional publication
exec sp_addpublication @.publication = N'TestPublication', @.restricted =
N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
= 'test', @.status = N'active', @.allow_push = N'true', @.allow_pull = N'true',
@.allow_anonymous = N'false', @.enabled_for_internet = N'false',
@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran
= N'false', @.autogen_sync_procs = N'false', @.retention = 336,
@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_dts = N'false', @.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.logreader_job_name = N'TestPI-4'
exec sp_addpublication_snapshot @.publication =
N'TestPublication',@.frequency_type = 4, @.frequency_interval = 1,
@.frequency_relative_interval = 0, @.frequency_recurrence_factor = 1,
@.frequency_subday = 1, @.frequency_subday_interval = 0, @.active_start_date =
0, @.active_end_date = 0, @.active_start_time_of_day = 230000,
@.active_end_time_of_day = 0, @.snapshot_job_name =
N'TestPI-TestPublication-11'
GO
exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
N'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @.publication = N'TestPublication', @.article = N'Region',
@.source_owner = N'dbo', @.source_object = N'Region', @.destination_table =
N'Region', @.type = N'logbased', @.creation_script = null, @.description =
null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
@.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_Region', @.del_cmd = N'CALL sp_MSdel_Region', @.upd_cmd = N'MCALL
sp_MSupd_Region', @.filter = null, @.sync_object = null, @.auto_identity_range
= N'false'
GO
|||Hi Paul , when i ran ur script against the db that i want to create the
publication i got the following.
i am not sure abt the part that this edition does not support because i am
using the standard edition or how can i check the edition of my SQL Server ?
could be service pack issue ?
================================================== =====
Server: Msg 21108, Level 16, State 1, Procedure sp_addpublication, Line 275
This edition of SQL Server does not support transactional publications.
Server: Msg 15001, Level 11, State 1, Procedure sp_addpublication_snapshot,
Line 117
Object 'TestPublication' does not exist or is not a valid object for this
operation.
Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
42
The publication 'TestPublication' does not exist.
Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
42
The publication 'TestPublication' does not exist.
Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
42
The publication 'TestPublication' does not exist.
Server: Msg 14027, Level 11, State 1, Procedure sp_addarticle, Line 478
TestPublication does not exist in the current database.
================================================== =====
tks & rdgs
Paul Ibison wrote:
>Please can you run the following code in your database. All you need to do
>is a find and replace - change Region to a table name that has a PK. If
>there are any messages, please post them back.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
>http://www.nwsu.com/0974973602p.html)
>-- Adding the transactional publication
>exec sp_addpublication @.publication = N'TestPublication', @.restricted =
>N'false', @.sync_method = N'native', @.repl_freq = N'continuous', @.description
>= 'test', @.status = N'active', @.allow_push = N'true', @.allow_pull = N'true',
>@.allow_anonymous = N'false', @.enabled_for_internet = N'false',
>@.independent_agent = N'false', @.immediate_sync = N'false', @.allow_sync_tran
>= N'false', @.autogen_sync_procs = N'false', @.retention = 336,
>@.allow_queued_tran = N'false', @.snapshot_in_defaultfolder = N'true',
>@.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
>@.allow_dts = N'false', @.allow_subscription_copy = N'false',
>@.add_to_active_directory = N'false', @.logreader_job_name = N'TestPI-4'
>exec sp_addpublication_snapshot @.publication =
>N'TestPublication',@.frequency_type = 4, @.frequency_interval = 1,
>@.frequency_relative_interval = 0, @.frequency_recurrence_factor = 1,
>@.frequency_subday = 1, @.frequency_subday_interval = 0, @.active_start_date =
>0, @.active_end_date = 0, @.active_start_time_of_day = 230000,
>@.active_end_time_of_day = 0, @.snapshot_job_name =
>N'TestPI-TestPublication-11'
>GO
>exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
>N'BUILTIN\Administrators'
>GO
>exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
>N'distributor_admin'
>GO
>exec sp_grant_publication_access @.publication = N'TestPublication', @.login =
>N'sa'
>GO
>-- Adding the transactional articles
>exec sp_addarticle @.publication = N'TestPublication', @.article = N'Region',
>@.source_owner = N'dbo', @.source_object = N'Region', @.destination_table =
>N'Region', @.type = N'logbased', @.creation_script = null, @.description =
>null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
>@.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
>sp_MSins_Region', @.del_cmd = N'CALL sp_MSdel_Region', @.upd_cmd = N'MCALL
>sp_MSupd_Region', @.filter = null, @.sync_object = null, @.auto_identity_range
>= N'false'
>GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Hi
i am not too sure where to check the edition of the SQL server but below is
the version of the SQL from "SELECT @.@.version"
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
tks & rdgs
maxzsim wrote:[vbcol=seagreen]
>Hi Paul , when i ran ur script against the db that i want to create the
>publication i got the following.
>i am not sure abt the part that this edition does not support because i am
>using the standard edition or how can i check the edition of my SQL Server ?
>could be service pack issue ?
>================================================= ======
>Server: Msg 21108, Level 16, State 1, Procedure sp_addpublication, Line 275
>This edition of SQL Server does not support transactional publications.
>Server: Msg 15001, Level 11, State 1, Procedure sp_addpublication_snapshot,
>Line 117
>Object 'TestPublication' does not exist or is not a valid object for this
>operation.
>Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
>42
>The publication 'TestPublication' does not exist.
>Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
>42
>The publication 'TestPublication' does not exist.
>Server: Msg 20026, Level 16, State 1, Procedure sp_MSpublication_access, Line
>42
>The publication 'TestPublication' does not exist.
>Server: Msg 14027, Level 11, State 1, Procedure sp_addarticle, Line 478
>TestPublication does not exist in the current database.
>================================================= ======
>tks & rdgs
>[quoted text clipped - 45 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
|||You're using MSDE (not 'Standard').
You'll need to change edition of SQL Server.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Can not replicate DDL changes

Merge replication. We switched a publication over from push to pull and are now initiating everything within an application. We have just encountered a situation where it is now completely impossible to replicate DDL.

When this was a push subscription, we could execute the following and it would fly straight through the engine and hit every subscriber without having to do anything at all:

ALTER TABLE <tablename>

ADD <columnname> <datatype> NULL

Now that it is a pull subscription when I issue an ALTER TABLE and add a nullable column to the end of the table, it does NOT replicate at ALL. We get the following error message:

The schema definition of the destination table 'dbo'.'Player' in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199478)
Get help: http://help/MSSQL_REPL-2147199478

The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199481)
Get help: http://help/MSSQL_REPL-2147199481

It apears that we are now required to either reinitialize every subscriber every time we add a column or we are required to first distribute the DDL change to each subscriber, make sure they all have it, then add it to the publisher before anyone replicates, and then reinit every single one of them without a snapshot. This makes absolutely no sense at all.

The interesting thing is that we can add articles at will and those get applied with absolutely no problems at all to the subscribers without having to do anything other than add the article and generate a new snapshot.

Version 9.00.3042.00

I am experiencing the same problem. It appears to only occur if there are both data and metadata changes to the replicated table. Has that been your experience?|||

No. We have this problem even if only DDL needs to replicate.

There is a KB artilcle of a bug with DDL replication that is related to kicking synchronization off via Replication Monitor, but that does not apply here either. We are initiating it using RMO from the client and have verified that it is not an RMO issue by directly launching it from the command line using replmerg.exe. We have the latest service pack and hotfixes applies. There is nothing that we can find that narrows this down at this point.

DDL plain and simply does not replicate. We have built this on a test environment using a centrally manage configuration, aka push, and all DDL replicates without any problems. We then dropped the push subscribers off and configured pull subscribers to this same publication and at that point DDL refuses to replicate via any mechanism.

The only way we can get DDL changes shoved down to a subscriber is to do one of the following:

1. Move the DDL change to a new table and add that as a new article

2. Drop the existing article from the publication, have everyone synch to remove it from their machines, apply the DDL change, add the article back in, have everyone synch to get the incremental snapshot and add the table back with the updated schema

3. Reinitialize all of the subscribers

Every one of those options is horrible, for obvious reasons when you are dealing with hundreds of subscribers moving all over the globe.

|||I am using push subscriptions, initializing the sync using RMO. I continue to see the problem when the subscriptions are set to reinitialize. The workaround supplied by the error message works but is impractical.|||We aren't reinitializing ours. They are just blowing up this way. In our case, you can't do what is suggested in the error message, because schema changes are not allowed on the subscriber.|||Is there any official response to this problem?|||Nope. Since we can't even get the support case moving forward, we are taking the alternative approach of blowing away each of the publications, issuing the DDL that we need, putting the publication back together and then reinitializing. We've reverted our application design and deployment to what I've been using for the past decade, prior to SQL Server 2005. I no longer trust the DDL replication component and will not use it. This creates a major bottleneck in our ability to deliver solutions quickly as well as enhance existing solutions, but we are left with no choice.

Can not replicate DDL changes

Merge replication. We switched a publication over from push to pull and are now initiating everything within an application. We have just encountered a situation where it is now completely impossible to replicate DDL.

When this was a push subscription, we could execute the following and it would fly straight through the engine and hit every subscriber without having to do anything at all:

ALTER TABLE <tablename>

ADD <columnname> <datatype> NULL

Now that it is a pull subscription when I issue an ALTER TABLE and add a nullable column to the end of the table, it does NOT replicate at ALL. We get the following error message:

The schema definition of the destination table 'dbo'.'Player' in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199478)
Get help: http://help/MSSQL_REPL-2147199478

The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199481)
Get help: http://help/MSSQL_REPL-2147199481

It apears that we are now required to either reinitialize every subscriber every time we add a column or we are required to first distribute the DDL change to each subscriber, make sure they all have it, then add it to the publisher before anyone replicates, and then reinit every single one of them without a snapshot. This makes absolutely no sense at all.

The interesting thing is that we can add articles at will and those get applied with absolutely no problems at all to the subscribers without having to do anything other than add the article and generate a new snapshot.

Version 9.00.3042.00

I am experiencing the same problem. It appears to only occur if there are both data and metadata changes to the replicated table. Has that been your experience?|||

No. We have this problem even if only DDL needs to replicate.

There is a KB artilcle of a bug with DDL replication that is related to kicking synchronization off via Replication Monitor, but that does not apply here either. We are initiating it using RMO from the client and have verified that it is not an RMO issue by directly launching it from the command line using replmerg.exe. We have the latest service pack and hotfixes applies. There is nothing that we can find that narrows this down at this point.

DDL plain and simply does not replicate. We have built this on a test environment using a centrally manage configuration, aka push, and all DDL replicates without any problems. We then dropped the push subscribers off and configured pull subscribers to this same publication and at that point DDL refuses to replicate via any mechanism.

The only way we can get DDL changes shoved down to a subscriber is to do one of the following:

1. Move the DDL change to a new table and add that as a new article

2. Drop the existing article from the publication, have everyone synch to remove it from their machines, apply the DDL change, add the article back in, have everyone synch to get the incremental snapshot and add the table back with the updated schema

3. Reinitialize all of the subscribers

Every one of those options is horrible, for obvious reasons when you are dealing with hundreds of subscribers moving all over the globe.

|||I am using push subscriptions, initializing the sync using RMO. I continue to see the problem when the subscriptions are set to reinitialize. The workaround supplied by the error message works but is impractical.|||We aren't reinitializing ours. They are just blowing up this way. In our case, you can't do what is suggested in the error message, because schema changes are not allowed on the subscriber.|||Is there any official response to this problem?|||Nope. Since we can't even get the support case moving forward, we are taking the alternative approach of blowing away each of the publications, issuing the DDL that we need, putting the publication back together and then reinitializing. We've reverted our application design and deployment to what I've been using for the past decade, prior to SQL Server 2005. I no longer trust the DDL replication component and will not use it. This creates a major bottleneck in our ability to deliver solutions quickly as well as enhance existing solutions, but we are left with no choice.

can not remove rowguid currently replicated

After losing my publishing server, I re-built on a new server.
The databases had replication remnants so I ran cleanup scripts
(Replication system Objects, indexes and rowguids)
I then re-built replication
I find that I have a column (rowguid) left over that I can not remove.
The error message is "Can not Alter table, ... rowguid currently replicated"
how do I remove this column?
Haven't seen this error personally, but please have a look at the solutions
proposed in this thread for some ideas to take advantage of:
http://www.webservertalk.com/showthread.php?t=902849
HTH,
Paul Ibison
|||Hi Paul;
This is what I have done so far
Problem:
Open EM
Open Agency Table
Delete Column
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
ALTER TABLE DROP COLUMN failed because 'rowguid' is currently replicated.
Approaches:
Drop replicated column - sp_repldropcolumn
Drop subscription - sp_dropsubscription
drop publication - sp_droppublication
disable publishing and distributor - sp_removedbreplication
exec sp_repldropcolumn @.source_object = 'Agency'
, @.column = 'rowguid'
, @.force_reinit_subscription = 1
Server: Msg 21246, Level 16, State 1, Procedure sp_repldropcolumn, Line 212
This step failed because table 'Agency' is not part of any publication.
sp_dropsubscription @.publication = 'IsoprepArchive'
, @.subscriber = 'SQLDEV'
, @.destination_db = 'Archivedisopreps'
sp_droppublication @.publication = 'IsoprepArchive'
exec sp_removedbreplication 'Isoprep'
-- Unmark table for replication
SELECT 'exec sp_MSUnmarkReplInfo ' + '''' + Name + '''' + Char(13)
+ ' GO ' + Char(13)
+ 'ALTER TABLE ' + Name + CHAR(13)
+ 'DROP CONSTRAINT DF_' + Name + '_rowguid' + Char(13)
+ ' GO ' + CHAR(13)
+ 'ALTER TABLE ' + Name + CHAR(13)
+ 'DROP COLUMN ROWGUID' + Char(13)
+ ' GO ' + Char(13) + Char(13)
FROM sysobjects
WHERE xtype = 'U'
-- return replinfo flag
SELECT 'Print ' + '''' + Name + '''' + Char(13) + ' GO '
+ Char(13)
+ 'SELECT replinfo' + Char(13)
+ 'FROM sysobjects' + Char(13)
+ ' WHERE name = ' + '''' + name + '''' + Char(13)
FROM sysobjects
WHERE xtype = 'U'

exec sp_MSUnmarkReplInfo 'ErrorLog'
GO
ALTER TABLE ErrorLog
DROP CONSTRAINT DF_ErrorLog_rowguid
GO
ALTER TABLE ErrorLog
DROP COLUMN ROWGUID
GO
Warning: The table 'ErrorLog' has been created but its maximum row size
(9416)
exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes.
Server: Msg 4932, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'ROWGUID' is currently replicated.
Warning: The table 'ErrorLog' has been created but its maximum row size
(9416)
exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes.
Thanks
MJS
"Paul Ibison" wrote:

> Haven't seen this error personally, but please have a look at the solutions
> proposed in this thread for some ideas to take advantage of:
> http://www.webservertalk.com/showthread.php?t=902849
> HTH,
> Paul Ibison
>
|||Quite By Accident, I found the following:
open EM
Open Design view of table
open constraint tab
Look for replication constraints
I found a constraint that looked like:
"repl_identity_range_sub_B0BB3703_3C1D_4648_9DCA_B F47DE69E485"
execute the following to build an alter table statement that will
remove the constraints.
SELECT 'ALTER TABLE ' + U.name + CHAR(13)
+ 'DROP CONSTRAINT ' + C.name + CHAR(13)
+ CHAR(13) + 'GO' + CHAR(13)
FROM sysobjects C
, sysobjects U
WHERE C.parent_Obj = U.Id
AND C.xtype = 'C'
AND C.name like 'repl_identity_range_sub_%'
Thanks
MJ
"mj" wrote:
[vbcol=seagreen]
> Hi Paul;
> This is what I have done so far
> Problem:
> Open EM
> Open Agency Table
> Delete Column
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> ALTER TABLE DROP COLUMN failed because 'rowguid' is currently replicated.
> Approaches:
> Drop replicated column - sp_repldropcolumn
> Drop subscription - sp_dropsubscription
> drop publication - sp_droppublication
> disable publishing and distributor - sp_removedbreplication
>
> exec sp_repldropcolumn @.source_object = 'Agency'
> , @.column = 'rowguid'
> , @.force_reinit_subscription = 1
> Server: Msg 21246, Level 16, State 1, Procedure sp_repldropcolumn, Line 212
> This step failed because table 'Agency' is not part of any publication.
> sp_dropsubscription @.publication = 'IsoprepArchive'
> , @.subscriber = 'SQLDEV'
> , @.destination_db = 'Archivedisopreps'
> sp_droppublication @.publication = 'IsoprepArchive'
> exec sp_removedbreplication 'Isoprep'
> -- Unmark table for replication
> SELECT 'exec sp_MSUnmarkReplInfo ' + '''' + Name + '''' + Char(13)
> + ' GO ' + Char(13)
> + 'ALTER TABLE ' + Name + CHAR(13)
> + 'DROP CONSTRAINT DF_' + Name + '_rowguid' + Char(13)
> + ' GO ' + CHAR(13)
> + 'ALTER TABLE ' + Name + CHAR(13)
> + 'DROP COLUMN ROWGUID' + Char(13)
> + ' GO ' + Char(13) + Char(13)
> FROM sysobjects
> WHERE xtype = 'U'
> -- return replinfo flag
> SELECT 'Print ' + '''' + Name + '''' + Char(13) + ' GO '
> + Char(13)
> + 'SELECT replinfo' + Char(13)
> + 'FROM sysobjects' + Char(13)
> + ' WHERE name = ' + '''' + name + '''' + Char(13)
> FROM sysobjects
> WHERE xtype = 'U'
>
> ----
> exec sp_MSUnmarkReplInfo 'ErrorLog'
> GO
> ALTER TABLE ErrorLog
> DROP CONSTRAINT DF_ErrorLog_rowguid
> GO
> ALTER TABLE ErrorLog
> DROP COLUMN ROWGUID
> GO
> Warning: The table 'ErrorLog' has been created but its maximum row size
> (9416)
> exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes.
> Server: Msg 4932, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN failed because 'ROWGUID' is currently replicated.
> Warning: The table 'ErrorLog' has been created but its maximum row size
> (9416)
> exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes.
>
> Thanks
> MJS
> ----
> "Paul Ibison" wrote:

can not load xpstar.dll

hi,
i have sql server sp3 running upon win 2000 adv server sp 4. the server is
configured for merge replication with another machine with same
configuration (sql server sp3 running upon win 2000 adv server sp 4). few
days back i recieved this error when i was trying to connect SQL server
remotely from one macine to the other.
Microsoft SQL-DMO(ODMC SQL State:42000) Error 0: cannot load the dll
xpstar.dll, or one of the DLLs it refferences. Reason: 126(the specified
module could not be found)
even if i could connect, i get the same error upon almost every activity on
the remote sql server. i found no reasonable solution to the problem on the
internet. finnally i uninsalled the sql server and then installed fresh sql
server and sp3. the problem was resolved. but afetr few days, i m facing
the same problem back again. i have repeated the same course of action.
i need to know any why this is happning so i could reach to the root of the
problem. installing back the sql server is not an optimal solution. need
help and fast.
thanx in advance.
Atif Chowhan
When you check you will find that xpstar.dll is actually there and loaded. The file missing is probably shfolder.dll. This folder resides in %systemRoot%\system32, copy it from any other server.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

can not load xpstar.dll

hi,
i have sql server sp3 running upon win 2000 adv server sp 4. the server is
configured for merge replication with another machine with same
configuration (sql server sp3 running upon win 2000 adv server sp 4). few
days back i recieved this error when i was trying to connect SQL server
remotely from one macine to the other.
Microsoft SQL-DMO(ODMC SQL State:42000) Error 0: cannot load the dll
xpstar.dll, or one of the DLLs it refferences. Reason: 126(the specified
module could not be found)
even if i could connect, i get the same error upon almost every activity on
the remote sql server. i found no reasonable solution to the problem on the
internet. finnally i uninsalled the sql server and then installed fresh sql
server and sp3. the problem was resolved. but afetr few days, i m facing
the same problem back again. i have repeated the same course of action.
i need to know any why this is happning so i could reach to the root of the
problem. installing back the sql server is not an optimal solution. need
help and fast.
thanx in advance.
Atif ChowhanWhen you check you will find that xpstar.dll is actually there and loaded. The file missing is probably shfolder.dll. This folder resides in %systemRoot%\system32, copy it from any other server.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.