Friday, March 29, 2013

Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)

SQL Server 2005 :

Firstly, don't ask why I am still dealing with SQL Server 2005 in 2013. Sometimes it's difficult to upgrade the version because of financial as well as development constraints. But we are here to discuss the technical problems and not financial so let's get back to the problem on hand.

So for a while now I had snapshot replication running for SQL Server 2005 and had to set up a snapshot replication for one more database. Once I set up the replication I took a new snapshot but while delivering the snapshot I kept getting this error.

To fix this problem all I had to do was install Service Pack 4 and wolla!!! it started to work. If you are interested to know more about this then keep reading......

The problem was while creating a SP as part of the snapshot scripts. The Stored procedure had an INSERT statement. Since the Primary key was defined as IDENTITY columns (for that table) on the publisher, the column was also defined as 'NOT FOR REPLICATION' when set up for replication.

This means that on the subscriber also the column was defined similarly. But since the subscriber knows that it is set up for replication, it expects values for that columns to be explicitly mentioned in any INSERT statement. But this is not possible since the SP is on the publication and there you can not explicitly insert values for the IDENTITY column.

Alas, Microsoft had realized this long time ago and had provided the fix in SP4. So applied that and everything was working fine.

No comments:

Post a Comment