Replication sync problem

Question

Hello all,

We are having problem setting up transactional replication for one database. The sync at the subscriber is failing with the following error:

"Column name or number of supplied values does not match table definition."

I already matched the source and destination tables and didn't find anything different. The only difference I found is in the actual command (which is giving the error) in the Distribution database with the same command that I captured with SQL profiler.

The following I found from the profiler (when the command executed against the subscription database):

exec [dbo].[sp_MSins_dbotable] '1234 ','Address ',' ',' ',' ','

', ','2011-01-01 00:00:00'

The following I found from the distribution database using sp_browsereplcmds:

{CALL [dbo].[sp_MSins_dbotable] '1234 ','Address ',' ',' ',' ','

',' ',2011-01-01 00:00:00.000

So the date data in the actual command in distribution database has milliseconds, but the command that executed against the subscription database does not have it. Can this be the problem? The collation of subscriber and publisher is same. Could you please provide some suggestions on this.

Thanks in advance.

Answer 1

That is not the problem. Can you open up the proc and verify that it has the same number of parameters as the execution statement.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

The procedure sp_MSins_dbotable was created automatically when I set up the replication. I also checked the number of parameters and it is fine. I recreated this subscription 3 times and its giving the same error. Previously we had a problem with the Distribution database, as its compatibility level was 90 (the sql server is 2008 r2), but I recreated the Distribution database by running the "disable publishing and distribution" wizard, but the sync fails with same problem.

Is there any other server or database level setting that I should match?

Thanks. 

Answer 3

Can I see the schema for this table?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Hilary,

I am giving the schemas for the article, the procedure and the destination table below.

The result of sp_helparticlecolumns:

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_dbotable 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],
[CODE],
[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 destination table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Table](
[CUST] [char](15) NOT NULL,
[CODE] [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,
[CODE] 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

Answer 5

Can  you try this:

declare @artid int
select @artid =artid From sysarticles where dest_table='Table1'
exec sp_scriptinsproc @artid

replace table1 with your actual table name.

Then copy the output and paste it on your subscriber.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

Thanks Hilary.

The thing is that when I created the transactional replication, the subscription was initialized from a backup file of the publication database (so the schema of the subscriber database should be exactly same). Do you still think the solution you provided might fix this problem?

I do not have the replication running at the moment as I had to delete the subscription due to lack of disk space. But I will create the subscription again and try your solution if you think the solution might fix this problem.

Thanks.  

Answer 7

They should be identical. I am guessing there was a schema change somewhere in the mix which caused this inconsistency. Regenerating the proc can help.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 8

I will try again and use the solution that you provided.

Thanks.