Showing posts with label loading. Show all posts
Showing posts with label loading. Show all posts

Wednesday, March 28, 2012

Problems with sql:overflow-field and sql:datatype="xml" in XML bulk load

I am bulk loading data from an XML file into three tables in SS2005. All is well except for a section that I would like to load into a column of type XML in the table.

I have tried using sql:datatype="xml" and it does load into the XML column but there are two problems:

1) It adds two namespace tags to each element which I don't want.

2) It does not include the outermost tag <Form> but starts with the next tag in.

I then tried using sql:overflow-field and have a problem with it, also.

1) It includes the parent tag <Study> which I do not want. If I try to put the overflow annotation on the <Form> tag I get an error that it sees that as a column.

Here is the XSD:

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="StudyLoc"
parent="Study"
parent-key="StudyID"
child="StudyLocation"
child-key="StudyID" />
<sql:relationship name="StudyDoc"
parent="Study"
parent-key="StudyID"
child="StudyDocument"
child-key="StudyID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Study" sql:relation="Study">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Form" sql:datatype="xml" sql:field="XMLForm" />
<xsd:element minOccurs="0" name="LocationIDs" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="int" type="xsd:integer" sql:relation="StudyLocation" sql:relationship="StudyLoc" sql:field="LocationID" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element minOccurs="0" name="Documents" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="StudyDocument" sql:relation="StudyDocument" sql:relationship="StudyDoc">
<xsd:complexType>
<xsd:attribute name="DocumentID" type="xsd:integer" sql:field="StudyDocumentID" />
<xsd:attribute name="DocumentName" type="xsd:string" />
<xsd:attribute name="DocumentLocation" type="xsd:string" />
<xsd:attribute name="DocumentStatusCode" type="xsd:string" sql:field="StudyDocumentStatusCode" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ID" type="xsd:integer" sql:field="StudyID" />
<xsd:attribute name="Name" type="xsd:string" sql:field="StudyTitle" />
<xsd:attribute name="AssessmentCount" type="xsd:integer" />
<xsd:attribute name="StartDate" type="xsd:date" sql:datatype="dateTime" sql:field="StudyStartDate" />
<xsd:attribute name="EndDate" type="xsd:date" sql:datatype="dateTime" sql:field="StudyEndDate" />
<xsd:attribute name="TypeID" type="xsd:string" sql:field="StudyTypeID" />
<xsd:attribute name="ProjectID" type="xsd:integer" />
<xsd:attribute name="StudyStatus" type="xsd:string" sql:field="StudyStatusCode" />
<xsd:attribute name="AssessmentsCompleted" type="xsd:integer" />
<xsd:attribute name="SampleTarget" type="xsd:integer" />
<xsd:attribute name="SamplesCollected" type="xsd:integer" />
<xsd:attribute name="LastModifiedDate" type="xsd:date" sql:datatype="dateTime" />
<xsd:attribute name="AutoApprove" type="xsd:boolean" sql:field="AutoApproveFlag" default="false" />
</xsd:complexType>
</xsd:element>
</xsd:schema>

Here is the XML (the part in red is what I want loaded into the XML column):

<?xml version="1.0" encoding="utf-8"?>
<Study xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ID="124" Name="Test2" AssessmentCount="24" StartDate="2006-12-31T00:00:00" EndDate="2007-02-07T00:00:00" TypeID="1" ProjectID="2" StudyStatus="A" AssessmentsCompleted="0" SampleTarget="0" SamplesCollected="0" LastModifiedDate="0001-01-01T00:00:00" >
<Form>
<SiteInformation>
<Groups />
</SiteInformation>
<DataCollection>
<Groups>
<FormGroup Label="Data" ID="12" Required="false" Observation="true" ItemDetail="false" OrdinalPosition="1">
<Criteria>
<FormCriterion ID="4" Name="Default Text Field" Label="My Field" InputType="textbox" DependentOn="0" LastModifiedDate="2007-01-29T14:26:45" LastModifiedUser="inmar/cchacon" DefaultOrdinalPosition="3">
<Properties>
<FormCriterionProperty ID="28" Name="MinLength" Value="" />
<FormCriterionProperty ID="29" Name="MaxLength" Value="" />
<FormCriterionProperty ID="30" Name="DefaultValue" Value="" />
<FormCriterionProperty ID="31" Name="RegularExpression" Value="" />
</Properties>
<ListItems />
</FormCriterion>
<FormCriterion ID="5" Name="Default Check Box Group" Label="My Check" InputType="checkbox" DependentOn="0" LastModifiedDate="2006-11-27T08:32:00" LastModifiedUser="cchacon" DefaultOrdinalPosition="1">
<Properties />
<ListItems />
</FormCriterion>
<FormCriterion ID="41" Name="Sample Plan Criteria" Label="No More" InputType="planitem" DependentOn="0" LastModifiedDate="2006-12-08T16:48:12" LastModifiedUser="cchacon" DefaultOrdinalPosition="9">
<Properties />
<ListItems />
</FormCriterion>
</Criteria>
</FormGroup>
</Groups>
</DataCollection>
</Form>
<LocationIDs />
<Documents />
</Study>

And this is the table it's loading into:

CREATE TABLE Study
(
StudyID int IDENTITY (1,1) ,
StudyTitle varchar(30) NULL ,
AssessmentCount int NULL ,
StudyStartDate datetime NULL ,
StudyEndDate datetime NULL ,
StudyTypeID varchar(10) NULL ,
ProjectID int NULL ,
StudyStatusCode varchar(10) NULL ,
AssessmentsCompleted int NULL ,
SampleTarget int NULL ,
SamplesCollected int NULL ,
LastModifiedDate datetime NULL ,
AutoApproveFlag bit NOT NULL ,
XMLForm xml NULL ,
)
go

Any help would be appreciated! All the other XML files I've bulk loaded have worked great but loading a fragment into an XML column has not worked like I want it to.

Thanks!


You might try setting

minOccurs="0"

on the elements you do not want

|||

Thanks for the response. When I use sql:overflow-field on the Study tag to tell it to put all the unconsumed data, i.e., the <Form> tag and all it contains, into the XMLForm field, I get the <Study> tag with it since the overflow field picks up the parent tag.

If I try putting minOccurs=0 on the Study tag I get the error:

Attribute 'minOccurs' is not allowed on global 'element' declarations

If I try putting the overflow tag on the next level below Form, assuming I'll get Form then as the parent tag, I get an error that the Form tag needs a relation and relationship. And if I mark it as a constant, then it ignores it and I get Study as the parent tag again.

So if I use overflow I get a parent tag <Study> which I don't want and if I use xml I miss the <Form> tag which I do want.

|||ok, I am offically stumped. Perhaps if you checked with a pure XML forum they could help.

Monday, March 26, 2012

Problems with Snapshot Replication

Hello,

We are using Snapshot replication to move data for our Data Warehouse from a server which performs the loading to the production reporting server. We are currently using Sql Server 2005 in both environments. The loading server is setup as the Distributor and Publisher and the production reporting server is setup as the subscriber. However, the replication is not automated instead we only want it to run if the load process is successful. To do this we did not set a schedule for replication but added the jobs that the Replication Wizard created to our own Sql Agent job which manages the load. I have listed the below commands that the sql agent is running for replication in the order in which they are ran.

1) -Publisher [SRVLOADER] -PublisherDB [PRESENTATION] -Distributor [SRVLOADER] -Publication [SRVREPORTER] -DistributorSecurityMode 1

2)-Subscriber [SRVREPORTER] -SubscriberDB [PRESENTATION] -Publisher [SRVLOADER] -Distributor [SRVLOADER] -DistributorSecurityMode 1 -Publication [SRVREPORTER] -PublisherDB [PRESENTATION]

In our own job we run each of these jobs using the EXEC SP_START_JOB() procedure. Each job listed above runs under their own PROXY account.

Finally, we are experiencing an intermittent problem where the Agent will report it is unable to Copy data into "X" table, with "X" being any one of the tables in the reporting database. This has only ever occurred during the automation process and not from manually running each agent job. This problem also might not occur for a couple of days and then might occur 2 or 3 days in a row, which has led us to question the dependability of this approach.

In closing I would like to ask if there is a problem with the way we have set it up? Something we could do to improve the process so it becomes more reliable? Any advice would be greatly appreciated.The fact that the issue is intermittent would seem to rule out a security problem. I have to wonder if maybe it is a time-out issue with the connection, which would vary with the size of the tables and the amount of activity and make the problem difficult to replicate.|||Hi Blindman,

Thank you for responding. One of the primary concerns I have is that the only way you can tell the process is failing is by opening the replication monitor. The SP_START_JOB() procedure never reports a failure even though the process is failing.

The only other thing I thought could be happening, although I haven't been able to witness it, is that the first process:
1) -Publisher [SRVLOADER] -PublisherDB [PRESENTATION] -Distributor [SRVLOADER] -Publication [SRVREPORTER] -DistributorSecurityMode 1

is rebuilding the files used in the bulk load process.

Is it possible that this process might not be completing before the 2nd process starts? The second job is actually pushing the data.

I am kinda at my wits end on this one. Any further ideas or alternatives would be greatly appreciated.|||I wish I had more experience with replication to help you out, but I have only implemented it a few times.
I'd suggest you try the standard trouble shooting techniques. Try breaking the process down into smaller pieces (such as replicating half the tables) and see if you can't isolate the issue.
Also, replication is fairly complex both internally and externally. So anything you do out of the ordinary is suspect. This comment from your post would raise warning flags with me:
However, the replication is not automated instead we only want it to run if the load process is successful. To do this we did not set a schedule for replication but added the jobs that the Replication Wizard created to our own Sql Agent job which manages the load.
What happens if you kick off the job manually rather than having it triggered by the load process?|||If I manually go in and start the two jobs it has always worked. When manually starting the jobs I run the same 2 process in the same order. The jobs should not be running as me since they are set to run under a proxy account. So I believe that shouldn't be the issue.

To me I THINK the issue lies with the way that the Sql Jobs are invoked because they never report a failure even though one is occurring. I can only assume at this point that when I invoke sp_start_job that it may be running asynchronously. Once again just an assumption. Unfortunately BOL doesn't state whether it runs asynchronously or not, it just states it returns a 1 or a 0.|||Are you logging the output of the job? Unfortunately, this is really a pain in the ass to do with 2005. It was easy with 2000, but it is one of several things Microsquish boloxed up with the latest version.