Showing posts with label load. Show all posts
Showing posts with label load. Show all posts

Wednesday, March 28, 2012

Problems with SQLXML bulk load

Hi all,

I am newbie in all the stuff about xml importing into sql server.

What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)

When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.

My schema is:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>
.
.
.
</ENGROLE>

and my table definition where I try to insert:

CREATE TABLE [dbo].[table1](
[ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
) ON [PRIMARY]

As you see it is very simple, but I cannot get it work. Can anyone
give a hand?

Thanks!!If your data is involved in a DataSet, try to use
SQLBulkCopy class.|||It is not. I receive the data from a process that generates the data into XML format, and I cannot change the process.|||Try also to post the question on the .Net forum, you may get the right answer quicker.|||Ok, thanks!

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.

Friday, March 9, 2012

Problems with executing ssis package by job

Hi All,
I have created ssis package and am trying to execute it using sql
server agent job.
The problem is, that the job failes to find or load the package. It
tries to run it from the server side and failes to find it. I don't
really understand how can I publish the package on the server from the
client side.
Thanks in advance.Qwert wrote:
> Hi All,
> I have created ssis package and am trying to execute it using sql
> server agent job.
> The problem is, that the job failes to find or load the package. It
> tries to run it from the server side and failes to find it. I don't
> really understand how can I publish the package on the server from the
> client side.
> Thanks in advance.
Hi
Have you saved your package in SQL Server ?
from file menu in Microsoft Visual Studio select Save copy of package
as and save your package in SQL Server.
or
Connect to intergation service in Management studio and import package.
Regards
Amish Shah
http://shahamishm.tripod.com
Regards
Amish Shah|||Qwert wrote:
> Hi All,
> I have created ssis package and am trying to execute it using sql
> server agent job.
> The problem is, that the job failes to find or load the package. It
> tries to run it from the server side and failes to find it. I don't
> really understand how can I publish the package on the server from the
> client side.
> Thanks in advance.
Hi
Have you saved your package in SQL Server ?
from file menu in Microsoft Visual Studio select Save copy of package
as and save your package in SQL Server.
or
Connect to intergation service in Management studio and import package.
Regards
Amish Shah
http://shahamishm.tripod.com
Regards
Amish Shah|||amish wrote:
> Hi
> Have you saved your package in SQL Server ?
> from file menu in Microsoft Visual Studio select Save copy of package
> as and save your package in SQL Server.
> or
> Connect to intergation service in Management studio and import package.
>
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Regards
> Amish Shah
Hi, thanks for replying,
Yes I tried both things, what I get is
Executed as user: CLUSTERNET\admin. The package could not be loaded.
The step failed.
Yours Qwert

Problems with disconnected recordsets i Yukon

We have a VB6 application using ADO 2.8 connecting to SQL Server.

We use disconnected recordset to load data to client-side. The client does updates in the recordset and then sends it back to server-side component that converts the recordset into SQL to update the database. No problems in SQL 2000 but on Yukon we get error when trying to update the disconnected recordset on the client.

Error: -2147217887 Multiple-step operation generated errors. Check each status value.

Error only occurs on recordsets which obviously won't work it was a connected recordset; for example a UNION sql.

Is this the way Yukon is supposed to work? Is there anyway to disable this new functionality to really get a disconnected recordset?

One thing that would work is to create a recordset from scratch based on the loaded recordset and copy all data into this new recordset. But it seems like a lot of work doing this each time.

I suspect that Yukon is more accurately reporting that certain columns are not updatable and ADO is marking the disconnected recordset fields as read only. I'll see if there is a way to disable this. One thing you could do to see if this is the case is save the ADO recordset to XML and compare XML from SQL 2000 and Yukon to see what is different.|||

I have a similar problem, did you find any solution to this? I've tried all ADO recordset properties, but I can't find anything.

I saved ADO recordset to XML from SQL 2000 and SQL 2005. There was one difference that is propably the cause. XML from SQL 2005 was missing rs:writeunknown property from attributes. I just don't want to create a recordset first and copy data from original recordset or save the recordset to xml and modify it.

|||Unfortunately I never found any better solution than the one I described.

Problems with disconnected recordsets i Yukon

We have a VB6 application using ADO 2.8 connecting to SQL Server.

We use disconnected recordset to load data to client-side. The client does updates in the recordset and then sends it back to server-side component that converts the recordset into SQL to update the database. No problems in SQL 2000 but on Yukon we get error when trying to update the disconnected recordset on the client.

Error: -2147217887 Multiple-step operation generated errors. Check each status value.

Error only occurs on recordsets which obviously won't work it was a connected recordset; for example a UNION sql.

Is this the way Yukon is supposed to work? Is there anyway to disable this new functionality to really get a disconnected recordset?

One thing that would work is to create a recordset from scratch based on the loaded recordset and copy all data into this new recordset. But it seems like a lot of work doing this each time.

I suspect that Yukon is more accurately reporting that certain columns are not updatable and ADO is marking the disconnected recordset fields as read only. I'll see if there is a way to disable this. One thing you could do to see if this is the case is save the ADO recordset to XML and compare XML from SQL 2000 and Yukon to see what is different.|||

I have a similar problem, did you find any solution to this? I've tried all ADO recordset properties, but I can't find anything.

I saved ADO recordset to XML from SQL 2000 and SQL 2005. There was one difference that is propably the cause. XML from SQL 2005 was missing rs:writeunknown property from attributes. I just don't want to create a recordset first and copy data from original recordset or save the recordset to xml and modify it.

|||Unfortunately I never found any better solution than the one I described.

Problems with disconnected recordsets i Yukon

We have a VB6 application using ADO 2.8 connecting to SQL Server.

We use disconnected recordset to load data to client-side. The client does updates in the recordset and then sends it back to server-side component that converts the recordset into SQL to update the database. No problems in SQL 2000 but on Yukon we get error when trying to update the disconnected recordset on the client.

Error: -2147217887 Multiple-step operation generated errors. Check each status value.

Error only occurs on recordsets which obviously won't work it was a connected recordset; for example a UNION sql.

Is this the way Yukon is supposed to work? Is there anyway to disable this new functionality to really get a disconnected recordset?

One thing that would work is to create a recordset from scratch based on the loaded recordset and copy all data into this new recordset. But it seems like a lot of work doing this each time.

I suspect that Yukon is more accurately reporting that certain columns are not updatable and ADO is marking the disconnected recordset fields as read only. I'll see if there is a way to disable this. One thing you could do to see if this is the case is save the ADO recordset to XML and compare XML from SQL 2000 and Yukon to see what is different.|||

I have a similar problem, did you find any solution to this? I've tried all ADO recordset properties, but I can't find anything.

I saved ADO recordset to XML from SQL 2000 and SQL 2005. There was one difference that is propably the cause. XML from SQL 2005 was missing rs:writeunknown property from attributes. I just don't want to create a recordset first and copy data from original recordset or save the recordset to xml and modify it.

|||Unfortunately I never found any better solution than the one I described.