Re: sp_addarticle without re-creating entire new snapshot

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Publications are named.

note that table_test1 is now listed but thats because i re-gen the entire
snap etc etc though it doesnt matter what I do - i HAVE to inval the snap to
add an article, and then when I run the agent, i get the whole DB snapped
again...(if you follow me - my head is a little fuzzy as I have been working
on this all day...)

-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'timeseries_dev', @optname =
N'publish', @value = N'true'
GO

exec [timeseries_dev].sys.sp_addlogreader_agent @job_login =
N'methodologygrou\*****', @job_password = null, @publisher_security_mode = 1
GO
exec [timeseries_dev].sys.sp_addqreader_agent @job_login = null,
@job_password = null, @frompublisher = 1
GO
-- Adding the transactional publication
use [timeseries_dev]
exec sp_addpublication @publication = N'Timeseries', @description =
N'Transactional publication of database ''timeseries_dev'' from Publisher
''METHODOLOGY5''.', @sync_method = N'concurrent', @retention = 0, @allow_push
= N'true', @allow_pull = N'true', @allow_anonymous = N'true',
@enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',
@allow_subscription_copy = N'false', @add_to_active_directory = N'false',
@repl_freq = N'continuous', @status = N'active', @independent_agent =
N'true', @immediate_sync = N'true', @allow_sync_tran = N'false',
@autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts =
N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false',
@enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @publication = N'Timeseries',
@frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval =
0, @frequency_recurrence_factor = 0, @frequency_subday = 0,
@frequency_subday_interval = 0, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date =
0, @job_login = N'methodologygrou\mgproc', @job_password = null,
@publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'Timeseries', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'Timeseries', @login = N'NT
AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'Timeseries', @login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'Timeseries', @login =
N'METHODOLOGY5\SQLServer2005SQLAgentUser$METHODOLOGY5$MSSQLSERVER'
GO
exec sp_grant_publication_access @publication = N'Timeseries', @login =
N'METHODOLOGY5\SQLServer2005MSSQLUser$METHODOLOGY5$MSSQLSERVER'
GO
exec sp_grant_publication_access @publication = N'Timeseries', @login =
N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'Timeseries', @login =
N'*****'
GO

-- Adding the transactional articles
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'RT_LAST_VALUES', @source_owner = N'dbo', @source_object =
N'RT_LAST_VALUES', @type = N'logbased', @description = N'', @creation_script
= N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none', @destination_table =
N'RT_LAST_VALUES', @destination_owner = N'dbo', @status = 24,
@vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboRT_LAST_VALUES]', @del_cmd = N'CALL
[sp_MSdel_dboRT_LAST_VALUES]', @upd_cmd = N'SCALL
[sp_MSupd_dboRT_LAST_VALUES]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'Table_Test1',
@source_owner = N'dbo', @source_object = N'Table_Test1', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption =
N'none', @destination_table = N'Table_Test1', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTable_Test1]', @del_cmd = N'CALL [sp_MSdel_dboTS_EXPR_001]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_EXPR_001]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'table_test2',
@source_owner = N'dbo', @source_object = N'Table_Test2', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x0000000000030073, @identityrangemanagementoption =
N'none', @destination_table = N'table_test2', @destination_owner = N'dbo',
@status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dbotable_test2]', @del_cmd = N'CALL [sp_MSdel_dbotable_test2]',
@upd_cmd = N'CALL [sp_MSupd_dbotable_test2]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'TS_EXPR_001',
@source_owner = N'dbo', @source_object = N'TS_EXPR_001', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption =
N'none', @destination_table = N'TS_EXPR_001', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTS_EXPR_001]', @del_cmd = N'CALL [sp_MSdel_dboTS_EXPR_001]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_EXPR_001]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'TS_EXPR_002',
@source_owner = N'dbo', @source_object = N'TS_EXPR_002', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption =
N'none', @destination_table = N'TS_EXPR_002', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTS_EXPR_002]', @del_cmd = N'CALL [sp_MSdel_dboTS_EXPR_002]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_EXPR_002]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'TS_EXPR_003',
@source_owner = N'dbo', @source_object = N'TS_EXPR_003', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption =
N'none', @destination_table = N'TS_EXPR_003', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTS_EXPR_003]', @del_cmd = N'CALL [sp_MSdel_dboTS_EXPR_003]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_EXPR_003]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TS_NTUPLE_001', @source_owner = N'dbo', @source_object = N'TS_NTUPLE_001',
@type = N'logbased', @description = N'', @creation_script = N'',
@pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none', @destination_table =
N'TS_NTUPLE_001', @destination_owner = N'dbo', @status = 24,
@vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTS_NTUPLE_001]', @del_cmd = N'CALL [sp_MSdel_dboTS_NTUPLE_001]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_NTUPLE_001]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'TS_NUMQ_001',
@source_owner = N'dbo', @source_object = N'TS_NUMQ_001', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption =
N'none', @destination_table = N'TS_NUMQ_001', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTS_NUMQ_001]', @del_cmd = N'CALL [sp_MSdel_dboTS_NUMQ_001]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_NUMQ_001]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'TS_NUMQ_002',
@source_owner = N'dbo', @source_object = N'TS_NUMQ_002', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption =
N'none', @destination_table = N'TS_NUMQ_002', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTS_NUMQ_002]', @del_cmd = N'CALL [sp_MSdel_dboTS_NUMQ_002]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_NUMQ_002]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'TS_NUMQ_003',
@source_owner = N'dbo', @source_object = N'TS_NUMQ_003', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption =
N'none', @destination_table = N'TS_NUMQ_003', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTS_NUMQ_003]', @del_cmd = N'CALL [sp_MSdel_dboTS_NUMQ_003]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_NUMQ_003]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'TS_NUMQ_004',
@source_owner = N'dbo', @source_object = N'TS_NUMQ_004', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption =
N'none', @destination_table = N'TS_NUMQ_004', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTS_NUMQ_004]', @del_cmd = N'CALL [sp_MSdel_dboTS_NUMQ_004]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_NUMQ_004]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'TS_NUMQ_005',
@source_owner = N'dbo', @source_object = N'TS_NUMQ_005', @type = N'logbased',
@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F, @identityrangemanagementoption =
N'none', @destination_table = N'TS_NUMQ_005', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboTS_NUMQ_005]', @del_cmd = N'CALL [sp_MSdel_dboTS_NUMQ_005]',
@upd_cmd = N'SCALL [sp_MSupd_dboTS_NUMQ_005]'
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article = N'TsBulkLoad',
@source_owner = N'dbo', @source_object = N'TsBulkLoad', @type = N'proc schema
only', @description = N'', @creation_script = N'', @pre_creation_cmd =
N'drop', @schema_option = 0x0000000008000001, @destination_table =
N'TsBulkLoad', @destination_owner = N'dbo', @status = 16
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TSFetch_EXPR_001', @source_owner = N'dbo', @source_object =
N'TSFetch_EXPR_001', @type = N'proc schema only', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000001, @destination_table = N'TSFetch_EXPR_001',
@destination_owner = N'dbo', @status = 16
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TSFetch_EXPR_002', @source_owner = N'dbo', @source_object =
N'TSFetch_EXPR_002', @type = N'proc schema only', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000001, @destination_table = N'TSFetch_EXPR_002',
@destination_owner = N'dbo', @status = 16
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TSFetch_EXPR_003', @source_owner = N'dbo', @source_object =
N'TSFetch_EXPR_003', @type = N'proc schema only', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000001, @destination_table = N'TSFetch_EXPR_003',
@destination_owner = N'dbo', @status = 16
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TSFetch_NTUPLE_001', @source_owner = N'dbo', @source_object =
N'TSFetch_NTUPLE_001', @type = N'proc schema only', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000001, @destination_table = N'TSFetch_NTUPLE_001',
@destination_owner = N'dbo', @status = 16
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TSFetch_NUMQ_001', @source_owner = N'dbo', @source_object =
N'TSFetch_NUMQ_001', @type = N'proc schema only', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000001, @destination_table = N'TSFetch_NUMQ_001',
@destination_owner = N'dbo', @status = 16
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TSFetch_NUMQ_002', @source_owner = N'dbo', @source_object =
N'TSFetch_NUMQ_002', @type = N'proc schema only', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000001, @destination_table = N'TSFetch_NUMQ_002',
@destination_owner = N'dbo', @status = 16
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TSFetch_NUMQ_003', @source_owner = N'dbo', @source_object =
N'TSFetch_NUMQ_003', @type = N'proc schema only', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000001, @destination_table = N'TSFetch_NUMQ_003',
@destination_owner = N'dbo', @status = 16
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TSFetch_NUMQ_004', @source_owner = N'dbo', @source_object =
N'TSFetch_NUMQ_004', @type = N'proc schema only', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000001, @destination_table = N'TSFetch_NUMQ_004',
@destination_owner = N'dbo', @status = 16
GO
use [timeseries_dev]
exec sp_addarticle @publication = N'Timeseries', @article =
N'TSFetch_NUMQ_005', @source_owner = N'dbo', @source_object =
N'TSFetch_NUMQ_005', @type = N'proc schema only', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000001, @destination_table = N'TSFetch_NUMQ_005',
@destination_owner = N'dbo', @status = 16
GO

-- Adding the transactional subscriptions
use [timeseries_dev]
exec sp_addsubscription @publication = N'Timeseries', @subscriber =
N'MG-DR02', @destination_db = N'timeseries_dev', @subscription_type =
N'Pull', @sync_type = N'automatic', @article = N'all', @update_mode = N'read
only', @subscriber_type = 0
GO



"Hilary Cotter" wrote:

Drill down on your database in EM, open up the publications folder, right
click on it and select script publication. Then post it here. Are your
publications anonymous or named?

--
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:E0EF25C1-243B-4D23-9222-37EECE224E3A@xxxxxxxxxxxxxxxx
Not sure I know how to - I can script the pub out and post..?

"Hilary Cotter" wrote:

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: Log wont Truncate when Publication is created
    ... would prevent the logs from truncating when a snapshot publication exists? ... "Hilary Cotter" wrote: ... recreating the snapshot publication. ... database is almost completely rebuilt with each update, ...
    (microsoft.public.sqlserver.replication)
  • Re: sp_addarticle without re-creating entire new snapshot
    ... Hilary Cotter ... RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. ... without invalidating the snap. ... whether the immediate_sync publication property is true or not ...
    (microsoft.public.sqlserver.replication)
  • Re: sp_addarticle without re-creating entire new snapshot
    ... My database, tables and pub are already created, so i'm sorry, but what ... "Hilary Cotter" wrote: ... Use Paul ... right clicking on the relevant pub etc) a new snap of the entire dbase is ...
    (microsoft.public.sqlserver.replication)
  • Re: Restoring Merge Repolication... HELP!
    ... restore the publication database into another database with the ... Hilary Cotter ... > I accidentaly remove a publication, I need to restore it!!! ...
    (microsoft.public.sqlserver.replication)
  • Re: Error 14058: The subscription already exists
    ... I had to delete the pull subscription manually ... from the Publisher database. ... "Hilary Cotter" wrote: ... >> manually a pull subscription to a publication. ...
    (microsoft.public.sqlserver.replication)