2

I am setting up a transactional replication in SQL Server. I am using custom stored procedures for the insert, update, and delete. I also am not replicating schema to the target, a table is already there. The tables do not match as the target contains audit columns. In the SP the values are all mapped.

When I run the snapshot it completes and there are no rows in undistributed. When I check the target table, no data has been replicated.

If I go and perform a transaction on source, it is replicated to the target. Why would the existing data not replicate?

Samples below, if you need something else from this let me know.

Source Table:

CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Value1] [varchar](50) NULL,
[Value2] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Target Table:

CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SRC_ID] [int] NOT NULL,
[VALUE1] [varchar](50) NULL,
[VALUE2] [varchar](50) NULL,
[SRC_DELETE] [bit] NOT NULL,
[TRAN_DT] [datetime] NOT NULL,
[PROCESS_FLAG] [bit] NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_SRC_DELETE]  DEFAULT ((0)) FOR [SRC_DELETE]
GO

ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_TRAN_DT]  DEFAULT (getdate()) FOR [TRAN_DT]
GO

ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_PROCESS_FLAG]  DEFAULT ((0)) FOR [PROCESS_FLAG]
GO

Delete SP

Create procedure [dbo].[rep_del__Table_1]
@pkc1 int
as
begin
declare @primarykey_text nvarchar(100) = ''
insert into [Target].dbo.Table_1
(SRC_ID, SRC_DELETE)
values (@pkc1, 1)
if @@rowcount = 0
if @@microsoftversion>0x07320000
Begin

set @primarykey_text = @primarykey_text + '[ID] = ' + convert(nvarchar(100),@pkc1,1)
exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[Table_1]', @param2=@primarykey_text, @param3=13234
End
end

Insert SP

CREATE PROCEDURE [dbo].[rep_ins__Table_1]
@c1 int,
@c2 varchar(50),
@c3 varchar(50)
as
begin
insert into [Target].dbo.Table_1
(SRC_ID, VALUE1, VALUE2)
values (@c1, @c2, @c3)
end

Update SP

CREATE procedure [dbo].[rep_upd__Table_1]
@c1 int,
@c2 varchar(50),
@c3 varchar(50),
@pkc1 int
as
begin  
declare @primarykey_text nvarchar(100) = ''
insert into [Target].dbo.Table_1
(SRC_ID, VALUE1, VALUE2)
values (@pkc1, @c2, @c3)
if @@rowcount = 0
if @@microsoftversion>0x07320000
Begin

set @primarykey_text = @primarykey_text + '[ID] = ' + convert(nvarchar(100),@pkc1,1)
exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[Table_1]', @param2=@primarykey_text, @param3=13233
End
end

sp_addpublication

exec sp_addpublication @publication = N'Sample', @description = N'Transactional publication of database ''Source'' from Publisher ''DESKTOP''.', 
@sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @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'false', 
@allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 0, 
@allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

sp_addarticle

exec sp_addarticle @publication = N'Sample', @article = N'Table_1', @source_owner = N'dbo', @source_object = N'Table_1', @type = N'logbased', 
@description = N'', @creation_script = N'', @pre_creation_cmd = N'truncate', @schema_option = 0x000000000203008D, @identityrangemanagementoption = N'manual', 
@destination_table = N'Table_1', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [rep_ins__Table_1]', 
@del_cmd = N'CALL [rep_del__Table_1]', @upd_cmd = N'CALL [rep_upd__Table_1]'

sp_addsubscription

exec sp_addsubscription @publication = N'Sample', @subscriber = N'DESKTOP', @destination_db = N'Target', @subscription_type = N'Push', 
@sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
1

Why would the existing data not replicate?

Because when you added the subscription using sp_addsubscription the value that you provided to the parameter @sync_type was N'replication support only', and according to Microsoft docs this value means:

Provides automatic generation at the Subscriber of article custom stored procedures and triggers that support updating subscriptions, if appropriate. Assumes that the Subscriber already has the schema and initial data for published tables.

References: sp_addsubscription (Transact-SQL) | Arguments (Microsoft Docs)

This means that you as publisher of the subscription are responsible that the initial data is already available at the subscriber, before the replication is set up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.