| ChrisRickard on Feb 18, 2008 at 3:39:35 PM (# 6) When do you get the error?
swjs on Feb 19, 2008 at 1:42:30 AM (# 7)Hi Chris,
I get the error during the .Execute on the SQLXMLBulkLoad object. Here is the SQL Server 2000 DTS VBScript :-
Function Bulk_Load_XML_File(s_Parm_XSD_File_Name, s_Parm_File_Name, s_Parm_Process_Message)
Dim objXBulkLoad
Dim b_Error_Encountered
'#
'### Assume No Errors will be encountered
'#
b_Error_Encountered = False
'#
'### Instantiate the XML Bulk Load Object
'#
Set objXBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.4.0")
'#
'### Apply the Database connection string
'#
objXBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB.1;SERVER=.;UID=whatever;PWD=yeahright;DATABASE=BIG_DB;"
'#
'### Force Identity Column to be maintained
'#
objXBulkLoad.KeepIdentity = False
'#
'### Force Transaction
'#
objXBulkLoad.Transaction = True
'#SWJS# Only allow this property to be set if the XML
'#SWJS# document does not contain a root element.
'#SWJS# However, before changing, make sure the xsd is updated accordingly.
'#SWJS#
'#SWJS# objXBulkLoad.XMLFragment = True
'#
'### Identify the Error Log File
'#
objXBulkLoad.ErrorLogFile = DTSGlobalVariables("gs_XMLBulkLoad_Log_File_Name").Value
'#
'### Identify the Temporary File Path
'#
objXBulkLoad.TempFilePath = DTSGlobalVariables("gs_XMLBulkLoad_Temp_File_Path").Value
'#
'### Execute the Bulk Load for the XML Document
'#
On Error Resume Next
objXBulkLoad.Execute s_Parm_XSD_File_Name, s_Parm_File_Name '<== FAILING STATEMENT, BUT CAUGHT!
If Err.number <> 0 Then
s_Parm_Process_Message = s_Parm_Process_Message & Now() & " - " & "Fatal Error Encountered in Bulk_Load_XML_File processing File : " & s_Parm_File_Name & " - Error Details : " & Err.number & " - " & Err.Description & vbCrLf
b_Error_Encountered = True
End If
On Error Goto 0
'#
'### Return Boolean Status
'#
Bulk_Load_XML_File = Not b_Error_Encountered
End Function
The error I get is "CREATE DEFAULT must be the first statement in a query batch" and it is repeated several times.
It is an absolute bugger because I have found 2 very conflicting Microsoft references :-
1. This says it should work :-
http://msdn2.microsoft.com/en-us/library/aa225730(SQL.80).aspx
2. This catagorically says it won't :-
http://support.microsoft.com/kb/330582
I have found numerous examples indicating it will work with the automatic creation of IDENTITY Values. I have found lots of examples where you supply the ID's in the XML.
Unfortunately I must use automatic creation of IDENTITY Values as I am being sent the data from another company.
I am thinking that there is something wrong with the XSD. However, when I amend to make it a very simple 2 level structure, it still does not work.
Strangely, if I code inverse="true" on the sql:relationship it will create the entry in the child table, however it does not populate the Identity column value from the Parent.
I'm at my wits end with this one, coz the alternative is I have to hand craft, which I can do, but it is not as elegant and requires considerably more time and effort!
Many thanks Chris.
Kind Regards...
Simon.
ChrisRickard on Feb 19, 2008 at 11:35:20 AM (# 8)This is certainly something I'd have to do "hands on". If you can prepare en entire micro-database complete with tables, data, and DTS package that shows this behavior I'd have a lot more to work with. The smaller the better. You can send me the backup and I can get it up and running locally.
swjs on Feb 20, 2008 at 10:00:46 AM (# 9)Hi Chris,
I have now completely confused myself.
I have created a complete test harness as suggested.
tbl_Temp_Parent
tbl_Temp_Child
tbl_Temp_Grand_Child
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Temp_Child]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Temp_Child]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Temp_Grand_Child]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Temp_Grand_Child]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Temp_Parent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Temp_Parent]
GO
CREATE TABLE [dbo].[tbl_Temp_Child] (
[Parent_Id] [int] NOT NULL ,
[Child_Id] [int] IDENTITY (1000, 1) NOT NULL ,
[Child_Data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Child_Group_Data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Temp_Grand_Child] (
[Child_Id] [int] NOT NULL ,
[Grand_Child_Id] [int] IDENTITY (1000, 1) NOT NULL ,
[Grand_Child_Data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Temp_Parent] (
[Parent_Id] [int] IDENTITY (1000, 1) NOT NULL ,
[Parent_Data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Parent_Group_Data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
I have created the XSD that will allow data to be propogated with the Identity columns through all three tables :-
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Children"
parent="tbl_Temp_Parent"
child="tbl_Temp_Child"
parent-key="Parent_Id"
child-key="Parent_Id" />
<sql:relationship name="Grand_Children"
parent="tbl_Temp_Child"
child="tbl_Temp_Grand_Child"
parent-key="Child_Id"
child-key="Child_Id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Parents" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Parent" minOccurs="1" maxOccurs="unbounded" sql:relation="tbl_Temp_Parent">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Parent_Data" type="xsd:token" sql:field="Parent_Data" />
<xsd:element name="Parent_Group" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Parent_Group_Data" type="xsd:token" sql:field="Parent_Group_Data" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Childs" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Child" minOccurs="1" maxOccurs="unbounded" sql:relation="tbl_Temp_Child" sql:relationship="Children">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Child_Group" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Child_Group_Data" type="xsd:token" sql:field="Child_Group_Data" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Child_Data" type="xsd:token" sql:field="Child_Data"/>
<xsd:element name="Grand_Childs" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Grand_Child" minOccurs="1" maxOccurs="unbounded" sql:relation="tbl_Temp_Grand_Child" sql:relationship="Grand_Children">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Grand_Child_Data" type="xsd:token" sql:field="Grand_Child_Data"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
And here is some data that I attempted to load :-
<?xml version="1.0" encoding="ISO-8859-1" ?>
<Parents xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="skytobusparts.xsd">
<Parent>
<Parent_Data><![CDATA[HELLO PARENT 1]]></Parent_Data>
<Parent_Group>
<Parent_Group_Data><![CDATA[HELLO PARENT GROUP DATA 1]]></Parent_Group_Data>
</Parent_Group>
<Childs>
<Child>
<Child_Data><![CDATA[HELLO CHILD 1.1]]></Child_Data>
<Child_Group>
<Child_Group_Data><![CDATA[HELLO CHILD GROUP DATA 1.1]]></Child_Group_Data>
</Child_Group>
<Grand_Childs>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 1.1.1]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 1.1.2]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 1.1.3]]></Grand_Child_Data>
</Grand_Child>
</Grand_Childs>
</Child>
<Child>
<Child_Data><![CDATA[HELLO CHILD 1.2]]></Child_Data>
<Grand_Childs>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 1.2.1]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 1.2.2]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 1.2.3]]></Grand_Child_Data>
</Grand_Child>
</Grand_Childs>
</Child>
<Child>
<Child_Data><![CDATA[HELLO CHILD 1.3]]></Child_Data>
<Grand_Childs>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 1.3.1]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 1.3.2]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 1.3.3]]></Grand_Child_Data>
</Grand_Child>
</Grand_Childs>
</Child>
</Childs>
</Parent>
<Parent>
<Parent_Data><![CDATA[HELLO PARENT 2]]></Parent_Data>
<Childs>
<Child>
<Child_Data><![CDATA[HELLO CHILD 2.1]]></Child_Data>
<Grand_Childs>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 2.1.1]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 2.1.2]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 2.1.3]]></Grand_Child_Data>
</Grand_Child>
</Grand_Childs>
</Child>
<Child>
<Child_Data><![CDATA[HELLO CHILD 2.2]]></Child_Data>
<Grand_Childs>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 2.2.1]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 2.2.2]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 2.2.3]]></Grand_Child_Data>
</Grand_Child>
</Grand_Childs>
</Child>
<Child>
<Child_Data><![CDATA[HELLO CHILD 2.3]]></Child_Data>
<Grand_Childs>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 2.3.1]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 2.3.2]]></Grand_Child_Data>
</Grand_Child>
<Grand_Child>
<Grand_Child_Data><![CDATA[HELLO GRAND CHILD 2.3.3]]></Grand_Child_Data>
</Grand_Child>
</Grand_Childs>
</Child>
</Childs>
</Parent>
</Parents>
Guess what?
It all loads successfully and correctly propogates the IDENTITY Columns through to each of the subordinate tables.
I have tried with/without CDATA and it works each time.
As soon as I try to run with my real data, with the structure exactly the same - Death ensues!
CREATE DEFAULT Error again.....
The only difference I can think of is the fact that I have an INSERT Trigger on my main parent table tbl_Job_Raw_XML.
I will re-run the tests on my Parent->Child->Grand Child tables but with a TRIGGER on tbl_Temp_Parent to see if that makes any difference.
Sorry for blatting this all down, just thought I would show that I am trying!
I just hope it is the TRIGGER!
Kind Regards........
Simon.
ChrisRickard on Feb 20, 2008 at 10:36:54 AM (# 10)One thing I suggest is running profiler when this happens. Look for the offending SQL call. It may give you some clues.
swjs on Feb 20, 2008 at 11:25:27 AM (# 11)Hi Chris,
Peace and harmony has been restored!
The problem was caused by having columns in the table which take their default from a previously defined Default.
If the values for these columns are not supplied in the XML, then somehow SQLXMLBulkLoad throws a wobbly.
If you simply hard-code Default values eg 0 or CURRENT_TIMESTAMP in the Table Designer, and not reference a predefined Default eg :-
create default [dflt_Now] as CURRENT_TIMESTAMP
or
create default [dflt_Zero] as 0
Then the SQLXMLBulkLoad works a treat.
All the Identity keys are correctly propogated.
So the answer was in the error "CREATE DEFAULT"! Which in this case meant, do not use predefined Defaults on a table definition, if you expect Identity Id's to be propogated to child tables. If you are not populating Child tables it all just works!
Yipppppeeee!
Many thanks for your patience and guidance. Much appreciated.
Kind regards............
Simon..................
ChrisRickard on Feb 20, 2008 at 5:07:52 PM (# 12)I'm glad to know you're all fixed up. While I didn't really do anything for you directly, I guess I helped you help yourself, so you're welcome.
| 
Propagate Identity Column Multiple Tables Using SQLXMLBulkLoad