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 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 someone answer my detach/attach replicated post ?

... I monkeyed around with the sysdatabases to detach and then to finally
attach.. Is it safe to do so ?
While this may work, its unsupported. I will probably break merge
replication.
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:etX7OJkYFHA.4024@.TK2MSFTNGP10.phx.gbl...
> ... I monkeyed around with the sysdatabases to detach and then to finally
> attach.. Is it safe to do so ?
>
|||Im using it for transactional replication.. I intend to stop the log reader
and the distribution agent(s) tied to that database before i proceed ..
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uUuZckkYFHA.2684@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> While this may work, its unsupported. I will probably break merge
> replication.
> --
> 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
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:etX7OJkYFHA.4024@.TK2MSFTNGP10.phx.gbl...
finally
>
|||What are you tryin to accomplish by doing this?
--
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
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3MDVClYFHA.1344@.TK2MSFTNGP15.phx.gbl...
> Im using it for transactional replication.. I intend to stop the log
> reader
> and the distribution agent(s) tied to that database before i proceed ..
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uUuZckkYFHA.2684@.TK2MSFTNGP09.phx.gbl...
> finally
>

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: