August 29, 2012 at 9:36 am
Hi all,
I have created a pull transactional replication where the replication was running fine until the following error occured for a particular table:
"Replication-Replication Distribution Subsystem:..Column name or number of supplied values does not match table definition."
I checked the table definition, the definition of the stored procedure sp_MSins_dboTable (which is trying to insert the data into the table) and also the values that are being inserted and didn't find any error. One of the columns in the destination table is an identity column and it already has "Not for replication" set to Yes. Both publisher and subscriber have sql 2008 r2, one is enterprise and one is standard edition.
I already deleted and recreated both the publication and subscription once, but the same problem is occuring for this table. Not sure what else I can do now.
Please provide your thoughts.
Thanks.
August 29, 2012 at 10:46 am
ryan_xh (8/29/2012)
Hi all,I have created a pull transactional replication where the replication was running fine until the following error occured for a particular table:
"Replication-Replication Distribution Subsystem:..Column name or number of supplied values does not match table definition."
I checked the table definition, the definition of the stored procedure sp_MSins_dboTable (which is trying to insert the data into the table) and also the values that are being inserted and didn't find any error. One of the columns in the destination table is an identity column and it already has "Not for replication" set to Yes. Both publisher and subscriber have sql 2008 r2, one is enterprise and one is standard edition.
I already deleted and recreated both the publication and subscription once, but the same problem is occuring for this table. Not sure what else I can do now.
Please provide your thoughts.
Thanks.
Not sure if this will have anything to do with it, as it usually generates a different error... but how are you managing the range of the identity for this? Manually?
Jared
CE - Microsoft
August 29, 2012 at 10:52 am
No the identity values are managed automatically at the publisher.
August 29, 2012 at 10:59 am
ryan_xh (8/29/2012)
No the identity values are managed automatically at the publisher.
So, it is possible that you exceeded the range... I would change it to manual if you want to let the publisher handle it.
Jared
CE - Microsoft
August 29, 2012 at 11:29 am
Sorry I was wrong, identity management for the article is Manual at the publisher. I also found the following:
Publisher range size = 0
Subscriber range size = 0
Range threshold percentage = 0
Maximum identity value: 2147483647
I think the maximum value should be the same at subscriber, right? The identity value that was being inserted (which gave the error) is around 70000.
Do I still need to check or change anything at the subscriber regarding this?
Thanks.
August 29, 2012 at 11:32 am
If it is set to Manual, I wouldn't worry about anything else with that. Let me go back and read your first post again and see if I missed anything to ask with the knowledge that I have.
Jared
CE - Microsoft
August 29, 2012 at 11:33 am
Are all columns being replicated?
Jared
CE - Microsoft
August 29, 2012 at 11:40 am
Yes, I selected all tables, views, SPs, functions and did not filter anything.
I am also giving the following information, if it helps to identify the problem:
The result of sp_helparticlecolumns for the article in publisher:
column published publisher type subscriber type
CUST 1 char(15) char(15)
CODE 1 char(15) char(15)
SNID 1 char(15) char(15)
ZONE 1 char(3) char(3)
MTHD 1 char(15) char(15)
TAXID 1 char(15) char(15)
NAME 1 char(61) char(61)
ADDR1 1 char(61) char(61)
ADDR2 1 char(61) char(61)
ADDR3 1 char(61) char(61)
COUNTRY 1 char(61) char(61)
CITY 1 char(35) char(35)
STATE 1 char(29) char(29)
ZIP 1 char(11) char(11)
PHONE1 1 char(21) char(21)
PHONE2 1 char(21) char(21)
PHONE3 1 char(21) char(21)
FAX 1 char(21) char(21)
ENTDT 1 datetime datetime
UPDDT 1 datetime datetime
INTID 1 char(31) char(31)
INTSRCE 1 smallint smallint
NINTID 1 char(31) char(31)
NCODE 1 char(7) char(7)
DLID 1 char(15) char(15)
LOCNUM 1 char(11) char(11)
ERR 1 char(15) char(15)
USER1 1 char(21) char(21)
USER2 1 char(21) char(21)
UQ_DT 1 datetime datetime
UQ_ID 1 int int
The script for sp_MSins_dbotableName is the following:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_MSins_dboTable]
@c1 char(15),
@c2 char(15),
@c3 char(15),
@c4 char(3),
@c5 char(15),
@c6 char(15),
@c7 char(61),
@c8 char(61),
@c9 char(61),
@c10 char(61),
@c11 char(61),
@c12 char(35),
@c13 char(29),
@c14 char(11),
@c15 char(21),
@c16 char(21),
@c17 char(21),
@c18 char(21),
@c19 datetime,
@c20 datetime,
@c21 char(31),
@c22 smallint,
@c23 char(31),
@c24 char(7),
@c25 char(15),
@c26 char(11),
@c27 char(15),
@c28 char(21),
@c29 char(21),
@c30 datetime,
@c31 int
as
begin
insert into [dbo].[Table](
[CUST],
,
[SNID],
[ZONE],
[MTHD],
[TAXID],
[NAME],
[ADDR1],
[ADDR2],
[ADDR3],
[COUNTRY],
[CITY],
[STATE],
[ZIP],
[PHONE1],
[PHONE2],
[PHONE3],
[FAX],
[ENTDT],
[UPDDT],
[INTID],
[INTSRCE],
[NINTID],
[NCODE],
[DLID],
[LOCNUM],
[ERR],
[USER1],
[USER2],
[UQ_DT],
[UQ_ID]
) values (
@c1,
@c2,
@c3,
@c4,
@c5,
@c6,
@c7,
@c8,
@c9,
@c10,
@c11,
@c12,
@c13,
@c14,
@c15,
@c16,
@c17,
@c18,
@c19,
@c20,
@c21,
@c22,
@c23,
@c24,
@c25,
@c26,
@c27,
@c28,
@c29,
@c30,
@c31 )
end
GO
The script for the table is also given below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table](
[CUST] [char](15) NOT NULL,
[char](15) NOT NULL,
[SNID] [char](15) NOT NULL,
[ZONE] [char](3) NOT NULL,
[MTHD] [char](15) NOT NULL,
[TAXID] [char](15) NOT NULL,
[NAME] [char](61) NOT NULL,
[ADDR1] [char](61) NOT NULL,
[ADDR2] [char](61) NOT NULL,
[ADDR3] [char](61) NOT NULL,
[COUNTRY] [char](61) NOT NULL,
[CITY] [char](35) NOT NULL,
[STATE] [char](29) NOT NULL,
[ZIP] [char](11) NOT NULL,
[PHONE1] [char](21) NOT NULL,
[PHONE2] [char](21) NOT NULL,
[PHONE3] [char](21) NOT NULL,
[FAX] [char](21) NOT NULL,
[ENTDT] [datetime] NOT NULL,
[UPDDT] [datetime] NOT NULL,
[INTID] [char](31) NOT NULL,
[INTSRCE] [smallint] NOT NULL,
[NINTID] [char](31) NOT NULL,
[NCODE] [char](7) NOT NULL,
[DLID] [char](15) NOT NULL,
[LOCNUM] [char](11) NOT NULL,
[ERR] [char](15) NOT NULL,
[USER1] [char](21) NOT NULL,
[USER2] [char](21) NOT NULL,
[UQ_DT] [datetime] NOT NULL,
[UQ_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
CONSTRAINT [PKTable] PRIMARY KEY NONCLUSTERED
(
[CUST] ASC,
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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Table] WITH CHECK ADD CHECK ((datepart(hour,[UPDDT])=(0) AND datepart(minute,[UPDDT])=(0) AND datepart(second,[UPDDT])=(0) AND datepart(millisecond,[UPDDT])=(0)))
GO
ALTER TABLE [dbo].[Table] WITH CHECK ADD CHECK ((datepart(hour,[ENTDT])=(0) AND datepart(minute,[ENTDT])=(0) AND datepart(second,[ENTDT])=(0) AND datepart(millisecond,[ENTDT])=(0)))
GO
ALTER TABLE [dbo].[Table] ADD DEFAULT (getutcdate()) FOR [UQ_DT]
GO
August 30, 2012 at 11:33 am
Well I tried to execute the stored procedure sp_MSins_dboTable (which was giving the error) with the same values in SSMS (with IDENTITY_INSERT ON) for the table and the values were inserted without any error. So this is very confusing, why the error is only happening when the stored procedure sp_MSins_dboTable is being run by replication agent? Can this be a permission issue?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply