Showing posts with label bulk. Show all posts
Showing posts with label bulk. Show all posts

Wednesday, March 7, 2012

Another " Error: 4813 Expected the text length in data stream - "

After working for over a year, one of our snapshot replication
distributions now fails with the following error:
The process could not bulk copy into table '"texttab"'.
Expected the text length in data stream for bulk copy of text, ntext,
or image data.
(Source: DESTINATION_SERVER (Data source); Error number: 4813)
Function sequence error
(Source: ODBC Driver Manager (ODBC); Error number: S1010)
The last column of the table in error is type "text". If we lower the
number of the "BCPBatchSize" in the distribution agent profile, more
rows get copied before the error occurs - but there are too many other
very large tables in the snapshot to use that as a go-around.
As suggested in a previous post, we cannot change the table structure
because it is a third-party developed product.
When we use the -UseInProcLoader option in the dist agent, it creates
another error.
We're running SQL Server 2000 8.00.760
Thanks for any ideas.
remove the text column and replicate the remaining table. Use another method
to synchronize the text column, such as using a trigger to write to an audit
table. Have DTS read this audit table at regular intervals and then update
the text column between the publisher and subscriber. In many topologies you
will find that the text/image column is highly static.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dean" <dpichotto@.cse-insurance.com> wrote in message
news:1120071435.407646.302670@.g49g2000cwa.googlegr oups.com...
> After working for over a year, one of our snapshot replication
> distributions now fails with the following error:
> The process could not bulk copy into table '"texttab"'.
> Expected the text length in data stream for bulk copy of text, ntext,
> or image data.
> (Source: DESTINATION_SERVER (Data source); Error number: 4813)
> Function sequence error
> (Source: ODBC Driver Manager (ODBC); Error number: S1010)
> The last column of the table in error is type "text". If we lower the
> number of the "BCPBatchSize" in the distribution agent profile, more
> rows get copied before the error occurs - but there are too many other
> very large tables in the snapshot to use that as a go-around.
> As suggested in a previous post, we cannot change the table structure
> because it is a third-party developed product.
> When we use the -UseInProcLoader option in the dist agent, it creates
> another error.
> We're running SQL Server 2000 8.00.760
> Thanks for any ideas.
>
|||Thanks very much for your reply.
Wow - how twisted! I wonder if using ftp for the distribution would get
around this...
Posted by Ethel at 12:17 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

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: , , , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)