adding a stored proc to a publication

Hi

I created a publication (merge anonymous via Web) - the subscriber already had the data, so there was no need to to initialize data and schema.

The problem is when it comes to adding a new stored proc to the publication. I create the proc, add it as an article to the publication and recreate the snapshot ok, but then the synchronisation fails. I presume this is because the stored proc does not exist at the subscriber. I bet it would work if I had initialized the schema and data, but due to the size of the data this is not practical.

If I manually create the proc at the subscriber then it works fine - but this defeats the purpose somewhat.....

Any ideas ?

thanks
BruceSince the subscription was created as no-sync, you will need to manually create the proc on the subscriber. You could create different publications for tables and stored procs and workaround that way. Now you can initialize the subscriber for the publication with the stored proc (and other smaller articles if you wish) and large data publication can stay intact (as no-sync)|||Thanks for the suggestion - a good workaround for now....

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 new subscriber

Hi
I have merge replication. One client is already subscribed. I need to add
another client to the publication
The database is pretty big, so I don't want to send the whole db over the
internet.
I want to back it up and restore it, then establish the subscription.
Are the steps
a) backup database
b) recreate snapshot
c) restored db at subcriber B
d) establish subscription at subcriber B
I would assume I don't want any changes to the database made from the other
subscriber between steps a) and d)
Is this right. And I hope recreating the snapshot will not 'damage'
subscriber A
Thanks
Bruce
Thanks
Bruce
Bruce,
if you want a pull subscription, the easiest way is to create an Attachable
Subscription Database (details in BOL). If not, then you can backup and
restore and subscribe without initialization - @.sync_type = none.
HTH,
Paul Ibison
|||Bear in mind that if the database is too large the attachable subscription
will not work. The compressed databse is limited to 2 gig in size so a
large database will have problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.

adding a new column to a table involved in merge replication

Hi everyone,
I would like to know if there is some way to add a new column to an
existing table in a database published via merge replication that does
not require reinitialization of the articles.
Thanks.
Aramid
Aramid,
have a look at sp_repladdcolumn in BOL. This can also be done through the
GUI in Enterprise manager. In SQL Server 2005 the same effect is achieved
directly using Alter Table statements (provided the subscriber is also SQL
2005).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding a Distributor

I have a current merge replication system with a publisher server that is its
own distributor, and four remote subscriber servers. I now want to make the
present publisher/distributor a distibutor, and add a new publisher only
server. What is the easiest method?
Can I avoid having to recreate the whole system and remake the subscribers?
Thanks for advice.
You will have to script out your publications and subscriptions. Do a final
synchronization, drop them, and then migrate to the new publisher.
Then recreate everything.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"MDP" <MDP@.discussions.microsoft.com> wrote in message
news:0578784C-DA1B-40D5-9903-3F7228E79EA5@.microsoft.com...
>I have a current merge replication system with a publisher server that is
>its
> own distributor, and four remote subscriber servers. I now want to make
> the
> present publisher/distributor a distibutor, and add a new publisher only
> server. What is the easiest method?
> Can I avoid having to recreate the whole system and remake the
> subscribers?
> Thanks for advice.

Adding a column to tables in merge replication.

Hi, we need to add a column to the table that is used as the subscriber table
in a merge replication, will this break the replication.
We also need to add a column to the table that is used as the publisher
table in a merge replication, will this also break the replication?
Thanks,
Pingx
You should not touch the subscriber tables - they will be locked and you
will be unable to make modifications to them.
Use sp_repladdcolumn to add the column in SQL 2000. In SQL 2005 is the
replicate_ddl statement is set to true table modifications will be
replicated.
http://www.zetainteractive.com - Shift Happens!
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
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:2E4682B7-BD3D-4765-984F-2EC1A83C3BD0@.microsoft.com...
> Hi, we need to add a column to the table that is used as the subscriber
> table
> in a merge replication, will this break the replication.
> We also need to add a column to the table that is used as the publisher
> table in a merge replication, will this also break the replication?
> Thanks,
> Pingx
|||I used the statement
ALTER TABLE table_name ADD <column> at the publisher.
It got replicated to the subscriber.
Is this OK? do I need to use replicate_ddl statement ?
Thanks.
Pingx
"Hilary Cotter" wrote:

> You should not touch the subscriber tables - they will be locked and you
> will be unable to make modifications to them.
> Use sp_repladdcolumn to add the column in SQL 2000. In SQL 2005 is the
> replicate_ddl statement is set to true table modifications will be
> replicated.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:2E4682B7-BD3D-4765-984F-2EC1A83C3BD0@.microsoft.com...
>
>
|||It appears that you are using SQL 2005 where replicate_ddl is enabled by
default.
http://www.zetainteractive.com - Shift Happens!
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
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:1991127A-39EC-492C-8FBE-EAAB44EE0C3C@.microsoft.com...[vbcol=seagreen]
>I used the statement
> ALTER TABLE table_name ADD <column> at the publisher.
> It got replicated to the subscriber.
> Is this OK? do I need to use replicate_ddl statement ?
> Thanks.
> Pingx
> "Hilary Cotter" wrote:

Adding a colum to a merge replication

Hi,
I have a question on how to add a column in a merger replication?
Actualy I know how to add a column in a publication for a merge replication
What I need to know is how to create a foreign Key for that column
Exemple :
01- I have a table myTable that is already in my publication as an article
02- I want to add the column employeID int NULL
03- I also need to create a foreign key on that column to another column of
another table that is also in the publication
Is that possible ?
I have a doubt because a forign key is an alteration over a table and not
over a column and I think we can not make an alter Table on a table that is
in a publication with Sql 2000
SO is there a way we can create a foriegn Key for a new column in a table
that is already in a publication ?
Thanks in advance !
Pls have a look at sp_addscriptexec.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Add or Modify Keys in Merge Replication

I have several articles in a SQL Server 2000 merge publication where I need to do one of the following:
1. Delete a existing key and its column, and create a new one with a new column.
2. Add a new key using existing columns.
3. Add a new key using a new column to be added.
Since this is merge replication, I cannot drop the articles involved using sp_droparticle and then use sp_addarticle after making the modifications.
The publication has several active subscriptions.
Any suggestions and/or procedures for above 3 items would be very much appreciated.
Thanks.
Bill
Bill,
have a look at these three procedures to do what you require:
sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
For your three cases, something like:
1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
sp_repladdcolumn
2. sp_addscriptexec (alter table x add constraint ...)
3. sp_repladdcolumn
For the sp_addscriptexecs don't forget to do the same process on the
publisher, and be sure to get the syntax correct, otherwise there'll be a
continuous error.
HTH,
Paul Ibison
|||Thanks you, Paul! I will give these solutions a try.
"Paul Ibison" wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||Having done one of these (my situation calls for nuber 1) would I have to
reinitialise the snapshot?
On Mon, 5 Jul 2004 20:04:21 +0100, Paul Ibison <Paul.Ibison@.Pygmalion.Com>
wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn
> ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
|||"Paul Ibison" wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||Paul:
I tried using sp_addscriptexec to test the deletion of an existing key. However,
when the subscriber synchronizes, the script fails to run. It keeps saying the
script file or directory does not exists. However, it clearly does exist in the
path that is shown in the error.
The synchronization takes place over the internet. When I have run sp_addscripexec
I have tried each of the following in specifying the location of the script file on
the publisher/distributor:
-- "C:\<directory>\<sub directory>\<script file name>"
-- "\\<server name>\<share name>\<sub directory>\<script file name>"
-- "\\<IP of the server>\<share name>\<sub directory>\<script file name>"
-- "\\<URL of the server>\<share name>\<sub directory>\<script file name>"
The last one does not work when I run sp_addscriptexec. However, the first three
do. After sp_addscriptexec runs, it adds the script file to the snapshot location
on the server. I am running sp_addscriptexec at the publisher through a remote
desktop connection.
When the subscriber synchronizes, in the merge agent session details, the
synchronization is successful and data changes are merged, however, one of the
details shows the error that "osql" cannot find the file or directory at:
"\\<server name>\C$\<share name>\<snapshots folder>\<some long date & time
string>\<script file name>". The "<server name>" is the name of the
publisher\distributor.
I must be missing some step but I find nothing in BOL that helps. I also searched
Micorsoft.com and used Google to look for the osql error, but find nothing.
Do you or does anyone else have any suggestions or ideas to try?
Thanks in advance.
Bill
"Paul Ibison" wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||are you on the network? If you are not connected to the network or the
internet you will get this error.
If you are running workstation or professional you may have exceeded the
number of simultaneous connections for your os.
You may need to issue a net stop server and then a net start server and try
again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:43B07A06-4DBC-48C1-A815-E11BA7103C04@.microsoft.com...
> Paul:
> I tried using sp_addscriptexec to test the deletion of an existing key.
However,
> when the subscriber synchronizes, the script fails to run. It keeps
saying the
> script file or directory does not exists. However, it clearly does exist
in the
> path that is shown in the error.
> The synchronization takes place over the internet. When I have run
sp_addscripexec
> I have tried each of the following in specifying the location of the
script file on
> the publisher/distributor:
> -- "C:\<directory>\<sub directory>\<script file name>"
> -- "\\<server name>\<share name>\<sub directory>\<script file name>"
> -- "\\<IP of the server>\<share name>\<sub directory>\<script file name>"
> -- "\\<URL of the server>\<share name>\<sub directory>\<script file name>"
> The last one does not work when I run sp_addscriptexec. However, the
first three
> do. After sp_addscriptexec runs, it adds the script file to the snapshot
location
> on the server. I am running sp_addscriptexec at the publisher through a
remote
> desktop connection.
> When the subscriber synchronizes, in the merge agent session details, the
> synchronization is successful and data changes are merged, however, one of
the
> details shows the error that "osql" cannot find the file or directory at:
> "\\<server name>\C$\<share name>\<snapshots folder>\<some long date & time
> string>\<script file name>". The "<server name>" is the name of the
> publisher\distributor.
> I must be missing some step but I find nothing in BOL that helps. I also
searched
> Micorsoft.com and used Google to look for the osql error, but find
nothing.[vbcol=seagreen]
> Do you or does anyone else have any suggestions or ideas to try?
> Thanks in advance.
> Bill
>
> "Paul Ibison" wrote:
,[vbcol=seagreen]
a[vbcol=seagreen]

Add new tables to replication

Using SQL Server 2000 with sp4, I have setup a merge replication with a snapshot, currently if we do a schema change we have to:
-break replication,
-stop the subscription,
-make our modifications to the publisher,
-then create a backup of the database,
-push the backup to all of the subscribers,
-restore the database to the subscriber,
-reinitialize the subscription and push the subscription to the subscribers without pushing the contents of the initial snapshot

the reason why we do it this way is that the various servers are located hundreds of miles apart from the publisher and even with broadband connections, re-initializing the subscription always fails.

this is incredibly cumbersome and time consuming and i know there must be a better way to do this.

i need to add two tables to an existing database that is currently being replicated via merge replication, is there an easier way to make the schema changes and have them replicate to all the subscribers?

Rather than making a schema change to an existing table article in the merge publication, it seems like you are trying to add an incremental article to an existing table article. Also from what I see in the scenario, it seems like the subscriptions are set up to be NO SYNC (SP_addmergesubscription @.sync_type='none'). The NO SYNC means you are populating the subscriber server with the initial data manually via backup/restore.

In order for you to add a new table or two to the existing merge publication you may not have to break replication. Here are some steps to try:

1) Create the new tables at the publisher. These new tables must include a rowguid column (i.e. [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL) because merge will use this for change tracking. This scenario will not work without rowguid columns on the new tables.

2) Populate the table

3) Add the new table as a table article in the existing merge publication. You will be warned appropriately that the full snapshot needs to be rerun.

4) Run the full snapshot agent job.

5) BCP out the data from the new table article to a file and send it to the remote subscriber server

6) Create the new table schema at the subscriber

7) BCP in the data on the subscriber server

8) Execute the merge agent. This is required to have the merge triggers built on the subscriber for change tracking. *** Do not do any DML changes on the subscriber server until the merge agent is executed at least one time.

9) Now you should be able to execute DML on the new table at both the publisher and subscriber and sync the changes.

Try this out on a test system to verify it works. I tried on my system and it accomplished what you wanted.

Tom

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

Add Column to table in merge and transactional rep

Hi,
I have a table that is in both merge and transactional replication and I
would like to add a column to it. Typically, when I add columns to tables in
replication they only exist in one table, so I just go to the publication
properties and add it use the add column button. This time I am a unsure of
how to proceed though because I am not convinced that If I add it through the
interface of the merge publication that it will flow through in the
transactional replication.
So my solution is to add this to the merge replication as per normal and
reinitialize the transactional one and I wondered if anyone had an opinion on
this.
OK, through some experimentation I found that when I go to add a column to
one publication it allows you to choose any others that are applicable, so I
did and that worked great.

Add column to Merge publication causes re-init without subcriberchanges

First off, we have added columns in the past and I understand that
causes a reinit, but the option is in place to upload subscriber
changes. This has worked before, we are now on CU4 and I am
suspecting something might have changed?
It is a vague error message "The merge process could not enumerate
changes at the 'Subscriber'. "
I have tried it both from the GUI and from sp_mergearticlecolumn,
neither worked.
Anyone have any ideas?
fyi, these are CE subscribers...
That's a hard one. Try setting the querytimeout to a large value and
enable logging to see where it is getting stuck. You can specify a
profile name in the sqlcereplication class.
On Nov 15, 12:25 pm, Greg J <greg.judk...@.gmail.com> wrote:
> First off, we have added columns in the past and I understand that
> causes a reinit, but the option is in place to upload subscriber
> changes. This has worked before, we are now on CU4 and I am
> suspecting something might have changed?
> It is a vague error message "The merge process could not enumerate
> changes at the 'Subscriber'. "
> I have tried it both from the GUI and from sp_mergearticlecolumn,
> neither worked.
> Anyone have any ideas?
> fyi, these are CE subscribers...
|||Sounds like you are doing something wrong.
First, adding a column does not cause a new snapshot to be created. A new
table yes, a new column - no.
Use:
sp_repladdcolumn @.source_object = 'tableName'
, @.column = 'newColumn'
, @.typetext = 'dataType'
, @.publication_to_add = 'mergePubName'
, @.force_invalidate_snapshot = 0
Second - increase the QueryTimeout value in your merge agent profile.
Finally, make sure you are performing merge systable maintenance... at a
minimum you should be rebuilding indexes on these tables:
DBCC DBREINDEX (MSmerge_contents, '', 80)
DBCC DBREINDEX (MSmerge_genhistory, '', 80)
DBCC DBREINDEX (MSmerge_tombstone, '', 80)
DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
ChrisB MCDBA
MSSQLConsulting.com
"Greg J" wrote:

> First off, we have added columns in the past and I understand that
> causes a reinit, but the option is in place to upload subscriber
> changes. This has worked before, we are now on CU4 and I am
> suspecting something might have changed?
> It is a vague error message "The merge process could not enumerate
> changes at the 'Subscriber'. "
> I have tried it both from the GUI and from sp_mergearticlecolumn,
> neither worked.
> Anyone have any ideas?
> fyi, these are CE subscribers...
>
|||fyi... sp_repladdcolumn is SQL2000 syntax
SQL2005 - use regular DDL: ALTER TABLE myTable ADD newColumn int
ChrisB
"Greg J" wrote:

> First off, we have added columns in the past and I understand that
> causes a reinit, but the option is in place to upload subscriber
> changes. This has worked before, we are now on CU4 and I am
> suspecting something might have changed?
> It is a vague error message "The merge process could not enumerate
> changes at the 'Subscriber'. "
> I have tried it both from the GUI and from sp_mergearticlecolumn,
> neither worked.
> Anyone have any ideas?
> fyi, these are CE subscribers...
>
|||On Nov 15, 12:56 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
> fyi... sp_repladdcolumn is SQL2000 syntax
> SQL2005 - use regular DDL: ALTER TABLE myTable ADD newColumn int
> ChrisB
>
> "Greg J" wrote:
>
>
> - Show quoted text -
Yes this is 2005.
I am using the alter table followed by the sp below. We have
@.replicate_ddl turned off, we don't want all columns published.
ALTER TABLE myTable ADD
myColumn smallint NULL
GO
exec sp_mergearticlecolumn @.publication = N'myPub',
@.article = N'myTable',
@.column = N'myColumn,
@.operation = N'add',
@.force_invalidate_snapshot = 1, -- I have tried running without this
option, it does not allow it because there is already a valid
snapshot.
@.force_reinit_subscription = 1 -- I have tried this without the
option, it won't allow it b/c subscribers are already tied to it.
I'm working on getting verbose logging going now...
|||On Nov 15, 1:41 pm, Greg J <greg.judk...@.gmail.com> wrote:
> On Nov 15, 12:56 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
>
>
>
>
>
>
> Yes this is 2005.
> I am using the alter table followed by the sp below. We have
> @.replicate_ddl turned off, we don't want all columns published.
> ALTER TABLE myTable ADD
> myColumn smallint NULL
> GO
> exec sp_mergearticlecolumn @.publication = N'myPub',
> @.article = N'myTable',
> @.column = N'myColumn,
> @.operation = N'add',
> @.force_invalidate_snapshot = 1, -- I have tried running without this
> option, it does not allow it because there is already a valid
> snapshot.
> @.force_reinit_subscription = 1 -- I have tried this without the
> option, it won't allow it b/c subscribers are already tied to it.
> I'm working on getting verbose logging going now...- Hide quoted text -
> - Show quoted text -
Ok, so the lesson to be learned is just do it the way Microsoft
expects you to. I turned on the @.replicate_ddl to true for the
publication and added the column. It put the column in the
publication, brought the schema to all the subscribers, uploaded all
changes, not even a need for a snapshot or re-inits from the devices.
That leaves the question why doesn't the above stored proc behave like
the @.replicate_ddl option would, but I'm ok not knowing.

Add column to a merge article.

Hi, I need to add a new column to a table that is an
article in a merge replication. I know how to do that in
enterprise manager, but i need to add this new column in
the table primary key. I try to do it but it seem that i
have to remove the replication, do the changes and then
configure the replication again.
Do you know if i can do this changes without removing the
replication? Could you explain me how?
Thanks a lot for your help.
have you tried sp_repladdcolumn?
"maria" <mariaisabelguzman@.icasa.com.gt> wrote in message
news:f7d501c4135a$1e0f9aa0$a001280a@.phx.gbl...
> Hi, I need to add a new column to a table that is an
> article in a merge replication. I know how to do that in
> enterprise manager, but i need to add this new column in
> the table primary key. I try to do it but it seem that i
> have to remove the replication, do the changes and then
> configure the replication again.
> Do you know if i can do this changes without removing the
> replication? Could you explain me how?
> Thanks a lot for your help.
>
|||Sp_repladdcolumn is valid for SQL Server 2000. If you have SQL Server 7.0,
you must drop the subscriptions and publication before adding the column.
Regards,
Shirley
SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. 2001 Microsoft Corporation. All rights
reserved.

add column disaster

Hi all, got a disaster on my hands,
ive added a field to a merge replication through the enterprise manager, in
the create manage publications, and its propogate ok to one server, but to
another server, when the merge agent runs, the agents timesout trying to add
the field, and its locking the users at the subscriber out, this table has
about 3 million rows in it. ive tried deleteing the contents, and even
truncating the contents of the table at both publisher and subscriber, but
this also locks out the users.
so the problem is that i cant run the merge agent as the field wont add at
the subscriber, times out and stops, so non of the data changes in the other
tables are getting fed accross.
Any help would be great cheers Andrew
use profiler to determine what process is locking with the merge agent
running on the subscriber and kill it. Try to keep all users off this
problem subscriber until it has completed.
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:u7qot480FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all, got a disaster on my hands,
> ive added a field to a merge replication through the enterprise manager,
> in the create manage publications, and its propogate ok to one server, but
> to another server, when the merge agent runs, the agents timesout trying
> to add the field, and its locking the users at the subscriber out, this
> table has about 3 million rows in it. ive tried deleteing the contents,
> and even truncating the contents of the table at both publisher and
> subscriber, but this also locks out the users.
> so the problem is that i cant run the merge agent as the field wont add at
> the subscriber, times out and stops, so non of the data changes in the
> other tables are getting fed accross.
> Any help would be great cheers Andrew
>

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 article to Merge Repl. Publication

I am running merge replication (SQL 2000 with SP2) with an anonymous pull subscription. The application vendor has come out with update that requires adding a table to a database. The vendor has created scripts that will add the table, as well as some stored procedures. If I apply the scripts to both servers and add the table as a new article to the publication, am I going to have to apply a snapshot of the entire database (which is very large)?

Your help is greatly appreciated.

GaryHi, don't add the table to both sides. Just add the table at the publisher and include it in the publication using the ARTICLES tab of Replication-Properties in EM. Start the Snapshot-Agent. The merge agent would apply only the new table to the subscriber. It's for the subscribers running SQL2000.|||Thanks for the prompt reply. The scripts will also modify some stored procedures - is that handled by the snapshot, or are the changes just replicated?|||Hi, the merge-agent applies the new tables to the subscriber.
About stored-procedures i am not sure, can u modify the stored-procedure if it's being published? Published tables can't be modified unless we use sp_repladdcolumn/sp_repldropcolumn to add or drop columns within the published tables. See if u can modify a published stored procedure at the publisher.

Howdy!

Add and Change tables in Merge

We have a merge replication setup in SQL 2000 that synchs with 2 laptops. I
need to add a new table to the database that will be included in the new
subscription as well as a change (adding a column) to a table already in the
publication. What are the steps I need to take to get the schema changes to
the 2 laptops? Thanks.
David
David - the new table can be added through the EM gui. Then run the snapshot
agent which will snapshot all articles but the synchronization will just
take the new article. For the new column, you can run sp_repladdcolumn.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul. Will the laptop users get the new snapshots automatically or
will I have to do something special? They are using MSDE. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uwzVBo3KHHA.4912@.TK2MSFTNGP02.phx.gbl...
> David - the new table can be added through the EM gui. Then run the
> snapshot agent which will snapshot all articles but the synchronization
> will just take the new article. For the new column, you can run
> sp_repladdcolumn.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Also, I have 2 publications. One has about 50% of the tables as articles and
the other has about 20-30 views and stored procs. And 1 of the tables has a
triigger update. Does any of this change your recommendation? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uwzVBo3KHHA.4912@.TK2MSFTNGP02.phx.gbl...
> David - the new table can be added through the EM gui. Then run the
> snapshot agent which will snapshot all articles but the synchronization
> will just take the new article. For the new column, you can run
> sp_repladdcolumn.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||David - they'll get the new snapshotted table the next time they
synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||David - I'm not too sure why this is being mentioned - but perhaps I'm
missing something . From what you mentioned, you're just adding a new
table and a column. Perhaps you're saying that the procedural code will
itself need modifying? In that case I usually use sp_addscriptexec to
propagate it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul - I was mostly concerned with the new trigger as it will be on a table
other than the one being added to the articles.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FzseC5KHHA.1276@.TK2MSFTNGP04.phx.gbl...
> David - I'm not too sure why this is being mentioned - but perhaps I'm
> missing something . From what you mentioned, you're just adding a new
> table and a column. Perhaps you're saying that the procedural code will
> itself need modifying? In that case I usually use sp_addscriptexec to
> propagate it.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||OK - in that case you could add this using sp_addscriptexec.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Add a Table to an Anonymous Merge Publication

What is the "correct" way to ADD a Table to an Anonymous Merge Publication?
How is it then propagated to the Subscribers?
Using Query Analyzer, I successfully ran a script to Create the Table. I
added it to the Publication (Show unpublished ...) and Reran the Snapshot
Agent. The Snapshot posted to the expected unc Folder(s).
From an Internet Client using SQL-DMO or Windows Synchronize Manager, it
successfully connects to the SQL Server, but fails with this Error:
"You must rerun the snapshot because the current snapshot is obsolete"
Does that mean the Entire Database needs to be downloaded again? If so, what
happens to the Changes on the Subscriber that have not been Uploaded yet?
TIA
Aubrey Kelley
Hi Aubrey,
Thanks for your post.
From your descriptions, I understood you have concern when you are notified
"You must rerun the snapshot because the current snapshot is obsolete". If
I have misunderstood your concern, please feel free to point it out.
Based on my knowledge, alter adding a new merge article, you must generate
a new snapshot before changes from any subscription can be merged. Although
a snapshot of all articles must be generated, only the snapshot of the new
article will be used to synchronized existing subscriptions.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks, Michael
The error message on attempting to rerun the Snapshot was due to a corrupt
Database. Using a different Published Database, after running the SQL Script
to successfully Add a Table, I Started the Snapshot Agent (successful also),
and was able to Synchronize with NO alterations from an Internet Connected
Access Runtime Remote Client with an existing Subscription to that Database.
An MSDN MSPSS call Helped run the Profiler to pinpoint the offender, a View
with a File Name name format. Also, we added this to the Snapshot Agent
Properties, Edit Job Step: -Output <filename.txt> -OutputVerboseLevel 3
Aubrey Kelley
"Michael Cheng [MSFT]" wrote:

> Hi Aubrey,
> Thanks for your post.
> From your descriptions, I understood you have concern when you are notified
> "You must rerun the snapshot because the current snapshot is obsolete". If
> I have misunderstood your concern, please feel free to point it out.
> Based on my knowledge, alter adding a new merge article, you must generate
> a new snapshot before changes from any subscription can be merged. Although
> a snapshot of all articles must be generated, only the snapshot of the new
> article will be used to synchronized existing subscriptions.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Aubrey,
Thanks for sharing this information with us and it's great to hear that you
have resolved it with the help of PSS support.
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

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 article to a Merge Replication

I have a Merge Replication with 48 subscriptions and I like add a new
article, but I don't like reinitialize all my subscriptions.
I try to use the sp_addarticle,
sp_addarticle [ @.publication = ] 'NamePublication'
, [ @.article = ] 'NameArticle'
, [ @.source_table = ] 'source_table'
but when I 'm running the sp it is send me this message
Server: Msg 208, Level 16, State 1, Procedure sp_MSreinit_article, Line 25
Invalid object name 'syspublications'.
can you query the syspublications table on your publisher?
Are all objects owned by dbo?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"David Barquero" <davbarquero@.hotmail.com> wrote in message
news:um0KPj$9EHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a Merge Replication with 48 subscriptions and I like add a new
> article, but I don't like reinitialize all my subscriptions.
> I try to use the sp_addarticle,
> sp_addarticle [ @.publication = ] 'NamePublication'
> , [ @.article = ] 'NameArticle'
> , [ @.source_table = ] 'source_table'
> but when I 'm running the sp it is send me this message
> Server: Msg 208, Level 16, State 1, Procedure sp_MSreinit_article, Line 25
> Invalid object name 'syspublications'.
>
|||I was getting very similar messages when I was modifying system stored
procedures and did not restored their original attributes.
I am not sure how it all works, but if you modify system stored procedure,
it's attributes (in sysobjects table) are changed - column 'status' and
'base_schema_ver' are updated by the system. You have to restore those
values to the state that was before applying your modifications.
For example you are modifying stored procedure sp_addmergearticle.
1. Before doing your modifications, select current data from sysobjects for
that stored procedure.
SELECT * FROM [master].[dbo].[sysobjects] WHERE name = 'sp_addmergearticle'
2. Do your modifications to sp_addmergearticle
3. Update sysobjects with values selected in the 1st step
UPDATE [master].[dbo].[sysobjects]
SET [status]=-1073741823,
[base_schema_ver]=16
WHERE name = 'sp_addmergearticle'
4. Enjoy your working (hopefully) replication
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"David Barquero" <davbarquero@.hotmail.com> wrote in message
news:um0KPj$9EHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a Merge Replication with 48 subscriptions and I like add a new
> article, but I don't like reinitialize all my subscriptions.
> I try to use the sp_addarticle,
> sp_addarticle [ @.publication = ] 'NamePublication'
> , [ @.article = ] 'NameArticle'
> , [ @.source_table = ] 'source_table'
> but when I 'm running the sp it is send me this message
> Server: Msg 208, Level 16, State 1, Procedure sp_MSreinit_article, Line 25
> Invalid object name 'syspublications'.
>
|||Please use sp_addmergearticle.
thanks - deepak
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"David Barquero" <davbarquero@.hotmail.com> wrote in message
news:um0KPj$9EHA.3504@.TK2MSFTNGP12.phx.gbl...
> I have a Merge Replication with 48 subscriptions and I like add a new
> article, but I don't like reinitialize all my subscriptions.
> I try to use the sp_addarticle,
> sp_addarticle [ @.publication = ] 'NamePublication'
> , [ @.article = ] 'NameArticle'
> , [ @.source_table = ] 'source_table'
> but when I 'm running the sp it is send me this message
> Server: Msg 208, Level 16, State 1, Procedure sp_MSreinit_article, Line 25
> Invalid object name 'syspublications'.
>
|||Great observation Somehow I missed the point that David was using
sp_addarticle instead of sp_addmergearticle
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Deepak kumar [MSFT]" <dk@.online.microsoft.com> wrote in message
news:uQ3AnZQ$EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Please use sp_addmergearticle.
> thanks - deepak
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "David Barquero" <davbarquero@.hotmail.com> wrote in message
> news:um0KPj$9EHA.3504@.TK2MSFTNGP12.phx.gbl...
25
>