Microsoft KB Archive/330582

= PRB: &quot;No Data Was Provided for Column&quot; Error Message When You Propagate Identity Column with SQLXMLBulkLoad =

Article ID: 330582

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q330582



SYMPTOMS
When you try to use the SQLXMLBulkLoad object to propagate identity value, you receive an error message that is similar to the following:

No data was provided for column  on table , and this column cannot contain NULL values



RESOLUTION
To work around this problem, specify values for the parent table identity column in the XML data, and set the XML Bulk Load KeepIdentity property to True before you execute the Bulk Load. When you set the KeepIdentity property to True, the values that the user supplies are inserted in the identity column in the parent table, and therefore, the identity values are propagated to the child table(s). If you set the KeepIdentity property to False, and you do not supply values for the parent table identity column in the XML data, or you do both, the identity values are not propagated to the child table(s).

Other methods to work around this issue are to use updategrams, or to use views and triggers for the population.



STATUS
This behavior is by design.



MORE INFORMATION
Based on the specifications, the functionality for identity columns is not supported. The following excerpt is from the &quot;XML for SQL&quot; documentation:

&quot;Bulk load will NOT automatically generate ID values when linking data through one-to-many or many-to-many relationships. They are assumed to be present in the data file. While Bulk Load can be used to allow SQL Server to generate identity values, Bulk Load will not use such generated values when filling in ID's linking tables together. So in general, the user should specify the IDs explicitly in the XML data stream and specify to Bulk Load that identity values should be kept&quot;

