replication error while copy data

Hi,
I have a transactional replication between SQL Server and Oracle. We already have the schema exist in both databases. So the the snapshut I check the 'If the table exists, leave it unchanged' box. But when the data start to load for the intial snapshut, I
got an error saying' The process encountered invalid column data in bcp file ...' for an existing table. How can me trace down which column has error? What is the data type for the Rowguid in my subscriber ( I do not have a rowguid column setup in the su
bscriber)?
Thanks
Hong,
was this error encountered when creating the snapshot file or when
distributing it to Oracle?
You can get some more (hopefully useful) info by logging:
http://support.microsoft.com/?id=312292
HTH,
Paul Ibison
|||It is encountered when distributing it to Oracle.
Hong Wang
"Paul Ibison" wrote:

> Hong,
> was this error encountered when creating the snapshot file or when
> distributing it to Oracle?
> You can get some more (hopefully useful) info by logging:
> http://support.microsoft.com/?id=312292
> HTH,
> Paul Ibison
>
>
|||OK, then try the logging I mentioned but I had hoped that it was a snapshot
agent error, as this is almost certainly an Oracle issue - mapping of
datatypes etc. Anyway, let's see if the logging returns anything.
Regards,
Paul Ibison
sql

Replication Error in Store Procedure that is not in Articles list

Hello,
I have a replication error related to the schema script of a procedure that
is
not longer on the list of articles to replicate from the publisher and also
it is
not part of the replicated store procedures in the replicas.
I someone has an idea what could it be the problem. Please let me know.
Thanks,
Car.
What is the error message you are getting.
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
"Car" <Car@.discussions.microsoft.com> wrote in message
news:ECA4299C-6D87-4F2B-B4C5-93039FCC0165@.microsoft.com...
> Hello,
> I have a replication error related to the schema script of a procedure
> that
> is
> not longer on the list of articles to replicate from the publisher and
> also
> it is
> not part of the replicated store procedures in the replicas.
> I someone has an idea what could it be the problem. Please let me know.
> Thanks,
> Car.
|||I delete the subscriptions and recreate them and I don't have any more this
message It was something like comErrorException(0x80004005). However when I
delete an article from the publisher. It does not work. it is not letting me
to do the schema changes I used to do before.
"Hilary Cotter" wrote:

> What is the error message you are getting.
> --
> 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
>
> "Car" <Car@.discussions.microsoft.com> wrote in message
> news:ECA4299C-6D87-4F2B-B4C5-93039FCC0165@.microsoft.com...
>
>

Replication error - schema

I am getting the error below trying the 1st merge synch to my laptop. The
laptop has MSDE2000 SP3 on it. I tried this synch right after I setup the
database as a publisher. This is the 1st subscriber I have tried to create.
Can anyone help? Thanks.
David
The schema script
'\\LIFEDEV02\d$\sqldata\MSSQL\ReplData\unc\LIFEDEV 02_MCFIData_MCFIData\20051129091321\vw_BillingDeta ilBalance_1465.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: DONAFAYE (Agent); Error number: 0)
------
Invalid object name 'dbo.vw_BillingPaid'.
(Source: DONAFAYE (Data source); Error number: 208)
------
I think that I read somewhere that all objects such as functions, triggers,
procs, etc.. have to be at the subscriber if there is a dependency AND if
they are not in your publication. If the referenced object is not in your
publication then you'll have to add it or manually create it at the
subscriber.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"David" wrote:

> I am getting the error below trying the 1st merge synch to my laptop. The
> laptop has MSDE2000 SP3 on it. I tried this synch right after I setup the
> database as a publisher. This is the 1st subscriber I have tried to create.
> Can anyone help? Thanks.
> David
> The schema script
> '\\LIFEDEV02\d$\sqldata\MSSQL\ReplData\unc\LIFEDEV 02_MCFIData_MCFIData\20051129091321\vw_BillingDeta ilBalance_1465.sch'
> could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> Unable to replicate a view or function because the referenced objects or
> columns are not present on the Subscriber.
> (Source: DONAFAYE (Agent); Error number: 0)
> ------
> Invalid object name 'dbo.vw_BillingPaid'.
> (Source: DONAFAYE (Data source); Error number: 208)
> ------
>
>
|||David,
stored procedures use deferred name resolution, so you can get away with
this sort of thing, but in the case of views it doesn't work - you'll need
to have vw_BillingPaid on the subscriber to be able to initialize. You could
drop the subscription, XXX the view into the publication then readd the
subscription. You could alternatively add vw_BillingPaid manually, but this
would have to be done for any other subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I'm confused. This is the FIRST subscription I am trying to create. The
view that is referred to DOES exist on the Publication. I do not understand
why I am getting this error.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23fvafr09FHA.912@.TK2MSFTNGP11.phx.gbl...
> David,
> stored procedures use deferred name resolution, so you can get away with
> this sort of thing, but in the case of views it doesn't work - you'll need
> to have vw_BillingPaid on the subscriber to be able to initialize. You
> could drop the subscription, XXX the view into the publication then readd
> the subscription. You could alternatively add vw_BillingPaid manually, but
> this would have to be done for any other subscribers.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
I also looked at the articles being published and ALL views are selected. I
tried dropping and re-creating the subscription, but it did the same thing
at the exact same place. I once had a similar error problem with DTS on a
different database but never was able to resolve it. I also tried your
suggestion to add the view vw_BillingPaid to the subscriber. I did it with
DTS and exported the view and it went over fine. Then I ran the synch
again, but got the exact same error. This is very frustrating. Any ideas
on what else I can do to make this work? I have 7 laptops to setup as
anonymous subscribers, but can't even get the very 1st one to work. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23fvafr09FHA.912@.TK2MSFTNGP11.phx.gbl...
> David,
> stored procedures use deferred name resolution, so you can get away with
> this sort of thing, but in the case of views it doesn't work - you'll need
> to have vw_BillingPaid on the subscriber to be able to initialize. You
> could drop the subscription, XXX the view into the publication then readd
> the subscription. You could alternatively add vw_BillingPaid manually, but
> this would have to be done for any other subscribers.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
I think your dependency information is corrupted. If you add the view to the
subscriber manually and meanwhile drop it from the publication it should be
ok. The current problem is that it is still in the publication and is
therefore being dropped.
Alternatively, run sp_depends on the view that errors to check that the
dependency is not acknowledged by SQL Server. If this shows no dependencies
then refresh the view and run it again to check the dependency is
recognised, and once this is the case, the snapshot article order should be
correct.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You were right. I dropped the pub and subscriber and dropped and re-created
the 2 views and checked their dependencies. Both now appear ok. Then I
tried publication and the create subscriber again and got error on a
different view. Same error. I will remove and recreate everything again!
Is there any way to check all views at once for corrupt dependencies? (I'm
guessing no). Also, any idea how this could happen to so many views?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O8FdSaQ%23FHA.3340@.TK2MSFTNGP12.phx.gbl...
> David,
> I think your dependency information is corrupted. If you add the view to
> the subscriber manually and meanwhile drop it from the publication it
> should be ok. The current problem is that it is still in the publication
> and is therefore being dropped.
> Alternatively, run sp_depends on the view that errors to check that the
> dependency is not acknowledged by SQL Server. If this shows no
> dependencies then refresh the view and run it again to check the
> dependency is recognised, and once this is the case, the snapshot article
> order should be correct.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||David,
these links show the sort of thing we're talking about:
http://support.microsoft.com/?id=115333
http://support.microsoft.com/?id=201846
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Transactional\Snapshot replication in SQL2005 (the SQL2005 snapshot agent
specifically) is much more resilient to inaccurate\missing information in
sysdepends or sys.sql_dependencies. For Merge replication users, they have
the capability to specify a custom ordering for the articles in SQL2005 to
overcome these kinds of problems.
-Raymond
"David" <dlchase@.lifetimeinc.com> wrote in message
news:OTAXiyR%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> You were right. I dropped the pub and subscriber and dropped and
> re-created the 2 views and checked their dependencies. Both now appear
> ok. Then I tried publication and the create subscriber again and got
> error on a different view. Same error. I will remove and recreate
> everything again! Is there any way to check all views at once for corrupt
> dependencies? (I'm guessing no). Also, any idea how this could happen to
> so many views?
> David
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:O8FdSaQ%23FHA.3340@.TK2MSFTNGP12.phx.gbl...
>

replication error

Hi,
I have configured transactional replication with one publisher and one subscriber.
after completion of schema and bcp operation , it has given message as "posting commands into the distribution database" after that timedout expired .
Now the error message is
"The concurrent snapshot for publication 'Siebel OLTP Transactional' has not been activated by the log reader agent" and schema in shared folder got deleted and snopshat agent started again.
Pls provide resolution
Thanks
Murali
Murali,
you could try increasing the -QUERYTIMEOUT parameter of the distribution
agent's profile. The second error might be resulting from the first, but
just in case it isn't I'd increase the -QUERYTIMEOUT of the logreader also.
I'm not too sure where your post title fits in - "Space is not available in
TempDB" - did you also see this error message? Obviously in this case TempDB
needs increasing in size.
HTH,
Paul Ibison
|||I'd try setting the QueryTimeout parameter in the Snapshot agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23tDHzD3YEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Murali,
> you could try increasing the -QUERYTIMEOUT parameter of the distribution
> agent's profile. The second error might be resulting from the first, but
> just in case it isn't I'd increase the -QUERYTIMEOUT of the logreader
also.
> I'm not too sure where your post title fits in - "Space is not available
in
> TempDB" - did you also see this error message? Obviously in this case
TempDB
> needs increasing in size.
> HTH,
> Paul Ibison
>

replication and schema change

Hello everyone!
We are software developement company. We are planning to setup replication
for few our customers. But our software is still under developement and
database schema is changing quite often.
What is the best, fastest and easiest way to implement this database chnages
at our "replication" customers?
Uros
Uros,
sp_repladdcolumn and sp_repldropcolumn cater for a lot of schema changes. To
alter a column take a look at this article:
http://www.replicationanswers.com/AddColumn.asp. On SQL Server 2005 things
are easier: http://www.replicationanswers.com/AlterSchema2005.asp.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||An alternative method which allows for more than just adding or removing 1
column at a time...
--Drop Subscription & Article
exec sp_dropsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
exec sp_droparticle @.publication = 'fxDB6_Pub1'
, @.article = 'MyTable'
-- Make DDL changes
ALTER TABLE MyTable
ALTER COLUMN...
-- Add article
exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
@.source_owner = N'dbo'
, @.source_object = N'MyTable', @.destination_table = N'MyTable', @.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_MyTable',
@.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
@.filter = null
, @.sync_object = null, @.auto_identity_range = N'false'
-- Add Subscription(s)
exec sp_addsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
, @.destination_db = 'SubscrDBName'
, @.sync_type = 'automatic'
-- Start Snapshot agent - creates snapshot only for MyTable article
-- Distribution agents will ship to subscribers
Another solution for changes to many tables:
- drop all subscriptions
- drop publication
- make changes
- re-create publication, with script of course (ui takes too long w/ many
tables)
- add subscriptions
- fire up agents...
Regards,
ChrisB
www.MyDatabaseAdmin.com
"uros" wrote:

> Hello everyone!
> We are software developement company. We are planning to setup replication
> for few our customers. But our software is still under developement and
> database schema is changing quite often.
> What is the best, fastest and easiest way to implement this database chnages
> at our "replication" customers?
> Uros
|||Chris - Good suggestion. However, "@.schema_option =
0x00000000000000F3" can be a different value depending on the options one
may have chosen through the interface during the first attempt for the setup.
-A
"Chris" wrote:
[vbcol=seagreen]
> An alternative method which allows for more than just adding or removing 1
> column at a time...
> --Drop Subscription & Article
> exec sp_dropsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> exec sp_droparticle @.publication = 'fxDB6_Pub1'
> , @.article = 'MyTable'
> -- Make DDL changes
> ALTER TABLE MyTable
> ALTER COLUMN...
> -- Add article
> exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
> @.source_owner = N'dbo'
> , @.source_object = N'MyTable', @.destination_table = N'MyTable', @.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_MyTable',
> @.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
> @.filter = null
> , @.sync_object = null, @.auto_identity_range = N'false'
> -- Add Subscription(s)
> exec sp_addsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> , @.destination_db = 'SubscrDBName'
> , @.sync_type = 'automatic'
> -- Start Snapshot agent - creates snapshot only for MyTable article
> -- Distribution agents will ship to subscribers
> Another solution for changes to many tables:
> - drop all subscriptions
> - drop publication
> - make changes
> - re-create publication, with script of course (ui takes too long w/ many
> tables)
> - add subscriptions
> - fire up agents...
> Regards,
> ChrisB
> www.MyDatabaseAdmin.com
> "uros" wrote:

Replication / Restores and initialize schema question

I have a database with 3 added filegroups/files. The restore with move,
moves the logical filename to a specifiic physical file.
Once restored the db objects should be restored to their proper filegroups.
Once I run the snapshot, can all of the schema and data be pushed to the
PRIMARY/DEFAULT filegroup?
Because this is what appears to be happening, all objects are being pushed
into the PRIMARY/DEFAULT filegroup.
In published db, objects are in their correct filegroups, in the subscriber
db, they are all in the PRIMARY/DEFAULT.
Does this sound right... I posted a database restore question to the server
ng. I'm wondering if replication could cause this.
This doesn't make any sense to me...
no, you will have to then apply your schema through a pre-snapshot command,
and ensure you use the delete existing data in the snapshot tab of the table
article properties.
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
"frankm" <frankm@.nospam.postalias> wrote in message
news:OwVk18MKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> I have a database with 3 added filegroups/files. The restore with move,
> moves the logical filename to a specifiic physical file.
> Once restored the db objects should be restored to their proper
filegroups.
> Once I run the snapshot, can all of the schema and data be pushed to the
> PRIMARY/DEFAULT filegroup?
> Because this is what appears to be happening, all objects are being pushed
> into the PRIMARY/DEFAULT filegroup.
> In published db, objects are in their correct filegroups, in the
subscriber
> db, they are all in the PRIMARY/DEFAULT.
> Does this sound right... I posted a database restore question to the
server
> ng. I'm wondering if replication could cause this.
> This doesn't make any sense to me...
>
|||Then it sounds like replication is actually pushing the schema and data to
the PRIMARY/DEFAULT filegroup.
I'm still trying to understand why it does that?
Looking at the "delete existing data" selection - this appears to be per
table.
Is there a way to set this globally? I'd hate to select that for 700 tables
individually.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uo8FoNNKFHA.2936@.TK2MSFTNGP15.phx.gbl...
> no, you will have to then apply your schema through a pre-snapshot
command,
> and ensure you use the delete existing data in the snapshot tab of the
table[vbcol=seagreen]
> article properties.
> --
> 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
> "frankm" <frankm@.nospam.postalias> wrote in message
> news:OwVk18MKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> filegroups.
pushed
> subscriber
> server
>
|||oops - should have looked further down - looks like I can set it globally in
"Article Defaults"
"frankm" <frankm@.nospam.postalias> wrote in message
news:O9RjwUNKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Then it sounds like replication is actually pushing the schema and data to
> the PRIMARY/DEFAULT filegroup.
> I'm still trying to understand why it does that?
> Looking at the "delete existing data" selection - this appears to be per
> table.
> Is there a way to set this globally? I'd hate to select that for 700
tables[vbcol=seagreen]
> individually.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uo8FoNNKFHA.2936@.TK2MSFTNGP15.phx.gbl...
> command,
> table
move,[vbcol=seagreen]
the
> pushed
>

Replication & NDF

If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
JLS,
I'd say 'it depends' Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||" I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher."
Perfect, and exactly what I thought!!!! I knew this guy didn't have a clue as to what he was doing, and you just confirmed this for me. I read the exact same thing, and also hedged due to the fact that if the NDF becomes corrupt, the entire db is marked Suspect.....
Thanx once again Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:uwKaevZ2FHA.3964@.TK2MSFTNGP10.phx.gbl...
JLS,
I'd say 'it depends' Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
|||No, we do database backups using 3rd party tool, LiteSpeed.
So, I see no advantage to the .ndf & am planning to replicate without considering the .ndf
Thanx for your opinions & advice, I really appreciate it!!!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:OVvZ7ta2FHA.3156@.TK2MSFTNGP10.phx.gbl...
JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||Short answer is what I thought you would say, Thanx!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXFxh1c2FHA.3912@.TK2MSFTNGP15.phx.gbl...
I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!

Replication - Row not correctly sync

Hi Everyone!

I have a strange issue in my replicated databases.

I've a 5 merge subscribers replication schema. Our client discover a row that isn't matching with another database.

After some research it seam that the correct row is only at one database and all other database, have a the same row in a incoherent status.

Anyone have a idea on how this could happen?
I'm clue less right now

Thanks!

To fix the issue I decide to bring a change to the correct row to let it propagate to every other subcriber. But event more strange, not all collumn are updated to other subscriber!!!

This is really really strange

replication

Using merge replication, we made some schema changes to the publishing
database. The changes included the addition of a 1 column to to different
tables, a modification to a view and the addition of 3 contraints. To do this
we created a new publication.
When I created the subscription for the changes and ran the sychronization,
everything except the views were copied over.
Any idea why?
Thanks,
Gracie
Gracie,
Have a look at the article properties of the view to check it is set to drop
if exists.
Also, have a look at the snapshot history to see if the view definition was
created, and the snapshot files themselves if they haven't yet been cleaned
up.
BTW, unless there was a specific reason for having another publication, this
isn't mandatory, and if the new tables are related to existing tables from
the other publication, you might be better adding them there, so as to keep
PK-FK changes together in one synchronization.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication

undefined

Hi,

I have successfully replicated a database (type : Transactional), but when i have executed a Alter scripts to effect the schema of the database it worked. My concern is whether the replication was done successfully or not ?
Can we change the schema of a replicated database by plain Alter scripts ?
Plz let me know....

Thanks
RakeshDepending on what you altered. If you want to alter rules or foreign keys without checking replication, you don't have to do anything to the replication setup. But if you change table schma, you need to either use the replication system procedures to change it or you have to drop all subscriptions and initialize them.|||I am able to alter the table schema by using Replication stored procedures, but my concern is, i am also able to change the table schema by simple alter statements on the primary database, but these changes are not making into secondary database i,e subscriber database. Do we get any error Msg trying to alter a table schema of a replicated database ?|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.

Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?

-PatP|||Yes, you should get an error messge when you try to use an ALTER TABLE command against a replicated table. The ALTER TABLE should fail, but it sounds like it is working for you.

Is it possible that the table you are affecting isn't being replicated (either it was never included in a publication, or that its article has been dropped from the publication)?

-PatP

In case of merge replication it is not possible to alter repilcated table but for transaction replication it works. Welcome to Microsoft...

Replicating the schema while preserving existing data

Hello All,
Here is my situation:
I have a core database that is used to define the schema
of a client-owned database for use with some software. As
the software evolves and changes so too will the schema of
the core database. Ideally these changes should be
propogated down to the client-owned database;altering the
schema to match the new core version without destroying
the data that may already exists.
My attempts to use replication for this have failed when I
perform the following:
-The core database has a single table with three fields.
-The client database has nothing.
*I configure and activate replication
- Now the client database has an identical copy of the
table from the core database.
*I make changes to the core database schema by adding
three more fields to the table.
- Now the client database and core database have
differeing schemas.
*I add data to the client database table.
*I initiate replication from the core to the client
database expecting my three new fields to be appended to
the table without destroying my existing data.
-I don't get what I expect, instead I get a the original
three field table that was first replicated into the
client database and the data is missing.
How can I replicate a schema change from one database into
another without destroying any of the data that may
already exist on the target database?
Thanks,
Marc Deskins
marcdeskins@.hotmail.com
Marc,
you'll need to use sp_repladdcolumn, and after that there's no need to
reinitialize or do anything else, provided you haven't done a nosync
initialization. Just synchronizing will be enough.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick reply. I'll look into using
sp_repladdcolumn.
Marc Deskins
|||Paul,
Using sp_repladdcolumn was only a partial success. While
the newly added column is propogated down to the client
database from the core database the data that was added to
the client database before synchronizing is deleted.
What I am trying to do is have the schema changes to the
tables in the core database propogate down to the client
database without deleting data that was entered before
synchronization.
I understand this is sometimes unavoidable (dropped
columns etc.) so what I am concentrating on right now is
synchronizing the changes to the schema due to column
additions.
So if I have a table that looks like this in the client
database:
Field1|Field2|Field3
-Data-|-Data-|-Data-
-Data-|-Data-|-Data-
-Data-|-Data-|-Data-
And I make a change in the core database to the table so
it now looks like this:
Field1|Field2|Field3|Field4
Then I a synchronize the client and core databases so the
tables have matching schemas.
I want my newly synchronized table in the client database
to look like this:
Field1|Field2|Field3|Field4
-Data-|-Data-|-Data-|
-Data-|-Data-|-Data-|
-Data-|-Data-|-Data-|
Not like this (which is what it does now):
Field1|Field2|Field3|Field4
Is there something I am missing or can this not be done
using SQL Server 2000 replication?
Thanks,
Marc Deskins

>--Original Message--
>Marc,
>you'll need to use sp_repladdcolumn, and after that
there's no need to
>reinitialize or do anything else, provided you haven't
done a nosync
>initialization. Just synchronizing will be enough.
>HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>.
>
|||Marc,
something is definitely wrong here. Can you post up the table schema
(Field1|Field2|Field3) and script out the publication and post it up. Also,
please confirm that you are on sp3. I'll try to reproduce, because what
you're seeing shouldn't happen.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replicating Schema only

Hi,

I have a database that is being set up for merge replication (Sql 2005), but there is one table that I only want the schema replicating, not the data - I never want the data to be replicated in either direction. I can see from sp_addmergearticle that you can do something like this for sp's or functions but is it possible to do this for tables?

Regards

Graham

I'd just script out the object to a file and specify parameter @.post_snapshot_script to proc sp_addmergepublication.

Replicating Schema Changes

Hi there,

I'm using Sql 2005 merge replication and I have noticed something, I'm not sure if this is true or not:...

My publication is set to replicate schema changes (replicate_ddl = 1). Now, I have noticed that schema changes are only replicated if the current snapshot is valid. Is this right? If so why?

My next question carries on from the first. If I'm about to run a TSQL script on my publisher that will add a column or two to a published table, how do I ensure my snapshot is valid inorder for the ddl changes to be replcated? Should I be using:

EXEC sp_mergearticlecolumn

@.publication = <publicationname>,

@.article = <article name>,

@.force_invalidate_snapshot = 1,

@.force_reinit_subscription = 1

on each table I modifiy, after I have added the new column?

Thanks for your help

Graham

As for the first question, we don't have special requirement for snapshot. After DDL on publisher side, you just run merge agent, changes will be propagated to the subscriber.

As for the second question, how to ensure your snapshot is valid, you can just reinitiate subscriptions and regenerate snapshot, although it is not necessary for your ddl changes.

THanks

Yunjing

|||

Thanks for the reply Yunjing, but I can say for sure it seems to only replicate DDL in MERGE when the snapshot is current.

If I add a column to a table that has been published then look at the properties for the publication using SQL Management studio I can see the Articles. Drilling in to the articles reveals the new column, but when the snapshot is not current then this new column is not ticked to indicate it will be replicated.

Graham

|||Supposedly, merge agent should auto-detect if regenerating a new valid snapshot is necessary for propogating the ddl changes.|||

Hi Graham,

If you are using SQL Server 2005 for the publisher, subscriber, and distributor, then creating a merge publication with a table article that has replicate_ddl enabled should allow you to use the more robust schema change features built into replication. You are now able to use "Alter Table" syntax to physically add/remove columns from tables that are published for replication. There is no dependancy on snapshot to do this and the changes are propagated during the next sync with the subscriber. Check out the "How to: Replicate Schema Changes" topic in SQL Server 2005 Books Online. This is a new feature in SQL 2005.

As for the stored procedure you are executing above, sp_mergearticlecolumn. This stored procedure is used to vertically filter out or add columns to an existing table article. This is done at the logical layer in the article definition, not in the physical table definition. I believe you want to physically add a column to the table, so using Alter Table syntax is appropriate, not sp_mergearticlecolumn. Also, if the table you are publishing does not have any vertical filtering of columns, then when you add the column to the table via Alter Table syntax, the column will automatically get added to your table article definition with no extra effort.

Hope this helps and that I am interpreting the question correctly,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Tom,

you have interpreted it correctly. However, what I found is that SQL 2005 does correctly replicate any schema change done at the publisher with ALTER TABLE statement, but...

This is only true if the current snapshot is upto date - I used sp_helpmergepublication and look at the snapshot_ready column.

If however, the snapshot becomes out of date (I forced this by adding a new table, then adding a new merge article for that table before issuing an ALTER TABLE statement), then the merge agent does not replicate the new DDL, it continually says a new snapshot is required.

Does this make sense?

Graham

|||

Hi Tom,

I have now isolated the time when ALTER TABLE does NOT replicate schema changes.

I appears the if the publication uses row filtering on any of its articles / tables (and the filter uses HOT_NAME()), then using ALTER TABLE to add a new column does not mark this new column for publication in the existing table article.

Did you know this? If so, is there anyway I can get the new column added to my article for publication without removing the article and re-adding it?

Regards

Graham

|||

Hi Graham,

This is surprising.

Double check a couple items. The backward_comp_level should be 90 and replicate_ddl should be 1.

select backward_comp_level, replicate_ddl from sysmergepublications

Though I could not reproduce this issue, and I tried with all four row filtering partitioning options, here is something for you to try. Since the new column seems to be vertically partitioned out of the table article definition, now you should be able to use sp_mergearticlecolumn to add the column back into the table article. This will require the full snapshot to be regenerated because you are making a change to the schema of the pubished table article. This can be done without removing and re-adding the table article to the publication.

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Tom,

the results for the select statement return 90 and 1 respectively.

This is very bizzare. Can I ask you a question please? When you tried it did you do it through script (TSQL) or did you use the Sql Wizards?

If I create the publication and subscription using the wizards then it fails as I describe. If however I use TSQL to create everything it works as you expect and indeed found.

This is all sounding a bit strange!

Graham

|||

We did experienced same problems: "alter table" is not propagating the new column to sql server mobile database (and yes, we use hostname on row filtering).

What we see is that it is propragated if we create a new subscription...

strange for sure since it was working fine under SS2K (trough enterprise manager).

Thanks for your help.

Fab

|||

Hi guys,

some more info for you. I seem to be able to replicate this without a problem.

Using sp_helpmergearticle @.publication='<my publication>' I can predict if the schema will be replicated.

This sp will return the state of all articles for the given publication. Using the Status column I noted that after a snapshot is taken the status is 2 (Active) for my table. As soon as I add a subscription and initialize it then the status of the articles goes to 1 (Inactive). If an alter table statement is issued on the table when the status is 1, then the new column will NOT be part of the article and hence not replicated.

To fix it, I have to re-generate a new snapshot of the publisher BEFORE any alter table statements. Regenerating the snapshot seems to set the article status back to 2.

Does anyone know why this is happening? What does this Status mean on the articles?

Thanks

Graham

|||

Hi Graham!

Did you solve this problem?

It turned out that I've the same problem - the changes become *visible* just after I re-generate new snapshot.

It's very annoying, because if snapshot was not re-generated, we must re-init all subs :(

Paul

|||

Hi Paul,

sorry I never solved what was causing this. It became so unreliable that whenever we schema changes we drop replication :( then recreate the subscribers.

Graham

|||

I've just independently discovered this problem. It is exactly as Graham describes.

Is there anyone out there from Microsoft with anything to say about this? I'm starting to lose confidence in replication as a solution to anything other than the most trivial of problems.

|||

Can someone tell us what scenario you are getting into.

I see it is mentioned that the status of the article turns to 1 after adding a subscription and initializing it. I do not see this happening. What type of subscription is this? Client/Server, push/pull, local/remote, SQL Server 2000/SQL Server 2005?

If someone can give more information, I can try to reproduce this help with the resolution.

Replicating Schema Changes

Hi there,

I'm using Sql 2005 merge replication and I have noticed something, I'm not sure if this is true or not:...

My publication is set to replicate schema changes (replicate_ddl = 1). Now, I have noticed that schema changes are only replicated if the current snapshot is valid. Is this right? If so why?

My next question carries on from the first. If I'm about to run a TSQL script on my publisher that will add a column or two to a published table, how do I ensure my snapshot is valid inorder for the ddl changes to be replcated? Should I be using:

EXEC sp_mergearticlecolumn

@.publication = <publicationname>,

@.article = <article name>,

@.force_invalidate_snapshot = 1,

@.force_reinit_subscription = 1

on each table I modifiy, after I have added the new column?

Thanks for your help

Graham

As for the first question, we don't have special requirement for snapshot. After DDL on publisher side, you just run merge agent, changes will be propagated to the subscriber.

As for the second question, how to ensure your snapshot is valid, you can just reinitiate subscriptions and regenerate snapshot, although it is not necessary for your ddl changes.

THanks

Yunjing

|||

Thanks for the reply Yunjing, but I can say for sure it seems to only replicate DDL in MERGE when the snapshot is current.

If I add a column to a table that has been published then look at the properties for the publication using SQL Management studio I can see the Articles. Drilling in to the articles reveals the new column, but when the snapshot is not current then this new column is not ticked to indicate it will be replicated.

Graham

|||Supposedly, merge agent should auto-detect if regenerating a new valid snapshot is necessary for propogating the ddl changes.|||

Hi Graham,

If you are using SQL Server 2005 for the publisher, subscriber, and distributor, then creating a merge publication with a table article that has replicate_ddl enabled should allow you to use the more robust schema change features built into replication. You are now able to use "Alter Table" syntax to physically add/remove columns from tables that are published for replication. There is no dependancy on snapshot to do this and the changes are propagated during the next sync with the subscriber. Check out the "How to: Replicate Schema Changes" topic in SQL Server 2005 Books Online. This is a new feature in SQL 2005.

As for the stored procedure you are executing above, sp_mergearticlecolumn. This stored procedure is used to vertically filter out or add columns to an existing table article. This is done at the logical layer in the article definition, not in the physical table definition. I believe you want to physically add a column to the table, so using Alter Table syntax is appropriate, not sp_mergearticlecolumn. Also, if the table you are publishing does not have any vertical filtering of columns, then when you add the column to the table via Alter Table syntax, the column will automatically get added to your table article definition with no extra effort.

Hope this helps and that I am interpreting the question correctly,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Tom,

you have interpreted it correctly. However, what I found is that SQL 2005 does correctly replicate any schema change done at the publisher with ALTER TABLE statement, but...

This is only true if the current snapshot is upto date - I used sp_helpmergepublication and look at the snapshot_ready column.

If however, the snapshot becomes out of date (I forced this by adding a new table, then adding a new merge article for that table before issuing an ALTER TABLE statement), then the merge agent does not replicate the new DDL, it continually says a new snapshot is required.

Does this make sense?

Graham

|||

Hi Tom,

I have now isolated the time when ALTER TABLE does NOT replicate schema changes.

I appears the if the publication uses row filtering on any of its articles / tables (and the filter uses HOT_NAME()), then using ALTER TABLE to add a new column does not mark this new column for publication in the existing table article.

Did you know this? If so, is there anyway I can get the new column added to my article for publication without removing the article and re-adding it?

Regards

Graham

|||

Hi Graham,

This is surprising.

Double check a couple items. The backward_comp_level should be 90 and replicate_ddl should be 1.

select backward_comp_level, replicate_ddl from sysmergepublications

Though I could not reproduce this issue, and I tried with all four row filtering partitioning options, here is something for you to try. Since the new column seems to be vertically partitioned out of the table article definition, now you should be able to use sp_mergearticlecolumn to add the column back into the table article. This will require the full snapshot to be regenerated because you are making a change to the schema of the pubished table article. This can be done without removing and re-adding the table article to the publication.

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Tom,

the results for the select statement return 90 and 1 respectively.

This is very bizzare. Can I ask you a question please? When you tried it did you do it through script (TSQL) or did you use the Sql Wizards?

If I create the publication and subscription using the wizards then it fails as I describe. If however I use TSQL to create everything it works as you expect and indeed found.

This is all sounding a bit strange!

Graham

|||

We did experienced same problems: "alter table" is not propagating the new column to sql server mobile database (and yes, we use hostname on row filtering).

What we see is that it is propragated if we create a new subscription...

strange for sure since it was working fine under SS2K (trough enterprise manager).

Thanks for your help.

Fab

|||

Hi guys,

some more info for you. I seem to be able to replicate this without a problem.

Using sp_helpmergearticle @.publication='<my publication>' I can predict if the schema will be replicated.

This sp will return the state of all articles for the given publication. Using the Status column I noted that after a snapshot is taken the status is 2 (Active) for my table. As soon as I add a subscription and initialize it then the status of the articles goes to 1 (Inactive). If an alter table statement is issued on the table when the status is 1, then the new column will NOT be part of the article and hence not replicated.

To fix it, I have to re-generate a new snapshot of the publisher BEFORE any alter table statements. Regenerating the snapshot seems to set the article status back to 2.

Does anyone know why this is happening? What does this Status mean on the articles?

Thanks

Graham

|||

Hi Graham!

Did you solve this problem?

It turned out that I've the same problem - the changes become *visible* just after I re-generate new snapshot.

It's very annoying, because if snapshot was not re-generated, we must re-init all subs :(

Paul

|||

Hi Paul,

sorry I never solved what was causing this. It became so unreliable that whenever we schema changes we drop replication :( then recreate the subscribers.

Graham

|||

I've just independently discovered this problem. It is exactly as Graham describes.

Is there anyone out there from Microsoft with anything to say about this? I'm starting to lose confidence in replication as a solution to anything other than the most trivial of problems.

|||

Can someone tell us what scenario you are getting into.

I see it is mentioned that the status of the article turns to 1 after adding a subscription and initializing it. I do not see this happening. What type of subscription is this? Client/Server, push/pull, local/remote, SQL Server 2000/SQL Server 2005?

If someone can give more information, I can try to reproduce this help with the resolution.