Re: sp_addarticle without re-creating entire new snapshot

This is puzzling. Could you post your publication schema as well.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



"Methodology" <Methodology@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FD287306-E14D-44F8-B613-58D5C03C574A@xxxxxxxxxxxxxxxx
USE [timeseries_dev]
GO
/****** Object: Table [dbo].[Table_Test1] Script Date: 09/25/2006
15:19:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_Test1](
[Test1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Test1b] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Table_Test1] PRIMARY KEY CLUSTERED
(
[Test1] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



sp_addarticle is just the command in its most basic form.

pub and subsc were already created via the EM

"Hilary Cotter" wrote:

As I don't have your table schema, nor do I have your publication script,
nor access to your server I had to do a proof of concept using bogus
table
names.

When I issue a sp_addarticle statement I do not need to use the
force_invalidate_snapshot switch. Could you possibly post the schema of
the
problem table as well as your sp_addarticle, sp_addpublication, and
sp_addsubscriptions statements?


--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



"Methodology" <Methodology@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4EB586CD-EB20-4F92-B63A-A4163521EFEE@xxxxxxxxxxxxxxxx
Hilary

My database, tables and pub are already created, so i'm sorry, but what
should I run?



"Hilary Cotter" wrote:

Only the snapshot for the newly added article will be genereated if
you
use
a script. If you use EM it could be the entire snapshot.

This is for plain vanilla transactional. If you are using updateable
subscriptions it could be different.

Here is a repro.

create database paul
GO
create database PaulSub
GO
Use Paul
GO
create table table1(pk int not null primary key, charcol char(20))
GO
sp_replicationdboption 'paul','publish','true'
GO
sp_addpublication 'paul',@status='active'
go
sp_addpublication_snapshot 'paul'
GO
sp_addarticle 'paul','table1','table1'
GO
sp_addsubscription 'paul','table1',@@servername,'paulsub'
GO
--ensure the snapshot is deployed
select * from paulsub.dbo.table1
--creating table2
create table table2(pk int not null primary key, charcol char(20))
GO
sp_addarticle 'paul','table2','table2'
GO
sp_refreshsubscriptions 'paul'
GO
sp_addsubscription 'paul','table2',@@servername,'paulsub'
GO
--ensure the snapshot is deployed
select * from paulsub.dbo.table2
--the message I see in the distribution agent is for 1 table - table2.




--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business
intelligence.

This posting is my own and doesn't necessarily represent
RelevantNoise's
positions, strategies or opinions.

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



"Methodology" <Methodology@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F1E3A517-C799-42BD-8C58-9C59CE91E882@xxxxxxxxxxxxxxxx
Hi

Im using sp_addarticle to add a new object to my pub - this works
fine.
I
go
to look at the properties of the publication, and my new artticle is
sitting
there with a tick. Great. I am however told that I must set the
force_invalidate to 1....When I re-run the agent from the EM console
(by
right clicking on the relevant pub etc) a new snap of the entire
dbase
is
created.

Hilary allued to the fact that I can add an article and then just
create a
snap of that new article, and not the entire dbase all over again

Can someone tell me how to do this please? - this is a deal breaker
ie
if
I
have to snap EVERYTHING all over again when I add an article, im
going
to
have to go and give Wansync 10 Grand..

Thanks

Alastair Jones.








.

Relevant Pages

  • Re: Invalid Descriptor Index
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... "Hilary Cotter" wrote: ...
    (microsoft.public.sqlserver.replication)
  • Re: Backup failure due to full-text indexing
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Shift the backup log after backup database (it was starting at the same ... "Hilary Cotter" wrote: ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Fulltext and cluster - odd problem
    ... Hilary Cotter ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... [Location of errorlog files] ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Web Synchronization over HTTPS fails
    ... Director of Text Mining and Database Strategy ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... Looking for a SQL Server replication book? ... "Hilary Cotter" skrev: ...
    (microsoft.public.sqlserver.replication)
  • Re: sp_addarticle without re-creating entire new snapshot
    ... "Hilary Cotter" wrote: ... Director of Text Mining and Database Strategy ... Use Paul ...
    (microsoft.public.sqlserver.replication)
Loading