PRB: "No Data Was Provided for Column" Error Message When You Propagate Identity Column with SQLXMLBulkLoad (330582)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)

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 ua ref on table Main User Info, 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 "XML for SQL" documentation:

"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"

REFERENCES

For additional information, see the following Microsoft Developer Network (MSDN) Web sites:

XML Bulk Load Overview

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sqlxml_bulkloadover.asp

Performing Bulk Load of XML Data

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp

XML for SQL Server 2000 Web Release 1

http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/554/msdncompositedoc.xml

Updating Data by Using XML Updategrams

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/updategram_0i5v.asp

Modification Type:	Major	Last Reviewed:	10/3/2003
Keywords:	kbprb KB330582