xp cmdshell: bulkload

I am having a problem importing XML documents into SQL using SQLXML Bulkload with an annotated XSD schema. I have three, hierarchical tables in a SQL 2000 DB: tblBatches -> tblRecords -> tblDetails. The XML document I want to import does not contain identity fields. So I am using '.KeepIdentity = False' in my ActiveX VB Script, so SQL Server will generate the key fields for me. When I run the script, the message I get is 'No data was provided for column 'record_ID' on table 'tblDetails', and this column cannot contain NULL values.' So, basically, it's not inserting the matching foreign key generated for tblRecords into tblDetails.
I have been playing around with this for several days, and if I remove all reference to the third table in the hierarchy, tblDetails, from my XSD schema, the data imports and SQL successfully inserts the identity fields for me for the first two tables. If it will insert the identity fields for two tables, why is it having so much trouble doing it for three? I thought SQLXML 3.0 SP2 was supposed to fix the identity propagation problem. I am using SQLXML 3.0 SP2, Windows 2000, VS.NET 2002, and SQL Server 2000.
I am pasting my VB code, XSD schema, and XML document below. I will also attach copies of them, if that is easier.
Here is the VB code I am using to run the bulkload:
Dim BulkLoad As SQLXMLBulkLoad3
Try
'Create the new XML Bulk Load object
BulkLoad = New SQLXMLBulkLoad3()
'Set the connection string.
BulkLoad.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;User ID=mmsuser;Initial Catalog=MMS;Data Source=NDEWITT\NetSDK;"
'Log any errors.
BulkLoad.ErrorLogFile = "C:\TestSQLErrorLog.xml"
'Have it automatically add the identity fields.
BulkLoad.KeepIdentity = False
BulkLoad.CheckConstraints = True
BulkLoad.Transaction = False
'And perform the data import.
BulkLoad.Execute("C:\TestMMSSchemaNETxsd.xsd", _
"C:\TestXDRNoIdentity.xml")
'Clean up.
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
BulkLoad = Nothing
End Try
Here is my XSD schema:
<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:msch="urn:schemas-microsoft-com:mapping-schema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BatchRec" parent="tblBatches" parent-key="batch_ID" child="tblRecords" child-key="batch_ID" />
<sql:relationship name="RecDetail" parent="tblRecords" parent-key="record_ID" child="tblDetails" child-key="record_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="statement_batch" sql:relation="tblBatches" sql:key-fields="batch_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="print_batch_key" type="xs:string" />
<xs:element name="print_batch_type_value" type="xs:string" />
<xs:element name="client_program_key" type="xs:string" />
<xs:element name="print_item_quantity" type="xs:int" />
<xs:element name="start_date" type="xs:date" />
<xs:element name="end_date" type="xs:date" />
<xs:element name="client_key" type="xs:string" />
<xs:element name="client_name" type="xs:string" />
<xs:element name="program_key" type="xs:string" />
<xs:element name="program_name" type="xs:string" />
<xs:element name="return_address_line_1" type="xs:string" />
<xs:element name="return_address_line_2" type="xs:string" />
<xs:element name="return_city" type="xs:string" />
<xs:element name="return_state" type="xs:string" />
<xs:element name="return_zip" type="xs:string" />
<xs:element name="miles_synonym" type="xs:string" />
<xs:element name="statement_synonym" type="xs:string" />
<xs:element name="member_id_synonym" type="xs:string" />
<xs:element name="member_synonym" type="xs:string" />
<xs:element name="redemption_http" type="xs:string" />
<xs:element name="redemption_phone" type="xs:string" />
<xs:element name="print_logo" type="xs:string" />
<xs:element name="run_date" type="xs:date" />
<xs:element name="custom_message" type="xs:string" />
<xs:element name="summary_text" type="xs:string" />
<xs:element name="format_type" type="xs:string" />
<xs:element name="envelope_type" type="xs:string" />
<xs:element name="paper_type" type="xs:string" />
<xs:element name="statement_record" sql:relation="tblRecords" sql:relationship="BatchRec" sql:key-fields="record_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="member_id" type="xs:string" />
<xs:element name="first_name" type="xs:string" />
<xs:element name="last_name" type="xs:string" />
<xs:element name="address_line_1" type="xs:string" />
<xs:element name="address_line_2" type="xs:string" />
<xs:element name="city" type="xs:string" />
<xs:element name="state" type="xs:string" />
<xs:element name="zip" type="xs:string" />
<xs:element name="country_id" type="xs:string" />
<xs:element name="employer" type="xs:string" />
<xs:element name="begin_balance" type="xs:int" />
<xs:element name="award_activity" type="xs:int" />
<xs:element name="redeem_activity" type="xs:int" />
<xs:element name="expired_miles" type="xs:int" />
<xs:element name="adjusted_miles" type="xs:int" />
<xs:element name="end_balance" type="xs:int" />
<xs:element name="unvested_balance" type="xs:int" />
<xs:element name="detail_record" sql:relation="tblDetails" sql:relationship="RecDetail" sql:key-fields="detail_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="effective_date" type="xs:date" />
<xs:element name="activity_type" type="xs:unsignedByte" />
<xs:element name="activity_description" type="xs:string" />
<xs:element name="activity_miles" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MMSSchema" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="statement_batch" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Here is a sample XML document I want to import:
<statement_batch>
<print_batch_key>609167</print_batch_key>
<print_batch_type_value>Member Statement</print_batch_type_value>
<client_program_key>857148</client_program_key>
<print_item_quantity>108</print_item_quantity>
<start_date>02-13-2004</start_date>
<end_date>03-04-2004</end_date>
<client_key>317229</client_key>
<client_name>CARLSON SYSTEMS</client_name>
<program_key>857143</program_key>
<program_name>Carlson Systems -The Green Mile</program_name>
<return_address_line_1>3475 Piedmont Rd. NE</return_address_line_1>
<return_address_line_2>Suite 300</return_address_line_2>
<return_city>Atlanta</return_city>
<return_state>GA</return_state>
<return_zip>30305</return_zip>
<miles_synonym>Points</miles_synonym>
<statement_synonym>Statement</statement_synonym>
<member_id_synonym>Participant ID</member_id_synonym>
<member_synonym>Participant</member_synonym>
<redemption_http>www.webrewards.com</redemption_http>
<redemption_phone>Merchandise- (877) 690-2080; Travel- (800) 210-8539</redemption_phone>
<print_logo>http://www.webrewards.com/carlson_sy...sonSystems.jpg</print_logo>
<run_date>03-04-2004</run_date>
<custom_message><![CDATA[
<BR>
<BR>]]></custom_message>
<summary_text>
</summary_text>
<format_type>STATEMENT_DETAIL_LOGO</format_type>
<envelope_type>PISTOL_STATEMENT</envelope_type>
<paper_type>STATEMENT_OFFSET_GRAY</paper_type>
<statement_record>
<member_id>859953</member_id>
<first_name>MICHAEL</first_name>
<last_name>STAPLES</last_name>
<address_line_1>4300 DELLWOOD LANE</address_line_1>
<address_line_2>
</address_line_2>
<city>MOUND</city>
<state>MN</state>
<zip>55364</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>313152</begin_balance>
<award_activity>21924</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>335076</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>176</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>265</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1344</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>235</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>3103</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>5435</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>154</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>66</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>500</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>597</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1014</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>756</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>56</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>490</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>212</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1875</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>256</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>240</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>202</activity_miles>
</detail_record>
</statement_record>
<statement_record>
<member_id>859954</member_id>
<first_name>M</first_name>
<last_name>LEGAULT</last_name>
<address_line_1>13156 BITTERSWEET ST NW</address_line_1>
<address_line_2>
</address_line_2>
<city>COON RAPIDS</city>
<state>MN</state>
<zip>55448</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>91188</begin_balance>
<award_activity>5569</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>96757</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>80</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>105</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>891</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1543</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
</statement_record>
</statement_batch>
Any help will be greatly appreciated. Thanks!I discovered your post at Midtown Computer Systems Enterprise web site about SQLXMLBulkLoad and identity columns
in 3 hierarchical tables. I have having similar problems as the ones you described in your post. Were you able to find
a solution? If so, can you share it here? Thanks.sql

I use following config in my vb Script to BulkLoad Data and set up tables in
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>
You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:

> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.

Friday, March 23, 2012

Problem propagating identity fields for multiple tables in SQLXML Bulkload

Wednesday, March 21, 2012

Problem Primary Key will not be created?

Problem Primary Key will not be created?

xp cmdshell

Blog Archive

About Me