Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts

Friday, February 24, 2012

annotated schema and bulk loading into multiple tables...

Hi guys,
I'm trying to get XML data loaded into a set of tables using bulk load. The
child tables also have an XML column where I want to store portions of the
XML.
So far the I've got it correctly inserting data into the parent, and able to
insert the correct amount of rows in the child tables, but the data in the
child tables is empty... The child data is an identity column, a foreign key
pointing back to the parent row (empty!), and an XML data column holding the
contents of the xml fragment (also empty!).
Any help would be greatly appreciated!
Thanks.
Daniel.
Below is where I've got to so far...
SQL definitions for my parent table and a child table:
----
CREATE TABLE [dbo].[Foo](
[Foo_PK] [int] IDENTITY(1,1) NOT NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
(
[Foo_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ChildOfFoo](
[ChildOfFoo_PK] [int] IDENTITY(1,1) NOT NULL,
[Foo_FK] [int] NULL,
[Data] [xml] NULL,
CONSTRAINT [PK_ChildOfFoo] PRIMARY KEY CLUSTERED
(
[ChildOfFoo_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here's the annotated XSD:
----
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<!-- RELATIONSHIP FOR CONNECTING THE CHILD KEY TO THE PARENT
INDEX -->
<sql:relationship name="HeaderWash"
parent="Foo"
parent-key="Foo_PK"
child="ChildOfFoo"
child-key="Foo_FK" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<!-- HEADER XML TO FOO TABLE -->
<xs:element name="Header"
sql:relation="Foo"
sql:key-fields="Foo_PK">
<xs:complexType>
<xs:sequence>
<xs:element name="Foo_PK" type="xs:integer"
minOccurs="0"
default="0" sql:identity="ignore"/>
<xs:element name="StartDateTime"
type="xs:dateTime" />
<xs:element name="EndDateTime"
type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>
<!-- CHILD XML TO CHILD OF FOO TABLE -->
<xs:element name="Children" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Wash"
sql:relation="ChildOfFoo"
sql:key-fields="ChildOfFoo_PK"
sql:relationship="HeaderWash">
<xs:complexType>
<xs:sequence>
<xs:element name="ChildOfFoo_PK"
type="xs:string"
minOccurs="0"
default="0" sql:identity="ignore" />
<xs:element name="Foo_FK"
type="xs:string" />
<xs:element name="Wash"
sql:field="Data" sql:datatype="xml" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
And finally, (thanks for your patience!), a sample of XML data...
----
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Root>
<Header>
<StartDateTime>15 Jun 2007 08:07:00</StartDateTime>
<EndDateTime>15 Jun 2007 09:12:00</EndDateTime>
</Header>
<Children>
<!-- EACH CHILDOFFOO CREATE'S A ROW IN CHILDOFFOO TABLE
AND ALSO IS INSERTED INTO DATA XML COLUMN.
CAN CONTAIN <ChildOFFoo>, BUT BETTER TO JUST HAVE
INNER TEXT XML FRAGMENT...
-->
<ChildOfFoo>
<Phasetime>02:00</Phasetime>
<Heated>No</Heated>
</ChildOfFoo>
<ChildOfFoo>
<Quantityinjected>22.8 ml</Quantityinjected>
<Lowerlimit>61.1 ml</Lowerlimit>
<Upperlimit>82.7 ml</Upperlimit>
</ChildOfFoo>
<ChildOfFoo>
<Watertype>Cold water</Watertype>
<Numberofstages>1</Numberofstages>
</ChildOfFoo>
<ChildOfFoo>
<Quantityinjected>137.6 ml</Quantityinjected>
<Lowerlimit>122.3 ml</Lowerlimit>
<Upperlimit>165.4 ml</Upperlimit>
</ChildOfFoo>
<ChildOfFoo>
<Circulationpressure>Detected</Circulationpressure>
</ChildOfFoo>
<ChildOfFoo>
<Heated>No</Heated>
<Circulationpressure>Detected</Circulationpressure>
</ChildOfFoo>
</Children>
</Root>In case anyone elase wants this solution... The schema is modified as such:
1. When referencing a foreign key (as in the relationship) it needs to stay
in scope from when it is initially populated by the parent table, to each
time it is used. If you get a foreign key of null, you are most probably
using the FK out of scope.
2. Use the sql:overflow-field="<MyXmlColumn>" in the element that relates to
the table you want to store as XML. The sql:overflow-field field takes all
unreferenced XML in that node and stuffs it into the field you specify.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<!-- RELATIONSHIP FOR CONNECTING THE CHILD KEY TO THE PARENT
INDEX -->
<sql:relationship name="HeaderWash"
parent="Foo"
parent-key="Foo_PK"
child="ChildOfFoo"
child-key="Foo_FK" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<!-- HEADER XML ELEMENTS TO FOO TABLE -->
<xs:element name="Foo_PK" type="xs:integer" minOccurs="0"
default="0" sql:identity="ignore"/>
<xs:element name="StartDateTime" type="xs:dateTime" />
<xs:element name="EndDateTime" type="xs:dateTime" />
<!-- CHILD XML TO CHILD OF FOO TABLE -->
<xs:element name="Children" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Wash"
sql:relation="ChildOfFoo"
sql:key-fields="ChildOfFoo_PK"
sql:relationship="HeaderWash"
sql:overflow-field="Data">
<xs:complexType>
<xs:sequence>
<xs:element name="ChildOfFoo_PK"
type="xs:string"
minOccurs="0" default="0"
sql:identity="ignore" />
<xs:element name="Foo_FK"
type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:annotation>
> <xs:appinfo>
> <!-- RELATIONSHIP FOR CONNECTING THE CHILD KEY TO THE PARENT
> INDEX -->
> <sql:relationship name="HeaderWash"
> parent="Foo"
> parent-key="Foo_PK"
> child="ChildOfFoo"
> child-key="Foo_FK" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Root" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <!-- HEADER XML TO FOO TABLE -->
> <xs:element name="Header"
> sql:relation="Foo"
> sql:key-fields="Foo_PK">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Foo_PK" type="xs:integer"
> minOccurs="0"
> default="0" sql:identity="ignore"/>
> <xs:element name="StartDateTime"
> type="xs:dateTime" />
> <xs:element name="EndDateTime"
> type="xs:dateTime" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <!-- CHILD XML TO CHILD OF FOO TABLE -->
> <xs:element name="Children" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element maxOccurs="unbounded" name="Wash"
> sql:relation="ChildOfFoo"
> sql:key-fields="ChildOfFoo_PK"
> sql:relationship="HeaderWash">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ChildOfFoo_PK"
> type="xs:string"
> minOccurs="0"
> default="0" sql:identity="ignore" />
> <xs:element name="Foo_FK"
> type="xs:string" />
> <xs:element name="Wash"
> sql:field="Data" sql:datatype="xml" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:%23gNnsRHxHHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> I'm trying to get XML data loaded into a set of tables using bulk load.
> The child tables also have an XML column where I want to store portions of
> the XML.
> So far the I've got it correctly inserting data into the parent, and able
> to insert the correct amount of rows in the child tables, but the data in
> the child tables is empty... The child data is an identity column, a
> foreign key pointing back to the parent row (empty!), and an XML data
> column holding the contents of the xml fragment (also empty!).
> Any help would be greatly appreciated!
> Thanks.
> Daniel.
>
>
> Below is where I've got to so far...
> SQL definitions for my parent table and a child table:
> ----
> CREATE TABLE [dbo].[Foo](
> [Foo_PK] [int] IDENTITY(1,1) NOT NULL,
> [StartDateTime] [datetime] NULL,
> [EndDateTime] [datetime] NULL,
> CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
> (
> [Foo_PK] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[ChildOfFoo](
> [ChildOfFoo_PK] [int] IDENTITY(1,1) NOT NULL,
> [Foo_FK] [int] NULL,
> [Data] [xml] NULL,
> CONSTRAINT [PK_ChildOfFoo] PRIMARY KEY CLUSTERED
> (
> [ChildOfFoo_PK] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> Here's the annotated XSD:
> ----
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:annotation>
> <xs:appinfo>
> <!-- RELATIONSHIP FOR CONNECTING THE CHILD KEY TO THE PARENT
> INDEX -->
> <sql:relationship name="HeaderWash"
> parent="Foo"
> parent-key="Foo_PK"
> child="ChildOfFoo"
> child-key="Foo_FK" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Root" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <!-- HEADER XML TO FOO TABLE -->
> <xs:element name="Header"
> sql:relation="Foo"
> sql:key-fields="Foo_PK">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Foo_PK" type="xs:integer"
> minOccurs="0"
> default="0" sql:identity="ignore"/>
> <xs:element name="StartDateTime"
> type="xs:dateTime" />
> <xs:element name="EndDateTime"
> type="xs:dateTime" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <!-- CHILD XML TO CHILD OF FOO TABLE -->
> <xs:element name="Children" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element maxOccurs="unbounded" name="Wash"
> sql:relation="ChildOfFoo"
> sql:key-fields="ChildOfFoo_PK"
> sql:relationship="HeaderWash">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ChildOfFoo_PK"
> type="xs:string"
> minOccurs="0"
> default="0" sql:identity="ignore" />
> <xs:element name="Foo_FK"
> type="xs:string" />
> <xs:element name="Wash"
> sql:field="Data" sql:datatype="xml" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
>
> And finally, (thanks for your patience!), a sample of XML data...
> ----
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <Root>
> <Header>
> <StartDateTime>15 Jun 2007 08:07:00</StartDateTime>
> <EndDateTime>15 Jun 2007 09:12:00</EndDateTime>
> </Header>
> <Children>
> <!-- EACH CHILDOFFOO CREATE'S A ROW IN CHILDOFFOO TABLE
> AND ALSO IS INSERTED INTO DATA XML COLUMN.
> CAN CONTAIN <ChildOFFoo>, BUT BETTER TO JUST HAVE
> INNER TEXT XML FRAGMENT...
> -->
> <ChildOfFoo>
> <Phasetime>02:00</Phasetime>
> <Heated>No</Heated>
> </ChildOfFoo>
> <ChildOfFoo>
> <Quantityinjected>22.8 ml</Quantityinjected>
> <Lowerlimit>61.1 ml</Lowerlimit>
> <Upperlimit>82.7 ml</Upperlimit>
> </ChildOfFoo>
> <ChildOfFoo>
> <Watertype>Cold water</Watertype>
> <Numberofstages>1</Numberofstages>
> </ChildOfFoo>
> <ChildOfFoo>
> <Quantityinjected>137.6 ml</Quantityinjected>
> <Lowerlimit>122.3 ml</Lowerlimit>
> <Upperlimit>165.4 ml</Upperlimit>
> </ChildOfFoo>
> <ChildOfFoo>
> <Circulationpressure>Detected</Circulationpressure>
> </ChildOfFoo>
> <ChildOfFoo>
> <Heated>No</Heated>
> <Circulationpressure>Detected</Circulationpressure>
> </ChildOfFoo>
> </Children>
> </Root>
>
Posted by Ethel at 12:33 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

Monday, February 13, 2012

Analysis Services Processing Freezes or Hangs

Currently I have multiple Analysis Services databases on multiple servers. On occassion, when processing these databases it will freeze or hang. Sometime the problem is the database structure, sometimes it is the back end source tables being locked, etc. The only way I can terminate the processing is to kill the dev environment or management studio. This causes problems because the next time the database is opened, it will not process or save. The only solution I found is restarting Analysis Services. Is there some way to force stop processing without damaging the database and leaving connections open?

It might be possible to open a second instance of SSMS and use XMLA to discover the sessions or connections and then cancel the one that it stuck. But sometimes the server can take a while to respond to these commands if it is "stuck".

You can also use the xmlaDiscover class from the Analysis Services Stored Procedure project http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home which lets you manage this a bit simpler by running a command like:

CALL ASSP.DiscoverConnections()

which displays the current connections in a grid, and then:

CALL ASSP.CancelConnection(<ConnectionID>)

You should probably be aware that there is an issue with the Cancel command in SP2 that I read about in a KB article and sometimes it does not function correctly. Apparently there is a hotfix available if this issue affects you.

Posted by Ethel at 11:17 PM 0 comments
Labels: , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)