You are able to submit the data in the table using the application like InfoPath 2007 but when you go ahead and add the table in the Merge Replication Publication, the insert into the table using the InfoPath form starts to fail. This issue occurs when we have the application designed to insert data into multiple tables and the tables have master-child relationship with primary key in the tables set for identity value.
The error message you will see is similar to the below,
Error Message:-
InfoPath cannot submit the form.
An error occurred while the form was being submitted.
Cannot insert the value NULL into column ‘<Column_Name>’, table ‘<Table_Name>’; column does not allow nulls. INSERT fails.
When you remove the article from Replication, you will find the insert statement working again.
If you take an SQL Profiler during the failure on SQL Server, You can find that the application runs queries similar to the below,
SELECT @@identity
Then it inserts into the child table using the value returned by the above “SELECT @@identity” statement
The query “SELECT @@identity” returns incorrect results from the application perspective and the insert fails. This result is different because the Replication creates Triggers on the table which goes ahead and inserts data into the replication metadata tables during the operation. This increases the identity value and hence the application gets the updated identity value when it tries to insert into the child table. This causes the failure of the insert.
This is a known issue with ADO 2.8. The KB 951937 article provides a hotfix to correct this issue,
On a computer that is running Windows Vista, Windows Server 2008, or Windows XP, an incorrect value is returned when an application queries the identity column value of a newly inserted row in various versions of SQL Server 2005 and of SQL Server 2000
http://support.microsoft.com/?id=951937