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.

Adding a New Table and EXCLUDING from merge replication

New to merge replication - sql 2000
I simply wish to add a table to the publisher database and this table
will never be replicated. Is there anything I need to do to exclude it?
Thanks,
Craig
Craig,
no problems here - creating the table using Enterprise Manager or using
Create Table will not automatically add it to the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding a article causes ALL articles to be refreshed

I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add or drop a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time.

This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped.

Has that functionality changed from 2000 to 2005? Am I not doing something right?

Thanks,

Amir

This is the default behavior for anonymous subscribers. IIRC through the GUI for named subscribers a complete snapshot will be generated. However if you use sp_addarticle only the mini snapshot for the one table will be generated.|||

I have tried using sp_article. The problem is that it gives me the error:

Cannot make the change because a snapshot is already generated. Set @.force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

And in order to avoid the error, if I use the Option of @.force_invalidate_snapshot=1, then again I am back to where I was before and it re-initializes everything.

BTW, What does IIRC mean?

Thanks,

Amir

|||

Any more thoughts on this? If my replication is setup and running, is there NO WAY to add "one" table to the publication without having to do the Bulk copy on ALL tables? Adding a table to replication seems one of the most basic maintenance tasks. I'm still surprised that MSFT has made that basic task so difficult in 2005.

Addign Column to a replicated Table

Hello,
Is there a way to add a column to a replicated table
without deleting the subscription and recreating it?
Thanks,
niv
Niv,
have a look at sp_repladdcolumn in BOL.
HTH,
Paul Ibison
|||Paul,
Am I correct to assume that I will have to run this stored
procedure on both the publisher and the subscriber.
niv

>--Original Message--
>Niv,
>have a look at sp_repladdcolumn in BOL.
>HTH,
>Paul Ibison
>
>.
>
|||Niv,
just the publisher will do and synchronization will propagate it down to the
subscriber, and then update the relevant objects.
Regards,
Paul Ibison

Add table to replicated database

How do I add a table to a replicated database using the Enterprise
Manager? I know to use the Publications properties to add a column,
but want to be sure to add a table correctly.
It's the same dialog box. Click the checkbox to show unpublished articles
and then select the table. In this sense it is different to adding a column,
as the table must already exist. After that, run the snapshot agent to
create the bcp files for the table. If the publication is a merge one, a
complete snapshot is created although only the new table will be used. On
synchronization, just the new article will be sent to the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Add new user?Dial-up replication?

after I replicated two project server database in two sql server, I don`t add new user or modify old user in project server,Why??
and another my problem:
How replication with Dial-up connection(I replicated with LAN)??
Please help me
Thanks
Bita
Message posted via http://www.sqlmonster.com
I don't understand your first question. Are you saying you are unable to
modify any old user or add a new one? If so what is the error message you
are getting.
In reference to your second question, consult this kb article
http://support.microsoft.com/default...b;en-us;241149
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Bita Joghataie via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:46b960085fdb4a61a0d40f13df7c622f@.SQLMonster.c om...
> after I replicated two project server database in two sql server, I don`t
add new user or modify old user in project server,Why??
> and another my problem:
> How replication with Dial-up connection(I replicated with LAN)??
> Please help me
> Thanks
> Bita
> --
> Message posted via http://www.sqlmonster.com

Add new tables to replication

I have setup transactional replication with no snapshot. I created a new
table on publisher side and I would want the table also to be replicated.
Here is what i did-
- drop the subscription
- create/dts table to subscriber
- create insert update stored procedures at subscriber(i have created them
during testing)
- add article to publication
-recreate subscription
Is there any other way to do it as I donot want my subscription to be
dropped each time as it has some other tables that are replicated
continuously...
Regards,
Ravi
Ravi,
to avoid dropping the subscription: as you did before...
> - create/dts table to subscriber
> - create insert update stored procedures at subscriber(i have created them
> during testing)
> - add article to publication
Then
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
, @.sync_type = 'none'
ie add the subscription to the particular article.
HTH,
Paul Ibison, SQL Server MVP (www.replicationanswers.com)

Add new primary key column for replicated table with pull subscrip

I need to add a new column to a replicated table. But in my case a new column should be a part of a primary key. So, I can't use sp_repladdcolumn procedure since we need to completely repopulate the whole table and there is no default value for a new colu
mn. In addition to that this column should be first in the table's columns list, not the last one what happens when you usually alter the table adding a new column.
More there, I have a pull subscription and it looks like there is no way to drop subscription for a specified article only, like it is possible for a push subscription. I would appreciate any help pointing to an easiest way to add a new primary key column
for a replicated table with pull subscription.
TIA
Libra,
ordinarily you should be able to use sp_droparticle, sp_addarticle and
sp_refreshsubscriptions however to add the new column in a specific position
you'll need to drop the published article then recreate it and resubscribe.
You might use a nosync subscription to make things easier if the snapshot is
particularly large, but this will have restrictions if you later want to add
an article or column, so I'd recommend initializing with the complete
snapshot as per usual.
HTH,
Paul Ibison
(BTW no doubt you have seen other threads on this but placing columns in a
particular order is not generally considered 'good form' as TSQL should
ideally use explicit column names rather than rely on position.)

Add new column to a replicated table..

Hi,
I need to add a column to a table that is being replicated.
Can I just do the ALTER TABLE ADD xxxx bit then make a call out to
sp_replAddColumn?
When I do so, the sp fails saying the column exists. The manual says that it
adds the column to the publications, but does not suggest it does the
physical add tot the table..
Thanks in advance,
SPGYou use sp_replAddColumn INSTEAD of using alter table add column... the Sp
adds the column to the table, and fixes replication... since you manually
added the column the sp fails.... drop the column and use the SP only.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"SPG" <steve.nospoo.goodsell@.nopooblueyonder.co.nopoo.uk> wrote in message
news:r9IQd.16027$8B3.14345@.text.news.blueyonder.co.uk...
> Hi,
> I need to add a column to a table that is being replicated.
> Can I just do the ALTER TABLE ADD xxxx bit then make a call out to
> sp_replAddColumn?
> When I do so, the sp fails saying the column exists. The manual says that
> it adds the column to the publications, but does not suggest it does the
> physical add tot the table..
> Thanks in advance,
> SPG
>|||Thanks for that..
I sort of half guessed that when the sp failed, but the docs are not too
clear on this..
Steve
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23oEXG8CFFHA.2568@.TK2MSFTNGP10.phx.gbl...
> You use sp_replAddColumn INSTEAD of using alter table add column... the
> Sp adds the column to the table, and fixes replication... since you
> manually added the column the sp fails.... drop the column and use the SP
> only.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "SPG" <steve.nospoo.goodsell@.nopooblueyonder.co.nopoo.uk> wrote in message
> news:r9IQd.16027$8B3.14345@.text.news.blueyonder.co.uk...
>

add article to publication in Merge Replication

I added an article to one of my publication with sp_addmergearticle as a
no-sync. A subscriber replicated last night with the following error:
The merge process could not retrieve column information for table
'dbo.ChargeGroup'.
(Source: Merge Replication Provider (Agent); Error number: -2147201016)
Could not find stored procedure 'sp_sel_00BE6107195E4608967622C23A664DE2'.
(Source: CBD076\MGW (Data source); Error number: 2812)
Do I need to reinitialize all my subscribers? I'm trying to add the
article without having to manually touch each one of the subscribers since
all of them are remote.
Thanks
Tina
Yes you will have to. You must use the @.force_invalidate_snapshot switch
with a setting of 1.ie
sp_addmergearticle 'Publication','tableName','tableName',
@.force_invalidate_snapshot=1
The bad news is that you will have to regenerate your snapshot. The good
news is that only this new article/table will travel to the subscriber(s).
Before you do this, can you check a couple of things.
Do a
select *from sysmergearticles where
select_proc='sp_sel_00BE6107195E4608967622C23A664D E2' on your publication
database and the problem subscriber.
Do you get a row on the publisher and none on the subscriber?
Then do this on your publisher?
select name from sysmergearticles where
select_proc='sp_sel_00BE6107195E4608967622C23A664D E2'
using that name do this on your subscriber
select select_proc from sysmergearticles where name=the name you got on your
publisher.
If these don't agree you have to drop your publications and subscriptions
and start again. Were you using dynamic snapshots? I ran into this problem
when I deployed dynamic snapshots, but after fixing it the one time,
everything worked well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:e8hf7NGgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> I added an article to one of my publication with sp_addmergearticle as a
> no-sync. A subscriber replicated last night with the following error:
> The merge process could not retrieve column information for table
> 'dbo.ChargeGroup'.
> (Source: Merge Replication Provider (Agent); Error number: -2147201016)
> ----
--
> --
> Could not find stored procedure 'sp_sel_00BE6107195E4608967622C23A664DE2'.
> (Source: CBD076\MGW (Data source); Error number: 2812)
> ----
--
> --
> Do I need to reinitialize all my subscribers? I'm trying to add the
> article without having to manually touch each one of the subscribers since
> all of them are remote.
> Thanks
> Tina
>
|||I already added the article with force_invalidate_snapshot = 1 and
regenerated the snapshot.
The sp_sel_ does exist in my sysmergearticles table on the publisher. The
subscriber is remote so I haven't had a chance to check for the stored
procedure on the subscriber.
Yes, I am using dynamic filters. I sure hope I don't have to drop my
publications to fix this.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u9WkigGgEHA.556@.tk2msftngp13.phx.gbl...
> Yes you will have to. You must use the @.force_invalidate_snapshot switch
> with a setting of 1.ie
> sp_addmergearticle 'Publication','tableName','tableName',
> @.force_invalidate_snapshot=1
> The bad news is that you will have to regenerate your snapshot. The good
> news is that only this new article/table will travel to the subscriber(s).
> Before you do this, can you check a couple of things.
> Do a
> select *from sysmergearticles where
> select_proc='sp_sel_00BE6107195E4608967622C23A664D E2' on your publication
> database and the problem subscriber.
> Do you get a row on the publisher and none on the subscriber?
> Then do this on your publisher?
> select name from sysmergearticles where
> select_proc='sp_sel_00BE6107195E4608967622C23A664D E2'
> using that name do this on your subscriber
> select select_proc from sysmergearticles where name=the name you got on
your[vbcol=seagreen]
> publisher.
> If these don't agree you have to drop your publications and subscriptions
> and start again. Were you using dynamic snapshots? I ran into this problem
> when I deployed dynamic snapshots, but after fixing it the one time,
> everything worked well.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:e8hf7NGgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> ----
> --
'sp_sel_00BE6107195E4608967622C23A664DE2'.[vbcol=seagreen]
> ----
> --
since
>
|||You mentioned that you added it as no-sync.
Did you mean the subscription was added as a no sync subscription ?
If so then, the table needs to exist at the subscriber when merge is run.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> I already added the article with force_invalidate_snapshot = 1 and
> regenerated the snapshot.
> The sp_sel_ does exist in my sysmergearticles table on the publisher.
The[vbcol=seagreen]
> subscriber is remote so I haven't had a chance to check for the stored
> procedure on the subscriber.
> Yes, I am using dynamic filters. I sure hope I don't have to drop my
> publications to fix this.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u9WkigGgEHA.556@.tk2msftngp13.phx.gbl...
subscriber(s).[vbcol=seagreen]
publication[vbcol=seagreen]
> your
subscriptions[vbcol=seagreen]
problem[vbcol=seagreen]
a[vbcol=seagreen]
error:[vbcol=seagreen]
number: -2147201016)
> ----
> 'sp_sel_00BE6107195E4608967622C23A664DE2'.
> ----
> since
>
|||Hi Mahesh,
The table already exist at the subscriber.
Thanks
Tina
"Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
news:eHsJpXNgEHA.140@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You mentioned that you added it as no-sync.
> Did you mean the subscription was added as a no sync subscription ?
> If so then, the table needs to exist at the subscriber when merge is run.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> The
switch[vbcol=seagreen]
good[vbcol=seagreen]
> subscriber(s).
> publication
on[vbcol=seagreen]
> subscriptions
> problem
as[vbcol=seagreen]
> a
> error:
> number: -2147201016)
> ----
> ----
the
>
|||I ran into something a little similar with dynamic snapshots.
I deployed one group of 20 subscribers one month, and then another month I
deployed a second 20. IIRC the first 20 encountered this error.
I had to start from scratch. The error I got was with missing sp_ins*,
sp_upd*, and sp_del* procs on the subscriber.
After the second deployement we never had the error again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> I already added the article with force_invalidate_snapshot = 1 and
> regenerated the snapshot.
> The sp_sel_ does exist in my sysmergearticles table on the publisher.
The[vbcol=seagreen]
> subscriber is remote so I haven't had a chance to check for the stored
> procedure on the subscriber.
> Yes, I am using dynamic filters. I sure hope I don't have to drop my
> publications to fix this.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u9WkigGgEHA.556@.tk2msftngp13.phx.gbl...
subscriber(s).[vbcol=seagreen]
publication[vbcol=seagreen]
> your
subscriptions[vbcol=seagreen]
problem[vbcol=seagreen]
a[vbcol=seagreen]
error:[vbcol=seagreen]
number: -2147201016)
> ----
> 'sp_sel_00BE6107195E4608967622C23A664DE2'.
> ----
> since
>
|||Thanks for all your input. I have many shoppes scheduled to replicate
Monday night. I'll assess the issue Tuesday morning and see where I need
to go from there. I'll let you know how it turns out.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23QzAfNTgEHA.2588@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I ran into something a little similar with dynamic snapshots.
> I deployed one group of 20 subscribers one month, and then another month I
> deployed a second 20. IIRC the first 20 encountered this error.
> I had to start from scratch. The error I got was with missing sp_ins*,
> sp_upd*, and sp_del* procs on the subscriber.
> After the second deployement we never had the error again.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> The
switch[vbcol=seagreen]
good[vbcol=seagreen]
> subscriber(s).
> publication
on[vbcol=seagreen]
> subscriptions
> problem
as[vbcol=seagreen]
> a
> error:
> number: -2147201016)
> ----
> ----
the
>

Add a table to a merge replicated database WITHOUT forcing a snapshot

Hello,
I've tried to catch up on all the posts in this area, and I can't really see
where this has been asked. If I have repeated a previous question, I do
apologize.
We are trying to add new tables to an existing merge replication by running
a create table statement on a publication database and then using
sp_addmergearticle with the @.creation_script parameter pointing to the sql
file to run on the subscriber as we are setting the identity fields
manually. When the procedure runs, it detects that the change requires a new
snapshot. We cannot afford the time it takes to create an entirely new
snapshot (could take days) so we are looking for the ways to speed up the
process.
We also tried to create tables directly by entering information into the
system tables but we have had trouble with generating insert, update and
delete triggers to replicate data.
We would like to know whether
1. There is a way to invalidate a snapshot for new tables only?
2. There is another way of creating new tables on a replicated
database.
Any help you could provide would be greatly appreciated! Thanks for your
assistance.
Sincerely,
Marshall
The standard ways to achieve this are:
(a) to create a new publication which contains the new table
(b) or to 'initialize' the table (by creating a complete snapshot)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I guess that's the problem. Neither of these solutions is possible for us.
We are using Merge Replication in a high availability solution and cannot
afford to have the system down for the amount of time it will take to either
1. Generate a new publication with the additional requisite tables, or
2. Add the tables and generate a new snapshot.
What you have described are indeed the standard ways to accomplish the task,
however, due to our environment we really need to do something faster. We
are trying to create these additional tables in the same publication and
without the overhead of recreating the snapshot, which is totally
unnecessary except for the internal triggers required to keep the tables
synchronized (they are, after all, empty after they are created.) Isn't
there some way we could just create the triggers required for the new tables
and apply those to the subscriber? We're really looking for a solution that
takes less than thirty minutes!
Thanks in advance for any suggestions!
Marshall
|||Creation of new publication with one empty table in it should take much less than 30 minutes... If it's not a problem to have multiple publications, then it is the way to go.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"WideOpenWest" <MBCDev> wrote in message news:e6GdnTskHapNZPreRVn-jg@.wideopenwest.com...
Paul,
I guess that's the problem. Neither of these solutions is possible for us.
We are using Merge Replication in a high availability solution and cannot
afford to have the system down for the amount of time it will take to either
1. Generate a new publication with the additional requisite tables, or
2. Add the tables and generate a new snapshot.
What you have described are indeed the standard ways to accomplish the task,
however, due to our environment we really need to do something faster. We
are trying to create these additional tables in the same publication and
without the overhead of recreating the snapshot, which is totally
unnecessary except for the internal triggers required to keep the tables
synchronized (they are, after all, empty after they are created.) Isn't
there some way we could just create the triggers required for the new tables
and apply those to the subscriber? We're really looking for a solution that
takes less than thirty minutes!
Thanks in advance for any suggestions!
Marshall
|||I never thought of doing something like that; it would probably work, but it
is standard procedure for us to add tables when we do software releases.
After some time, we're going to have publication after publication, which is
ugly and probably a significant performance threat (each publication will
need its own agents.)
Is there some way we could use the information generated in the snapshot of
that new publication to add the tables to the original publication? Perhaps
this is not so orthidox, but we really need to find a solution to this
issue.
Another idea that has been suggested is to add several hundred generic,
empty tables to the new publication. Then, every time we need a new table
in a release we add the columns to one of those generic tables and begin
using it. The problem here is that we'd have several hundred generically
named tables! Is it possible to rename a table across a merge replication?
This is ugly also.
Help!
Thanks,
Marshall
"Kestutis Adomavicius" <kicker.lt@.noospaam_tut.by> wrote in message
news:%235KQnK63FHA.2424@.TK2MSFTNGP10.phx.gbl...
Creation of new publication with one empty table in it should take much less
than 30 minutes... If it's not a problem to have multiple publications, then
it is the way to go.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"WideOpenWest" <MBCDev> wrote in message
news:e6GdnTskHapNZPreRVn-jg@.wideopenwest.com...
Paul,
I guess that's the problem. Neither of these solutions is possible for us.
We are using Merge Replication in a high availability solution and cannot
afford to have the system down for the amount of time it will take to either
1. Generate a new publication with the additional requisite tables, or
2. Add the tables and generate a new snapshot.
What you have described are indeed the standard ways to accomplish the task,
however, due to our environment we really need to do something faster. We
are trying to create these additional tables in the same publication and
without the overhead of recreating the snapshot, which is totally
unnecessary except for the internal triggers required to keep the tables
synchronized (they are, after all, empty after they are created.) Isn't
there some way we could just create the triggers required for the new tables
and apply those to the subscriber? We're really looking for a solution that
takes less than thirty minutes!
Thanks in advance for any suggestions!
Marshall

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