Article in publication not replicating

I have transactional replication set up on SQL Server 2000 SP3a on a Windows
2003 server. The replicated server is also SQL Server 2000 SP3a with Windows
2003. One of the publications contains 6 articles. This publication was
configured and pushed to the subscriber all at the same time. One of the 6
articles was not sending data to the subscriber and there are was no messages
indicating that there was a problem. This is a production critical
application so when I found the error, I dropped the article and recreated
it. It is now working fine.
Has anyone ever run into this type of issue? I am at a loss to explain why
only one article was not replicating.
How was the publication created in the first place? For example did you add
it later using sp_addarticle, or was it added with the other articles in the
beginning?
If you use sp_addarticle you have to issue a sp_refreshpublications after
doing it, for the subscriber to be updated.
Hilary Cotter
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
"gert" <gert@.discussions.microsoft.com> wrote in message
news:D3F7320E-33FB-467D-9058-2FD56D6207B5@.microsoft.com...
>I have transactional replication set up on SQL Server 2000 SP3a on a
>Windows
> 2003 server. The replicated server is also SQL Server 2000 SP3a with
> Windows
> 2003. One of the publications contains 6 articles. This publication was
> configured and pushed to the subscriber all at the same time. One of the
> 6
> articles was not sending data to the subscriber and there are was no
> messages
> indicating that there was a problem. This is a production critical
> application so when I found the error, I dropped the article and recreated
> it. It is now working fine.
> Has anyone ever run into this type of issue? I am at a loss to explain
> why
> only one article was not replicating.
|||All the articles were added at the same time and pushed to the subscriber at
the same time. That is what is so confusing about this incident. I am aware
that when a article is added that it needed to be pushed to the subscriber.
I just cannot determine why only one of the articles disn't appear to be
pushed to the subscriber.
I have a customer looking for an explanation and I am at a loss.
"Hilary Cotter" wrote:

> How was the publication created in the first place? For example did you add
> it later using sp_addarticle, or was it added with the other articles in the
> beginning?
> If you use sp_addarticle you have to issue a sp_refreshpublications after
> doing it, for the subscriber to be updated.
> --
> Hilary Cotter
> 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
> "gert" <gert@.discussions.microsoft.com> wrote in message
> news:D3F7320E-33FB-467D-9058-2FD56D6207B5@.microsoft.com...
>
>
|||can you script out the tables and post them here?
I take it that you are doing plain vanilla transactional replication.
Hilary Cotter
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
"gert" <gert@.discussions.microsoft.com> wrote in message
news:E52DD2BF-5D3D-40BB-B997-F42CC43ED7B4@.microsoft.com...[vbcol=seagreen]
> All the articles were added at the same time and pushed to the subscriber
> at
> the same time. That is what is so confusing about this incident. I am
> aware
> that when a article is added that it needed to be pushed to the
> subscriber.
> I just cannot determine why only one of the articles disn't appear to be
> pushed to the subscriber.
> I have a customer looking for an explanation and I am at a loss.
>
> "Hilary Cotter" wrote:
|||this is the script. This 1st article is the one that did not replicate.
-- Adding the transactional articles
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_RPO',
@.source_owner = N'dbo',
@.source_object = N'VR_RPO',
@.destination_table = N'VR_RPO',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_SIR',
@.source_owner = N'dbo',
@.source_object = N'VR_SIR',
@.destination_table = N'VR_SIR',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_SP',
@.source_owner = N'dbo',
@.source_object = N'VR_SP',
@.destination_table = N'VR_SP',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_SVI',
@.source_owner = N'dbo',
@.source_object = N'VR_SVI',
@.destination_table = N'VR_SVI',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL', @.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_VEH_COMP',
@.source_owner = N'dbo',
@.source_object = N'VR_VEH_COMP',
@.destination_table = N'VR_VEH_COMP',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'SQL',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
exec sp_addarticle
@.publication = N'VEHICLE-Config4',
@.article = N'VR_VEH_COMP_HIST',
@.source_owner = N'dbo',
@.source_object = N'VR_VEH_COMP_HIST',
@.destination_table = N'VR_VEH_COMP_HIST',
@.type = N'logbased',
@.creation_script = null,
@.description = null,
@.pre_creation_cmd = N'none',
@.schema_option = 0x00000000000000F3,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'SQL',
@.del_cmd = N'NONE',
@.upd_cmd = N'SQL',
@.filter = null,
@.sync_object = null,
@.auto_identity_range = N'false'
GO
-- Adding the transactional subscription
exec sp_addsubscription
@.publication = N'VEHICLE-Config4',
@.article = N'all',
@.subscriber = N'CAIGSC024',
@.destination_db = N'vehicle',
@.sync_type = N'none',
@.update_mode = N'read only',
@.offloadagent = 0,
@.dts_package_location = N'distributor'
GO
"Hilary Cotter" wrote:

> can you script out the tables and post them here?
> I take it that you are doing plain vanilla transactional replication.
> --
> Hilary Cotter
> 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
> "gert" <gert@.discussions.microsoft.com> wrote in message
> news:E52DD2BF-5D3D-40BB-B997-F42CC43ED7B4@.microsoft.com...
>
>
|||I was more interested in the schemas of the tables you are replicating!
Hilary Cotter
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
"gert" <gert@.discussions.microsoft.com> wrote in message
news:48D96A93-6572-4BF7-839C-51437D2ED31D@.microsoft.com...[vbcol=seagreen]
> this is the script. This 1st article is the one that did not replicate.
> -- Adding the transactional articles
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_RPO',
> @.source_owner = N'dbo',
> @.source_object = N'VR_RPO',
> @.destination_table = N'VR_RPO',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_SIR',
> @.source_owner = N'dbo',
> @.source_object = N'VR_SIR',
> @.destination_table = N'VR_SIR',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_SP',
> @.source_owner = N'dbo',
> @.source_object = N'VR_SP',
> @.destination_table = N'VR_SP',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_SVI',
> @.source_owner = N'dbo',
> @.source_object = N'VR_SVI',
> @.destination_table = N'VR_SVI',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL', @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_VEH_COMP',
> @.source_owner = N'dbo',
> @.source_object = N'VR_VEH_COMP',
> @.destination_table = N'VR_VEH_COMP',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'SQL',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> exec sp_addarticle
> @.publication = N'VEHICLE-Config4',
> @.article = N'VR_VEH_COMP_HIST',
> @.source_owner = N'dbo',
> @.source_object = N'VR_VEH_COMP_HIST',
> @.destination_table = N'VR_VEH_COMP_HIST',
> @.type = N'logbased',
> @.creation_script = null,
> @.description = null,
> @.pre_creation_cmd = N'none',
> @.schema_option = 0x00000000000000F3,
> @.status = 16,
> @.vertical_partition = N'false',
> @.ins_cmd = N'SQL',
> @.del_cmd = N'NONE',
> @.upd_cmd = N'SQL',
> @.filter = null,
> @.sync_object = null,
> @.auto_identity_range = N'false'
> GO
> -- Adding the transactional subscription
> exec sp_addsubscription
> @.publication = N'VEHICLE-Config4',
> @.article = N'all',
> @.subscriber = N'CAIGSC024',
> @.destination_db = N'vehicle',
> @.sync_type = N'none',
> @.update_mode = N'read only',
> @.offloadagent = 0,
> @.dts_package_location = N'distributor'
> GO
> "Hilary Cotter" wrote:
subscriber[vbcol=seagreen]
be[vbcol=seagreen]
you[vbcol=seagreen]
in[vbcol=seagreen]
after[vbcol=seagreen]
with[vbcol=seagreen]
publication[vbcol=seagreen]
of[vbcol=seagreen]
no[vbcol=seagreen]
explain[vbcol=seagreen]
|||Here is the table schema, and yes I am doing vanilla transaction replication.
We have actually been replicating this data for 3 years, but just recently
moved to a Windows 2003 server.
CREATE TABLE VR_RPO (
RPO_CODE varchar (3) NOT NULL ,
FKVBI_PVI varchar (9) NOT NULL ,
CONSTRAINT PKVRRPO PRIMARY KEY NONCLUSTERED
(
FKVBI_PVI,
RPO_CODE
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_SIR (
ID varchar (3) NOT NULL ,
INSTRUCTION_DATA varchar (56) NULL ,
TYPE varchar (1) NULL ,
SEQUENCE_NUMBER varchar (2) NULL ,
FKVBI_PVI varchar (9) NOT NULL ,
CONSTRAINT PKVRSIR PRIMARY KEY NONCLUSTERED
(
FKVBI_PVI,
ID
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_SP (
KEY1 varchar (8) NOT NULL ,
Key2 varchar (10) NOT NULL ,
PVI char (9) NOT NULL ,
Data varchar (65) NULL ,
Update_Date_Time datetime NULL ,
CONSTRAINT PKVRSP PRIMARY KEY CLUSTERED
(
KEY1,
Key2,
PVI
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_SVI (
SVI_TYPE varchar (10) NOT NULL ,
SVI_VALUE varchar (20) NOT NULL ,
PVI varchar (9) NOT NULL ,
CONSTRAINT PKVRSVI PRIMARY KEY NONCLUSTERED
(
SVI_TYPE,
SVI_VALUE
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_VEH_COMP (
PVI varchar (9) NOT NULL ,
COMPONENT varchar (10) NOT NULL ,
PASSFAIL char (1) NOT NULL ,
COMPDATA varchar (20) NOT NULL ,
CREATE_DATE_TIME datetime NOT NULL ,
UPDATE_DATE_TIME datetime NULL ,
CONSTRAINT PKVRVEHCOMP PRIMARY KEY CLUSTERED
(
PVI,
COMPONENT
) WITH FILLFACTOR = 90
)
GO
CREATE TABLE VR_VEH_COMP_HIST (
PVI varchar (9) NOT NULL ,
COMPONENT varchar (10) NOT NULL ,
PASSFAIL char (1) NOT NULL ,
COMPDATA varchar (20) NOT NULL ,
CREATE_DATE_TIME datetime NOT NULL ,
UPDATE_DATE_TIME datetime NULL ,
CONSTRAINT PKVRVEHCOMHIS PRIMARY KEY CLUSTERED
(
PVI,
COMPONENT
) WITH FILLFACTOR = 90
)
GO
"Hilary Cotter" wrote:

> I was more interested in the schemas of the tables you are replicating!
> --
> Hilary Cotter
> 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
> "gert" <gert@.discussions.microsoft.com> wrote in message
> news:48D96A93-6572-4BF7-839C-51437D2ED31D@.microsoft.com...
> subscriber
> be
> you
> in
> after
> with
> publication
> of
> no
> explain
>
>