Did you know… The applications like Infopath may fail to submit data into table in SQL Server if the table is included in Merge replication

Did you know… The applications like Infopath may fail to submit data into table in SQL Server if the table is included in Merge replication

Rate This
  • Comments 1

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



Sanjaya Padhi
SE, Microsoft SQL Server

Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post