Adding a not null column to replicated table

Hi,

I'm merge replicating a SQL Server 2005 database (publisher) to SQL Compact databases (subscribers) on mobile devices. I understood that I could add a "not null" column to a replicated table on the server as long as I specified a default value, but it seems this is not possible. I ran the following script on the server database:

ALTER TABLE Activity ADD ActivityRequiresProject bit not null default(0)

which executed OK. When I went to synchronize the db on the mobile device I got the following error:

Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = alter table "Activity" add "ActivityRequiresProject" bit not NULL constraint "DF__Activity__Activi__4A47DDAE" default ( ( 0 ) ) ]

Does anyone know if this is a valid error? Is is possible to add a not null column with default, and if not how do I update the schema on a replicated database?

Regards,

Greg

I doubt this is a replication problem, but I don't have a SQL Compact db handy, can you try to create a new table on the compact db and run the TSQL command to see if SQL Compact even allows this?|||

Hi Greg,

Thanks for your reply. I created a new database on the device (not replicated), created a table "Activity", added some records and then executed the same script above in Query Analyzer and it worked fine. It only seems to be a problem when the command is run by the replication agent. Any ideas?

Regards,

Greg

|||can you tell me what version of ce you're using?|||

Hi,

I'm running SQL Server Compact (3.1) on Windows Mobile 5.0 (HP iPaq), and the server is SQL Server 2005 SP2.

Regards,

Greg

|||

Some extra information: I tried to get the subscriber to synchronize by reinitializing all subscriptions (with a new snapshot and "upload unsynchronized changes" set to yes). I had thought that this would regenerate the database at the subscriber after uploading subscriber changes, but it still comes up with the original error message. It's trying to add the not null column but seems to ignore the default constraint.

Any help on this would be greatly appreciated. At this stage I'm not really able to make any db schema changes on the server without deleting subscriber dbs and starting from scratch.

Regards,

Greg

|||We're trying to track down some answers for you, so please be patient.|||

This is a confirmed bug which we're trying to nail the root cause, thanks for raising this issue.

To get around your problem, you should do the following:

1. Reinitialize your subscriptions

2. Generate a new snapshot

3. Rerun your sync agents to apply the new snapshot

If you do this in order, you shouldn't hit the error because the new snapshot should generate a table with the new column. let me know if this still doesn't work.

|||

Thanks Greg,

As per my previous post, the main issue is that this problem is not resolved by reinitializing subscriptions with a new snapshot. I still get the error. The only solution I've found so far is to delete the subscriber database and synchronize again.

Regards,

Greg

|||

Hi,

This problem was seriously affecting the development and testing of this application, so I opened a support incident with Microsoft. The tech advised that I should use the stored procedure sp_repladdcolumn to add the column. It worked! Despite the documentation saying that this method is deprecated and should only be used with SQL Server 2000, it seems that this is only way to add a not null column with a default value to a SQL Server 2005 db table and have it successfully replicate to SQL Mobile/Compact (if anyone has any alternatives to this, please advise). This also seems to be a solution to another problem I've had with "could not enumerate changes at the subscriber" error when synchronizing after changing the db schema. Using the old stored procedures instead of "alter table" commands seems to fix this.

I hope MS will add a KB article about this so others won't have spend as much time as I have trying to get an answer.

Regards,

Greg

|||It's a bug in CE, not replication. Earlier I asked if you could run the alter table statement on your CE device to see if that failed, you said it passed but I think you ran it on your SQL Server 2005 server and not the CE. Running the alter table statement explicitly on the CE device will fail, if you remove the default name, then it works - CE should not choke like this. I'm glad you found a solution, a bug was already file, CE folks will be fixing this hopefully soon.

Add replication component

Hello,
I have a publication that is pushed from a SQL 2005 Standard box to
about 40 SQL express subscribers. One of the servers went down and was
rebuild and I reran the same install script as I ran on the other
servers ( I made the script). I pushed out the same subscription as
before to it, and it is running smoothly (transactional). I then
needed to set up a snapshot subscription to all servers, and this
server was the only one that would not allow me to. When I try and set
up a new subscription to it I get this message:
"Replication components are not installed on this server. Run SQL
Server SETUP again and select the option to install replication.
(Microsoft SQL Server, Error: 21028).
So I thought I would just redo the setup and only pick the replication
component, but you cant, it makes you pick the whole database engine
tree to get it, and then the install says that it is already installed
and won't let you continue. Same happens if I go through add/remove
programs and use the add component link.
Any thoughts or ideas on this error. Right now the only solution I
have is reinstall from scratch.
OK, that error message was totally wrong. Since adding it through the
UI was not working, I added another subscription through the UI and at
the end generated a script file instead of actually finishing it. I
then changed the script file to point at my stubborn server that
supposedly did not have replication components, and the script ran fine
and the subscription was created, and worked.
PT
|||Can you try to create a local publication on this server? I am not sure if
this message is erroneous or not.
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
"Paul T." <weluvpaul@.hotmail.com> wrote in message
news:1168454660.767978.320110@.p59g2000hsd.googlegr oups.com...
> Hello,
> I have a publication that is pushed from a SQL 2005 Standard box to
> about 40 SQL express subscribers. One of the servers went down and was
> rebuild and I reran the same install script as I ran on the other
> servers ( I made the script). I pushed out the same subscription as
> before to it, and it is running smoothly (transactional). I then
> needed to set up a snapshot subscription to all servers, and this
> server was the only one that would not allow me to. When I try and set
> up a new subscription to it I get this message:
> "Replication components are not installed on this server. Run SQL
> Server SETUP again and select the option to install replication.
> (Microsoft SQL Server, Error: 21028).
> So I thought I would just redo the setup and only pick the replication
> component, but you cant, it makes you pick the whole database engine
> tree to get it, and then the install says that it is already installed
> and won't let you continue. Same happens if I go through add/remove
> programs and use the add component link.
> Any thoughts or ideas on this error. Right now the only solution I
> have is reinstall from scratch.
>

Add new SP without reinitializing

I have stored procedures that I want to add to the publication and
have them passed down to the subscribers. I don't want to
reinitialized everything as this would bring the subscriber database
offline during the sync. I tried just creating the SP on the
publisher, but it did not pass down.
1) How do I add a single SP without reinitializing?
2) How do I alter an SP and pass it down without reinitializing?
I'm using SQL2005 SP2.
Thanks,
Peter Cwik
On Dec 20, 12:15 pm, PeterCwik <pjc...@.gmail.com> wrote:
> I have stored procedures that I want to add to the publication and
> have them passed down to the subscribers. I don't want to
> reinitialized everything as this would bring the subscriber database
> offline during the sync. I tried just creating the SP on the
> publisher, but it did not pass down.
> 1) How do I add a single SP without reinitializing?
> 2) How do I alter an SP and pass it down without reinitializing?
> I'm using SQL2005 SP2.
> Thanks,
> Peter Cwik
Sorry--I'm using Transaction replication
|||Try this,
Add the article with sp_addarticle and specify @.type = 'proc schema only' &
@.force_invalidate_snapshot = 1
Add subscription with sp_addsubscription, this may throw error Msg 14100 if
your publication is setup with @.sync_method= concurrent, solution is to set
the publication with @.sync_method='database snapshot' (only applicable to SQL
Server 2005 SP2) or workaround is to bypass this check by passing this
parameter @.reserved = N'internal' to sp_addsubscription
"PeterCwik" wrote:

> On Dec 20, 12:15 pm, PeterCwik <pjc...@.gmail.com> wrote:
> Sorry--I'm using Transaction replication
>
|||On Dec 25 2007, 3:32Xpm, Manoj <Ma...@.discussions.microsoft.com>
wrote:
> Try this,
> Add the article with sp_addarticle and specify @.type = X'proc schema only' &
> @.force_invalidate_snapshot = 1
> Add subscription with sp_addsubscription, this may throw error Msg 14100 if
> your publication is setup with @.sync_method= concurrent, solution is to set
> the publication with @.sync_method='database snapshot' (only applicable to SQL
> Server 2005 SP2) or workaround is to bypass this check by passing this
> parameter @.reserved = N'internal' to sp_addsubscription
>
> "PeterCwik" wrote:
>
>
> - Show quoted text -
Well that sort of helps. I know it may seem like an obvious question
but, what does @.force_invalidate_snapshot = 1 really do? What are the
implications of invalidating a snapshot?
Thanks,
Peter Cwik
|||On Jan 2, 12:49Xpm, PeterCwik <pjc...@.gmail.com> wrote:
> On Dec 25 2007, 3:32Xpm, Manoj <Ma...@.discussions.microsoft.com>
> wrote:
>
>
>
>
>
>
> Well that sort of helps. XI know it may seem like an obvious question
> but, what does @.force_invalidate_snapshot = 1 really do? XWhat are the
> implications of invalidating a snapshot?
> Thanks,
> Peter Cwik- Hide quoted text -
> - Show quoted text -
I think I may have answered my own question...I just need someone to
validate this.
1. Transactional replication is setup and working fine
2. The publisher is the Prod database and subscriber is Reporting
database
3. Power users are allowed to add stored procedures to Reporting
database so now the two database are close copies but not completely.
4. My concern was that a resync would kill all the user created
objects in the Reporting database
5. It appears this is not so. The terms Synchronize, Snapshot and
Reinitialize are very different and have different meanings, though
even in this forum they are used incorrectly interchangeably.
6. If a new object is created (table, sp, view, etc.) a new snapshot
must be created. Creating a new snapshot does not delete data in the
Reporting database.
7. If continuous synchronization is used, any "new" objects that were
included in the new snapshot will be replicated to the Reporting
database. If continuous synchronization is not used, then "Start"
synchrozination must be chosen.
8. In 2005 changes to objects in existing subscriptions will
automatically be pushed from Prod to Reporting.
9. The ONLY time data in the Reporting database will be whiped out
and a fresh copy brought down from the Prod server is when a
Reinitialize is run.
10. Reinitialize looks at a Snapshot and pushes data and objects from
Prod to Reporting.
11. Only a Generate a Snapshot and Sychronize is needed to move
additional objects from Prod to Reporting. Reinitialize in NOT
required for the "additional" objects.
Hilary, does this sound right?
Thanks,
Peter Cwik

Add new article to transactional replication

This one is driving me crazy...
We are using Transactional Replication with the subscribers initialized from
a backup (no snapshot). It's working great, but now we need to add several
new articles to the publication and have them picked up in the subscribers.
The only way I can get this to work is to drop the subscriber, backup the
published database, restore the previously made backup over the destination
databases and then re-create the subscriptions. There has to be a better
way?
I've tried using the sp_reinitializesubscription, but that doesn't work
since we aren't using a snapshot. I've tried skipping the restore, and just
creating the tables in both the source and destination databases. Doing
this, the subscription is re-created sucessfully, but data in the new
articles is not actually being replicated.
Replication isn't my forte, and the documentation on initializing with
backups is pretty sparse, so any advice is appreciated.
Use sp_addarticle followed by sp_addsubscription with @.sync_type =
automatic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Add Article(table) to Trans Repl - Move to Subscriber

How to you add an Article (Table) to a Transaction Replication Publication,
and have it move to the Subscribers without having to re-generate the Whole
Snapshot?
What to I monitor to see this happen and how long to I wait to see it update?
Thanks and God Bless,
ThomBeaux
Thanks and God Bless,
ThomBeaux
"ThomBeaux" wrote:

> How to you add an Article (Table) to a Transaction Replication Publication,
> and have it move to the Subscribers without having to re-generate the Whole
> Snapshot?
> What to I monitor to see this happen and how long to I wait to see it update?
> --
> Thanks and God Bless,
> ThomBeaux
|||Will a new snpahot be created for the whole Publication?
Thanks and God Bless,
ThomBeaux
"Paul Ibison" wrote:

> Just running sp_addarticle and sp_addsubscription then running the snapshot
> agent should be fine. Unlike merge, just the new article will get created.
> exec sp_addarticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.source_table = 'tEmployees'
> exec sp_addsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

Add additional Subscribers

Hi all,
Have Transactional Replication on SQL 2000 EE SP4 for some time now as
follows:
Publisher/Distributor is an 8 way Server
8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
Dist Agents run at Distributor as PUSH to existing Subscribers as follows
Subscriber1 - ServerA
Subscriber2 - ServerB
Subscriber3 - ServerC
This works great, and I now want to ADD:
Subscriber4 - ServerD
Subscriber5 - ServerE
What I want to do is add Server D and E as Subscribers (these are all
separate physical servers) without having to re-initialize any data on
Servers A, B, or C which have been running just fine.
I can prevent access to the PUBLISHER database (~20GB) and quiesce the
Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if need
be.
What is the best way overall to approach this ?
When the Replication was first setup, I set the Snapshot Agent to use 8 BCP
Threads and it took about 30 mins to generate the Initial Snapshot, and
about 1.5 hours to apply to each Subscriber A, B, C across the network
(these servers are all well connected with dedicated nics on a private LAN
solely for Server to Server Replication traffic).
If I go through the Publication Properties dialog and ADD my two new
Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
follows:
1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E cause
the Snapshot Agent to actually create a fresh snapshot ? I assume it will
"invalidate", but there is no existing snapshot currently available, and the
SS Agent has only been run manually on occasion since the intitial setup to
publish new articles to the subscribers which has worked fine. Otherwise the
SS Agent remains disabled.
2 - Will the Snapshot attempt to be applied to, or mess up anything for the
existing (already in-synch) Subscriptions on Servers A, B, C or should it
*only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
I hope the above makes sense and ariticulates what I'm trying to do. Thanks
in advance for any advice, or hints/tips you can provide.
Thanks,
Frank
Just as a follow up question, if I have the Publisher DB and Subscriber DB's
all in synch, and not accessible to users can I:
1 - Take a FULL Backup of one of the Subscription DB on any of the existing
Subscriber Servers (A/B or C), then restore it to Servers D/E
2 - Use the Publication Wizard to PUSH a new Subscription to Servers D/E
using the No Synch option ?
Does that do all the necessary behind the scenes processing correctly ? I
assume because I've restored a backup of an existing Subscriber DB to the
NEW Subscriber DB that it will have all the INS, UPD, DEL StoredProcs in
place as well ?
I read you can do a backup/restore of the Publication DB on a Subscriber for
a no-synch initialization, but the Subscription DB's have auto created
ClusteredIndexes for the PriKeys that exist on the Publication so would that
be an issue ?
In addition, there are some different non-clustered indexes on the
Subscribers as they are used for read-only queries, and mostly reporting
purposes that are offloaded from running on the Publisher DB.
Thanks,
Frank
"Frank Conte" <Frank.Conte@.discussions.ms.com> wrote in message
news:%23GPqOqpwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Have Transactional Replication on SQL 2000 EE SP4 for some time now as
> follows:
> Publisher/Distributor is an 8 way Server
> 8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
> Dist Agents run at Distributor as PUSH to existing Subscribers as follows
> Subscriber1 - ServerA
> Subscriber2 - ServerB
> Subscriber3 - ServerC
> This works great, and I now want to ADD:
> Subscriber4 - ServerD
> Subscriber5 - ServerE
> What I want to do is add Server D and E as Subscribers (these are all
> separate physical servers) without having to re-initialize any data on
> Servers A, B, or C which have been running just fine.
> I can prevent access to the PUBLISHER database (~20GB) and quiesce the
> Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if
> need be.
> What is the best way overall to approach this ?
> When the Replication was first setup, I set the Snapshot Agent to use 8
> BCP Threads and it took about 30 mins to generate the Initial Snapshot,
> and about 1.5 hours to apply to each Subscriber A, B, C across the network
> (these servers are all well connected with dedicated nics on a private LAN
> solely for Server to Server Replication traffic).
> If I go through the Publication Properties dialog and ADD my two new
> Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
> follows:
> 1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E
> cause the Snapshot Agent to actually create a fresh snapshot ? I assume it
> will "invalidate", but there is no existing snapshot currently available,
> and the SS Agent has only been run manually on occasion since the intitial
> setup to publish new articles to the subscribers which has worked fine.
> Otherwise the SS Agent remains disabled.
> 2 - Will the Snapshot attempt to be applied to, or mess up anything for
> the existing (already in-synch) Subscriptions on Servers A, B, C or should
> it *only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
> I hope the above makes sense and ariticulates what I'm trying to do.
> Thanks in advance for any advice, or hints/tips you can provide.
> Thanks,
> Frank
>
|||(1) for an initialisation you'll need to create a new snapshot.
(2) initialization of the 2 new subscriptions won't affect existing
subscriptions.
HTH,
Paul Ibison
|||You can use a backup of the subscriber - it is much less work than taking the
publisher backup. You don't have to be concerned about identity columns,
deleteing data from filtred articles, removing redundant tables and so on.
There are more details which might be relevant here:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the quick reply, and helpful info.
So, according to section 2.1 "Adding Additional Articles" in the link you
provided; if I do this by way of backup an existing Subscriber, restore it
to a new Subscriber(s) than do a no-sych Subscription through the wizard I
LOSE the ability to run the snapshot agent thereafter where it would
ordinarily:
1 - Generate schema, index and data files for a NEWLY added article on the
publisher. For example, I add a new table on the Publisher, then go to the
Publication Properties and check off the new table to be added in the
Article tab. Normally I would run the Snapshot Agent manually and it
generated the files for just that newly added article to the Publication.
2 - Have the Distribution Agent pickup the new articles and push them to my
new Subscribers, like it would for my existing ones that were originally
initialized via Snapshot (sync_type=automatic).
If that is the case, I think I should take the other approach as per my
other POST ?
That is...add the two new Subscribers, generate a fresh snapshot, and let
it apply it to the two new Subscribers all while the Publication DB, and
existing Subscription DB's are quiesced and offline (as in no changes
allowed) ?
Hassan's workaround you noted doesn't apply if adding articles via the
Publication Properties, correct ? It would always require using
sp_addsubscription going forward, and as you mentioned make for a less
maintainable setup than I would like.
Thanks,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5C31C7A4-96EE-4DC5-963E-0D8751CA04B3@.microsoft.com...
> You can use a backup of the subscriber - it is much less work than taking
> the
> publisher backup. You don't have to be concerned about identity columns,
> deleteing data from filtred articles, removing redundant tables and so on.
> There are more details which might be relevant here:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
I think this is the better approach for me (see my other reply to yours) in
this thread.
So provided I do not delete and re-add my existing Subscribers in the
Publication Properties they will remain completely uneffected by the new
snapshot files that I do want applied to my new Subscribers.
That sounds like the way to go to keep the maintainable setup going forward.
Is there any other known workaround similiar to Hassan's that doesn't
require using scripting after using the backup/restore method instead of the
initialize with snapshot files ?
Many thanks for you insight, and on going assistance you provide in these
NG's and via your web site.
Regards,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:971720D2-2205-43B4-96B9-91FC56D37051@.microsoft.com...
> (1) for an initialisation you'll need to create a new snapshot.
> (2) initialization of the 2 new subscriptions won't affect existing
> subscriptions.
> HTH,
> Paul Ibison
|||Hi Frank - this is correct. For addition of new 'automatic' articles, you'd
need to do this in scripts. Therefore adding new subscriptions normally will
be more suitable. Actually I do the nosync method primarily when the
bandwidth is small and the snapshot is huge - in which case I zip it up
before FTPing over - quite different to your case as far as I can determine.
Cheers,
Paul Ibison
|||What I've seen some people do is to add the new articles to a new publication
in order for the process to be automatic (after having already done a nosync
initialization). This is fine for a one-off provided the articles aren't
related.
HTH,
Paul Ibison

Add a column to published table without replicating to subscriber

I would like to add a column to a published table but not have that column replicated to subscribers. I can accomplish this via the UI by adding the column and then unchecking it. This adds the column to the publisher table but does not replicate it to the subscriber.

I am looking for a programmatic method to add a column to the base table and unmark it for replication.

Thanks for your assistance.

Here is a TSQL example (I suppose you mean SQL Server 2005):

1. First turn replication_ddl property off for your publication

exec sp_changepublication @.publication = 'pubname'
, @.property = 'replicate_ddl'
, @.value = '0'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 0

2. Execute DDL statement to add a new column:

alter table authors add newcol8 int not null default 0

3. If you want to turn schema/DDL replication on for subsequent add columns, then turn it back on:

exec sp_changepublication @.publication = 'pubname'
, @.property = 'replicate_ddl'
, @.value = '1'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 0

Hope that helps,

Zhiqiang Feng

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

|||

Another option you can have is vertical partitioning with which you can filter out which columns you want to publisher and which ones you do not want to.

When you mention that you could achieve this by doing in the UI, this is what is internally happening. The table is enabled for vertical partitioning and the newly added column is removed from the partition when you uncheck this column

Look in BOL for:sp_mergearticlecolumn, sp_articlecolumn, vertical_partition parameter of sp_addarticle/sp_addmergearticle