Showing posts with label file. Show all posts
Showing posts with label file. 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 Sql2k install on Windows 2K3 server std

After installing SQL on a Windows 2003 server I try to apply Sp3a but it dies
about 10% into the install Saying a file "cannot be open as write" however
it's a different file each time. Then after the SP fails it trashers the SQL
Install so that non of the services will run anymore.
Any Ideas
Thanks
Simon
> After installing SQL on a Windows 2003 server I try to apply Sp3a but it
dies
> about 10% into the install Saying a file "cannot be open as write"
however
> it's a different file each time. Then after the SP fails it trashers the
SQL
> Install so that non of the services will run anymore.
> Any Ideas
> Thanks
> Simon
You may not have sufficient privileges on the machine to complete the
install. You can use Regmon and Filemon to figure out where the install is
failing. These tools are available from http://www.sysinternals.com.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Problems with Sql2k install on Windows 2K3 server std

After installing SQL on a Windows 2003 server I try to apply Sp3a but it die
s
about 10% into the install Saying a file "cannot be open as write" however
it's a different file each time. Then after the SP fails it trashers the SQL
Install so that non of the services will run anymore.
Any Ideas
Thanks
Simon> After installing SQL on a Windows 2003 server I try to apply Sp3a but it
dies
> about 10% into the install Saying a file "cannot be open as write"
however
> it's a different file each time. Then after the SP fails it trashers the
SQL
> Install so that non of the services will run anymore.
> Any Ideas
> Thanks
> Simon
--
You may not have sufficient privileges on the machine to complete the
install. You can use Regmon and Filemon to figure out where the install is
failing. These tools are available from http://www.sysinternals.com.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Problems with Sql2k install on Windows 2K3 server std

After installing SQL on a Windows 2003 server I try to apply Sp3a but it dies
about 10% into the install Saying a file "cannot be open as write" however
it's a different file each time. Then after the SP fails it trashers the SQL
Install so that non of the services will run anymore.
Any Ideas
Thanks
Simon> After installing SQL on a Windows 2003 server I try to apply Sp3a but it
dies
> about 10% into the install Saying a file "cannot be open as write"
however
> it's a different file each time. Then after the SP fails it trashers the
SQL
> Install so that non of the services will run anymore.
> Any Ideas
> Thanks
> Simon
--
You may not have sufficient privileges on the machine to complete the
install. You can use Regmon and Filemon to figure out where the install is
failing. These tools are available from http://www.sysinternals.com.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.sql

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.

Problems with SQL Server agent

Hi,
I have som problems with a SQL Server agent job.
I have created a DTS package that extracts data from database and saves
the data to a csv file on the
network.
When I run the DTS package manually the file is created and all is OK.
But when I create a scheduled SQL Server agent job to run my DTS
package I get the following error message:
"Executed as user: SERVER\SYSTEM.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
(80004005)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
TSRun: Package execution complete. Process Exit Code 1. The step
failed."
I understand as much as the user running the job hasn't got access to
the file...
So.. how do I change the user that executes the job?
Thanks for your help.
Mikael
McA wrote:
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job?
> Thanks for your help.
> Mikael
>
Are the SQL Server and SQL Agent services configured to run as "Local
System" or as a domain user? When you run the DTS package manually, it
runs using the permissions that your personal login has. When it runs
from a job, it uses the permissions that SQL Server has. To access
network resources, SQL must be running as a domain user, and that domain
user must have the necessary permissions to access the resource.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Check the services application. Is the SQL Server Agent service running as
a domain user account or as the local system?
You need to make sure that the account that the SQL Server Agent is running
under has permissions to the file/directory.
Keith Kratochvil
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1160055899.543735.146020@.i42g2000cwa.googlegr oups.com...
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job?
> Thanks for your help.
> Mikael
>
|||Hello
It looks like the SQLSERVERAGENT runs with the Local System account.
So if I change the "Log On As" user to at user with rights to my file
I'll be OK?
I'll try that thanks..
Mikael
|||McA wrote:
> Hello
> It looks like the SQLSERVERAGENT runs with the Local System account.
> So if I change the "Log On As" user to at user with rights to my file
> I'll be OK?
> I'll try that thanks..
> Mikael
>
There's more to consider than whether the user has rights to your file,
but yes, that's the solution to your problem. You need to read about
Service Accounts in Books Online before changing anything, to make sure
you understand the requirements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problems with SQL Server agent

Hi,
I have som problems with a SQL Server agent job.
I have created a DTS package that extracts data from database and saves
the data to a csv file on the
network.
When I run the DTS package manually the file is created and all is OK.
But when I create a scheduled SQL Server agent job to run my DTS
package I get the following error message:
"Executed as user: SERVER\SYSTEM.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
(80004005)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
TSRun: Package execution complete. Process Exit Code 1. The step
failed."
I understand as much as the user running the job hasn't got access to
the file...
So.. how do I change the user that executes the job'
Thanks for your help.
MikaelMcA wrote:
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>
Are the SQL Server and SQL Agent services configured to run as "Local
System" or as a domain user? When you run the DTS package manually, it
runs using the permissions that your personal login has. When it runs
from a job, it uses the permissions that SQL Server has. To access
network resources, SQL must be running as a domain user, and that domain
user must have the necessary permissions to access the resource.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Check the services application. Is the SQL Server Agent service running as
a domain user account or as the local system?
You need to make sure that the account that the SQL Server Agent is running
under has permissions to the file/directory.
Keith Kratochvil
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1160055899.543735.146020@.i42g2000cwa.googlegroups.com...
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>|||Hello
It looks like the SQLSERVERAGENT runs with the Local System account.
So if I change the "Log On As" user to at user with rights to my file
I'll be OK?
I'll try that thanks..
Mikael|||McA wrote:
> Hello
> It looks like the SQLSERVERAGENT runs with the Local System account.
> So if I change the "Log On As" user to at user with rights to my file
> I'll be OK?
> I'll try that thanks..
> Mikael
>
There's more to consider than whether the user has rights to your file,
but yes, that's the solution to your problem. You need to read about
Service Accounts in Books Online before changing anything, to make sure
you understand the requirements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Problems with SQL Server agent

Hi,
I have som problems with a SQL Server agent job.
I have created a DTS package that extracts data from database and saves
the data to a csv file on the
network.
When I run the DTS package manually the file is created and all is OK.
But when I create a scheduled SQL Server agent job to run my DTS
package I get the following error message:
"Executed as user: SERVER\SYSTEM.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
(80004005)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset
Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
TSRun: Package execution complete. Process Exit Code 1. The step
failed."
I understand as much as the user running the job hasn't got access to
the file...
So.. how do I change the user that executes the job'
Thanks for your help.
MikaelMcA wrote:
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>
Are the SQL Server and SQL Agent services configured to run as "Local
System" or as a domain user? When you run the DTS package manually, it
runs using the permissions that your personal login has. When it runs
from a job, it uses the permissions that SQL Server has. To access
network resources, SQL must be running as a domain user, and that domain
user must have the necessary permissions to access the resource.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Check the services application. Is the SQL Server Agent service running as
a domain user account or as the local system?
You need to make sure that the account that the SQL Server Agent is running
under has permissions to the file/directory.
--
Keith Kratochvil
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1160055899.543735.146020@.i42g2000cwa.googlegroups.com...
> Hi,
> I have som problems with a SQL Server agent job.
> I have created a DTS package that extracts data from database and saves
> the data to a csv file on the
> network.
> When I run the DTS package manually the file is created and all is OK.
> But when I create a scheduled SQL Server agent job to run my DTS
> package I get the following error message:
> "Executed as user: SERVER\SYSTEM.
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259
> (80004005)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> Error Detail Records:
> Error: 5 (5); Provider Error: 5 (5)
> Error string: Error opening datafile: Access is denied.
> Error source: Microsoft Data Transformation Services Flat File Rowset
> Provider
> Help file: DTSFFile.hlp
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 D
> TSRun: Package execution complete. Process Exit Code 1. The step
> failed."
> I understand as much as the user running the job hasn't got access to
> the file...
> So.. how do I change the user that executes the job'
> Thanks for your help.
> Mikael
>|||Hello
It looks like the SQLSERVERAGENT runs with the Local System account.
So if I change the "Log On As" user to at user with rights to my file
I'll be OK?
I'll try that thanks..
Mikael|||McA wrote:
> Hello
> It looks like the SQLSERVERAGENT runs with the Local System account.
> So if I change the "Log On As" user to at user with rights to my file
> I'll be OK?
> I'll try that thanks..
> Mikael
>
There's more to consider than whether the user has rights to your file,
but yes, that's the solution to your problem. You need to read about
Service Accounts in Books Online before changing anything, to make sure
you understand the requirements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Friday, March 23, 2012

Problems with reporting

Hi,
I am testing MS reporting on VS 2005 Beta2 with the report file (.rdlc) embedded in a Windows application (no server involved).
Previewing the report works fine, but if I want to print it, I apparently must first
click on the print preview icon in the viewer's toolbar and then click on the print icon.
I believe that one should be able to print a report without showing the form containing the viewer; but is there at least a way of forcing the viewer into the print preview state by means of code?
Will something change in this respect in the delivery version?

One more question: is the version of Crystal Reports bundled in VS capable of building a report based on business objects? I could not find any way of performing this task.

Thanks

ReportViewer has SetDisplayMode () method that can be used to switch between Normal and PrintPreview modes.

|||

Hi Lev,
the SetDisplayMode method does not exist in the version of VS2005 I am using (Beta2.050215.4400).
Do you have a newer version?

|||

You can download the Release Candidate of Visual Studio 2005 via MSDN Subscriber Downloads.

problems with Publish Reports Script File

How do i make the datasources same path for each report loaded
I have 5 group sets
I end up manually change the datasource for each one
If i can make this script files to do it

what changes do i need to make ?

__
' Publish Reports
' Adapted from Microsoft Publish Reports Sample Script File


Dim parentfolder as string
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentPath As String = "/" + parentFolder
Dim filePath As String

Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim name As String


parentfolder = "DataSources"
parentpath = "/" + parentFolder

'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

CreateSampleDataSource()


'Publish the sample reports

parentfolder = "Polling Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\Polling Reports\"


PublishReport("Polling Interest")

parentfolder = "Mail Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\Mail Reports\"


PublishReport("Code Usage")
PublishReport("Communication History")
PublishReport("Mail Staff Load")
PublishReport("Outstanding Mail Staff")
PublishReport("Outstanding Mail Summary")
PublishReport("Top Code Usage")
PublishReport("Group Mail Summary")

parentfolder = "Casework Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try


filePath = "c:\apps\Corr6\Server\Reporting Services\Casework Reports\"

PublishReport("Casework Closed")
PublishReport("Casework County")
PublishReport("Casework Detail")
PublishReport("Casework Outstanding")
PublishReport("Casework Staff")
PublishReport("Casework Summary")

parentfolder = "WorkFlow Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try


filePath = "c:\apps\Corr6\Server\Reporting Services\workflow reports\"

PublishReport("Staff Productivity Report")
PublishReport("Overdue Tasks")

parentfolder = "System Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\System Reports\"
PublishReport("Interest System Codes")
PublishReport("Letter System Codes")
PublishReport("Personal System Codes")
PublishReport("System Staff")

End Sub

Public Sub CreateSampleDataSource()
Dim name As String = "DataSource"
Dim parent As String = "/" + parentFolder

'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "data source=(local);initial catalog=cap_win"
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False

Try
rs.CreateDataSource(name, parent, False, definition, Nothing)

Catch e As Exception
Console.WriteLine(e.Message)
End Try

End Sub

Public Sub PublishReport(ByVal reportName As String)
Try
Dim stream As FileStream = File.OpenRead(filePath + reportName + ".rdl")
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()

Catch e As IOException
Console.WriteLine(e.Message)
End Try

Try
warnings = rs.CreateReport(reportName, parentPath, False, definition, Nothing)

If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning

Else
Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
End If

Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub

Anybody know what to do ?|||

can it be done ?
any reference where i might get the answer ?

problems with Publish Reports Script File

How do i make the datasources same path for each report loaded
I have 5 group sets
I end up manually change the datasource for each one
If i can make this script files to do it

what changes do i need to make ?

__
' Publish Reports
' Adapted from Microsoft Publish Reports Sample Script File


Dim parentfolder as string
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentPath As String = "/" + parentFolder
Dim filePath As String

Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim name As String


parentfolder = "DataSources"
parentpath = "/" + parentFolder

'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

CreateSampleDataSource()


'Publish the sample reports

parentfolder = "Polling Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\Polling Reports\"


PublishReport("Polling Interest")

parentfolder = "Mail Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\Mail Reports\"


PublishReport("Code Usage")
PublishReport("Communication History")
PublishReport("Mail Staff Load")
PublishReport("Outstanding Mail Staff")
PublishReport("Outstanding Mail Summary")
PublishReport("Top Code Usage")
PublishReport("Group Mail Summary")

parentfolder = "Casework Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try


filePath = "c:\apps\Corr6\Server\Reporting Services\Casework Reports\"

PublishReport("Casework Closed")
PublishReport("Casework County")
PublishReport("Casework Detail")
PublishReport("Casework Outstanding")
PublishReport("Casework Staff")
PublishReport("Casework Summary")

parentfolder = "WorkFlow Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try


filePath = "c:\apps\Corr6\Server\Reporting Services\workflow reports\"

PublishReport("Staff Productivity Report")
PublishReport("Overdue Tasks")

parentfolder = "System Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\System Reports\"
PublishReport("Interest System Codes")
PublishReport("Letter System Codes")
PublishReport("Personal System Codes")
PublishReport("System Staff")

End Sub

Public Sub CreateSampleDataSource()
Dim name As String = "DataSource"
Dim parent As String = "/" + parentFolder

'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "data source=(local);initial catalog=cap_win"
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False

Try
rs.CreateDataSource(name, parent, False, definition, Nothing)

Catch e As Exception
Console.WriteLine(e.Message)
End Try

End Sub

Public Sub PublishReport(ByVal reportName As String)
Try
Dim stream As FileStream = File.OpenRead(filePath + reportName + ".rdl")
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()

Catch e As IOException
Console.WriteLine(e.Message)
End Try

Try
warnings = rs.CreateReport(reportName, parentPath, False, definition, Nothing)

If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning

Else
Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
End If

Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub

Anybody know what to do ?|||

can it be done ?
any reference where i might get the answer ?

Wednesday, March 21, 2012

Problems with parsing large XML data

Good day.
I'm trying to import an large XML data file (about 10M) in several tables in
MSSQL database.
There are a couple of entities in this file, one entity with large amount of
records (about 50000), other with less than 2000 records. There were no
problems with small recordsets, i've parsed and imported them using iteratio
n
through cursors like:
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
catarget nvarchar(256) 'catarget',
caquant nvarchar(256) 'caquant',
casum money 'casum'
)
But i couldn't import large recordsets, SQL server just silently rollbacks
transaction, it even doesn't throw any errors.
I've tried cursors (several combinations of static, dynamic, locak and
global modifyers) and INSERT FROM SELECT methods of parsing. When i
experimented used cursors, maximum row numbers in file before fault was abou
t
5000, with INSERT FROM SELECT (into temp. table) maximum rows number before
fault was like 1500 record. Approx. record size is near 1 k.
Any suggestions on this problem?UPDATE:
Having had run the same SP on my 512 RAM notebook instead of 256RAM PC, i
didn't come across this fault.|||It should not throw no errors, so either you have an older version or you
found a n interesting problem.
What version of SQL Server 2000 are you running (what service pack?) and how
did you install it (upgrade or fresh)?
Also, I would recommend that you copy the information into a table (could be
a temp table) from OpenXML and then do the cursor over that table (and why
do you need cursors in the first place?).
Best regards
Michael
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:D58AF7A2-1543-4EC8-8AA7-8AE763BCDD3D@.microsoft.com...
> Good day.
> I'm trying to import an large XML data file (about 10M) in several tables
> in
> MSSQL database.
> There are a couple of entities in this file, one entity with large amount
> of
> records (about 50000), other with less than 2000 records. There were no
> problems with small recordsets, i've parsed and imported them using
> iteration
> through cursors like:
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> catarget nvarchar(256) 'catarget',
> caquant nvarchar(256) 'caquant',
> casum money 'casum'
> )
> But i couldn't import large recordsets, SQL server just silently rollbacks
> transaction, it even doesn't throw any errors.
> I've tried cursors (several combinations of static, dynamic, locak and
> global modifyers) and INSERT FROM SELECT methods of parsing. When i
> experimented used cursors, maximum row numbers in file before fault was
> about
> 5000, with INSERT FROM SELECT (into temp. table) maximum rows number
> before
> fault was like 1500 record. Approx. record size is near 1 k.
> Any suggestions on this problem?|||"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23XREgotDFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Also, I would recommend that you copy the information into a table (could
be
> a temp table) from OpenXML and then do the cursor over that table (and why
> do you need cursors in the first place?).
I was going to suggest that the OP lose the cursor altogether until I
noticed:
So apparently, the OP had a non-cursor solution to begin with...?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"Michael Rys [MSFT]" wrote:

> It should not throw no errors, so either you have an older version or you
> found a n interesting problem.
> What version of SQL Server 2000 are you running (what service pack?) and h
ow
> did you install it (upgrade or fresh)?
version with fault is MSDE
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
fresh installation, withous any service packs

> Also, I would recommend that you copy the information into a table (could
be
> a temp table) from OpenXML and then do the cursor over that table (and why
> do you need cursors in the first place?).
>
I use cursors because each record in XML file has to be splitted between a
couple of tables, some of them have identity surrogate PK's which of course
aren't supplied in XML file. In short, this XML doesn't represents the
database design. So I couldn't just bulk insert records in tables.
Anyway, i've tried ti use temporary tables, but without much beneficial
effects.
Only running on other server that has more RAM helps.
Below is the script of SP. It fails on last OPENXML. It contsains some
cyrillic characters, so probably you couldn't read error messages and
comments. Also it uses other SP's
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc parseXML
@.XMLData ntext
as
BEGIN
BEGIN TRAN
SET DATEFORMAT dmy
declare @.phone_id int
declare @.lastphonenum nvarchar(20)
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XMLData
--CALL PERIOD
declare @.callperiod datetime
declare @.callperiod_id int
select @.callperiod = callperiod from OPENXML (@.hdoc, '/bill', 2) with
(callperiod nvarchar(20))
IF ( @.@.ROWCOUNT > 0)
BEGIN
EXEC sp_addcallperiod @.callperiod, @.callperiod_id OUTPUT
END
ELSE BEGIN
EXEC sp_xml_removedocument @.hdoc
RAISERROR ('Не найден элемент callperiod', 16, 1)
ROLLBACK TRAN
return -1
END
-- номера телефонов и summary
declare @.phonenum varchar(20)
declare @.phonesummary money
declare @.summary_id int
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone', 2)
with (phonenumber nvarchar(20) './phonenumber',
phonesummary money './summary'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--добавляем номер телефона
exec sp_addphone @.phonenum, @.phone_id OUTPUT
--добавляем summary
exec sp_addphonesummary @.phonesummary, @.phone_id, @.callperiod_id,
@.summary_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
END
CLOSE phonecursor
DEALLOCATE phonecursor
--operaggr
declare @.oaname nvarchar(128)
declare @.oasum money
declare @.aggr_id int
select @.lastphonenum = ''
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/operaggrs/operaggr', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
oaname nvarchar(128) 'oaname',
oasum money 'oasum'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--получаем ID номера телефона
IF @.lastphonenum <> @.phonenum
BEGIN
exec sp_addphone @.phonenum, @.phone_id OUTPUT
select @.lastphonenum = @.phonenum
END
--добавляем агрегаты
exec sp_addphoneoperationaggr @.oasum, @.phone_id, @.callperiod_id,
@.oaname, @.aggr_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
END
CLOSE phonecursor
DEALLOCATE phonecursor
--callaggr
declare @.catarget nvarchar(256)
declare @.caquant nvarchar(20)
declare @.casum money
declare @.caggr_id int
select @.lastphonenum = ''
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
catarget nvarchar(256) 'catarget',
caquant nvarchar(256) 'caquant',
casum money 'casum'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--получаем ID номера телефона
IF @.lastphonenum <> @.phonenum
BEGIN
exec sp_addphone @.phonenum, @.phone_id OUTPUT
select @.lastphonenum = @.phonenum
END
--добавляем агрегаты
exec sp_addphonecallaggr @.catarget, @.caquant, @.casum, @.phone_id,
@.callperiod_id, @.caggr_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
END
CLOSE phonecursor
DEALLOCATE phonecursor
--звонки
--THIS SECTION FAILS!!!!
declare @.i int
select @.i = 0
declare @.call_id int
declare @.price money
declare @.pszone nvarchar(128)
declare @.vtkzone nvarchar(128)
declare @.ctchar char(1)
declare @.ctype nvarchar(20)
declare @.amount nvarchar(20)
declare @.direct int
declare @.ctime nvarchar(30)
declare @.dt datetime
select @.lastphonenum = ''
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/calls/call', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
price money 'price',
pszone nvarchar(128) 'pszone',
vtkzone nvarchar(128) 'vtkzone',
ctchar char(1) 'ctchar',
ctype nvarchar(20) 'ctype',
amount nvarchar(20) 'amount',
direct int 'direct',
ctime nvarchar(30) 'ctime'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
@.ctchar, @.ctype, @.amount, @.direct, @.ctime
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
select @.dt = cast(@.ctime as datetime)
--получаем ID номера телефона
IF @.lastphonenum <> @.phonenum
BEGIN
exec sp_addphone @.phonenum, @.phone_id OUTPUT
select @.lastphonenum = @.phonenum
END
exec sp_addcall @.dt, @.amount, @.price, @.pszone, @.vtkzone, @.ctype, @.ctchar,
@.phone_id, @.direct, @.callperiod_id, @.call_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
@.ctchar, @.ctype, @.amount, @.direct, @.ctime
end;
CLOSE phonecursor
DEALLOCATE phonecursor
EXEC sp_xml_removedocument @.hdoc
COMMIT TRAN
return 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Structure of XML file is like
<?xml version="1.0"?>
<bill>
<callperiod>30.11.2004</callperiod>
<phones>
<phone>
<phonenumber>0923061267</phonenumber>
<summary>241.9676</summary>
<operaggrs>
<operaggr>
<oaname>WAP (старая)</oaname>
<oasum>1.8311</oasum>
</operaggr>
<operaggr>
<oaname>Абонентская плата/ Обязательная
ежемесячная
плата за трафик</oaname>
<oasum>5.5117</oasum>
</operaggr>
</operaggrs>
<callaggrs>
<callaggr>
<catarget>SMS исходящее: 12 категория</catarget>
<caquant>1 факт</caquant>
<casum>0.75</casum>
</callaggr>
<callaggr>
<catarget>SMS входящее</catarget>
<caquant>4 факт</caquant>
<casum>0</casum>
</callaggr>
</callaggrs>
<calls>
<call>
<price>0</price>
<pszone></pszone>
<vtkzone></vtkzone>
<ctchar></ctchar>
<ctype>Телеф.</ctype>
<amount>0: 01</amount>
<direct>0</direct>
<ctime>12.11.2004 15:15:00</ctime>
</call>
<call>
<price>0</price>
<pszone></pszone>
<vtkzone></vtkzone>
<ctchar></ctchar>
<ctype>Телеф.</ctype>
<amount>0: 01</amount>
<direct>1</direct>
<ctime>12.11.2004 15:15:00</ctime>
</call>
</calls>
</phone>
<phone>
.......
</phone>
..........
</phones>
</bill>
there are about 120 <phone> nodes and about 50000 total <call> nodes in it|||Have you thought about writing a transform to generate a script of insert
statements?
Then just run in the script through query analyser.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:D58AF7A2-1543-4EC8-8AA7-8AE763BCDD3D@.microsoft.com...
> Good day.
> I'm trying to import an large XML data file (about 10M) in several tables
> in
> MSSQL database.
> There are a couple of entities in this file, one entity with large amount
> of
> records (about 50000), other with less than 2000 records. There were no
> problems with small recordsets, i've parsed and imported them using
> iteration
> through cursors like:
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> catarget nvarchar(256) 'catarget',
> caquant nvarchar(256) 'caquant',
> casum money 'casum'
> )
> But i couldn't import large recordsets, SQL server just silently rollbacks
> transaction, it even doesn't throw any errors.
> I've tried cursors (several combinations of static, dynamic, locak and
> global modifyers) and INSERT FROM SELECT methods of parsing. When i
> experimented used cursors, maximum row numbers in file before fault was
> about
> 5000, with INSERT FROM SELECT (into temp. table) maximum rows number
> before
> fault was like 1500 record. Approx. record size is near 1 k.
> Any suggestions on this problem?|||
"David McNally" wrote:

> Have you thought about writing a transform to generate a script of insert
> statements?
> Then just run in the script through query analyser.
Unfortunatelly, it is against requirements specification. As a part of lare
automatization project, it should be a SP which processes XML data stream.
Application is to supply this stream via ADO.|||Thanks for the info. I will have this looked at by our team. Note that this
looks like the RTM version of SQL Server which is vulnerable to the Slammer
virus. So you may want to try it with MSDE SP3a (and download the MSXML 2.6
SP3).
Best regards
Michael
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:28438F56-430E-45F1-AFB3-AD05548ADA24@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
>
> version with fault is MSDE
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
> fresh installation, withous any service packs
>
> I use cursors because each record in XML file has to be splitted between a
> couple of tables, some of them have identity surrogate PK's which of
> course
> aren't supplied in XML file. In short, this XML doesn't represents the
> database design. So I couldn't just bulk insert records in tables.
> Anyway, i've tried ti use temporary tables, but without much beneficial
> effects.
> Only running on other server that has more RAM helps.
>
> Below is the script of SP. It fails on last OPENXML. It contsains some
> cyrillic characters, so probably you couldn't read error messages and
> comments. Also it uses other SP's
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
>
> CREATE proc parseXML
> @.XMLData ntext
> as
> BEGIN
> BEGIN TRAN
> SET DATEFORMAT dmy
> declare @.phone_id int
> declare @.lastphonenum nvarchar(20)
> DECLARE @.hdoc int
> EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XMLData
> --CALL PERIOD
> declare @.callperiod datetime
> declare @.callperiod_id int
> select @.callperiod = callperiod from OPENXML (@.hdoc, '/bill', 2) with
> (callperiod nvarchar(20))
> IF ( @.@.ROWCOUNT > 0)
> BEGIN
> EXEC sp_addcallperiod @.callperiod, @.callperiod_id OUTPUT
> END
> ELSE BEGIN
> EXEC sp_xml_removedocument @.hdoc
> RAISERROR ('' ' '? callperiod', 16, 1)
> ROLLBACK TRAN
> return -1
> END
> -- ' '? ? summary
> declare @.phonenum varchar(20)
> declare @.phonesummary money
> declare @.summary_id int
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone', 2)
> with (phonenumber nvarchar(20) './phonenumber',
> phonesummary money './summary'
> )
> OPEN phonecursor
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --'? '? ''
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> --'? summary
> exec sp_addphonesummary @.phonesummary, @.phone_id, @.callperiod_id,
> @.summary_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
> END
>
> CLOSE phonecursor
> DEALLOCATE phonecursor
> --operaggr
> declare @.oaname nvarchar(128)
> declare @.oasum money
> declare @.aggr_id int
> select @.lastphonenum = ''
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/operaggrs/operaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> oaname nvarchar(128) 'oaname',
> oasum money 'oasum'
> )
> OPEN phonecursor
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --'' ID ' ''
> IF @.lastphonenum <> @.phonenum
> BEGIN
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> select @.lastphonenum = @.phonenum
> END
> --'? ''
> exec sp_addphoneoperationaggr @.oasum, @.phone_id, @.callperiod_id,
> @.oaname, @.aggr_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
> END
> CLOSE phonecursor
> DEALLOCATE phonecursor
>
> --callaggr
>
> declare @.catarget nvarchar(256)
> declare @.caquant nvarchar(20)
> declare @.casum money
> declare @.caggr_id int
> select @.lastphonenum = ''
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> catarget nvarchar(256) 'catarget',
> caquant nvarchar(256) 'caquant',
> casum money 'casum'
> )
> OPEN phonecursor
>
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --'' ID ' ''
> IF @.lastphonenum <> @.phonenum
> BEGIN
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> select @.lastphonenum = @.phonenum
> END
> --'? ''
> exec sp_addphonecallaggr @.catarget, @.caquant, @.casum, @.phone_id,
> @.callperiod_id, @.caggr_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
> END
>
>
> CLOSE phonecursor
> DEALLOCATE phonecursor
>
> --'
> --THIS SECTION FAILS!!!!
> declare @.i int
> select @.i = 0
> declare @.call_id int
> declare @.price money
> declare @.pszone nvarchar(128)
> declare @.vtkzone nvarchar(128)
> declare @.ctchar char(1)
> declare @.ctype nvarchar(20)
> declare @.amount nvarchar(20)
> declare @.direct int
> declare @.ctime nvarchar(30)
> declare @.dt datetime
> select @.lastphonenum = ''
>
>
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/calls/call', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> price money 'price',
> pszone nvarchar(128) 'pszone',
> vtkzone nvarchar(128) 'vtkzone',
> ctchar char(1) 'ctchar',
> ctype nvarchar(20) 'ctype',
> amount nvarchar(20) 'amount',
> direct int 'direct',
> ctime nvarchar(30) 'ctime'
> )
> OPEN phonecursor
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
> @.ctchar, @.ctype, @.amount, @.direct,
> @.ctime
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> select @.dt = cast(@.ctime as datetime)
> --'' ID ' ''
> IF @.lastphonenum <> @.phonenum
> BEGIN
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> select @.lastphonenum = @.phonenum
> END
>
> exec sp_addcall @.dt, @.amount, @.price, @.pszone, @.vtkzone, @.ctype,
> @.ctchar,
> @.phone_id, @.direct, @.callperiod_id, @.call_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
> @.ctchar, @.ctype, @.amount, @.direct,
> @.ctime
> end;
>
> CLOSE phonecursor
> DEALLOCATE phonecursor
> EXEC sp_xml_removedocument @.hdoc
> COMMIT TRAN
> return 0
> END
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> --
> Structure of XML file is like
> <?xml version="1.0"?>
> <bill>
> <callperiod>30.11.2004</callperiod>
> <phones>
> <phone>
> <phonenumber>0923061267</phonenumber>
> <summary>241.9676</summary>
> <operaggrs>
> <operaggr>
> <oaname>WAP (')</oaname>
> <oasum>1.8311</oasum>
> </operaggr>
> <operaggr>
> <oaname>''? '?/ '' ''?
> '? ' '</oaname>
> <oasum>5.5117</oasum>
> </operaggr>
> </operaggrs>
> <callaggrs>
> <callaggr>
> <catarget>SMS '?: 12 '?</catarget>
> <caquant>1 '</caquant>
> <casum>0.75</casum>
> </callaggr>
> <callaggr>
> <catarget>SMS ''</catarget>
> <caquant>4 '</caquant>
> <casum>0</casum>
> </callaggr>
> </callaggrs>
> <calls>
> <call>
> <price>0</price>
> <pszone></pszone>
> <vtkzone></vtkzone>
> <ctchar></ctchar>
> <ctype>'?.</ctype>
> <amount>0: 01</amount>
> <direct>0</direct>
> <ctime>12.11.2004 15:15:00</ctime>
> </call>
> <call>
> <price>0</price>
> <pszone></pszone>
> <vtkzone></vtkzone>
> <ctchar></ctchar>
> <ctype>'?.</ctype>
> <amount>0: 01</amount>
> <direct>1</direct>
> <ctime>12.11.2004 15:15:00</ctime>
> </call>
> </calls>
> </phone>
> <phone>
> ........
> </phone>
> ...........
> </phones>
> </bill>
> --
> there are about 120 <phone> nodes and about 50000 total <call> nodes in it|||-- You can use @.temp/#temp tables and xml "metaproperties"
-- (see SQL Help files) to avoid cursors as follows:
-- Note that this is completely untested code.
-- I have implemented the concepts successfully, and
-- the code parser doesn't complain, but I didn't
-- put forth the effort to build some test data
-- or tables.
CREATE PROCEDURE parseXML (@.XMLData ntext)
AS
BEGIN
BEGIN TRAN
SET DATEFORMAT dmy
declare @.callperiod datetime
declare @.callperiod_id int
declare @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XMLData
--CALL PERIOD
SELECT @.callperiod = callperiod from OPENXML (@.hdoc, '/bill', 2) WITH
(callperiod nvarchar(20))
IF (@.@.ROWCOUNT > 0)
BEGIN
EXEC sp_addcallperiod @.callperiod,
@.callperiod_id OUTPUT
END
ELSE
BEGIN
EXEC sp_xml_removedocument @.hdoc
RAISERROR ('' ' '? callperiod', 16,
1)
ROLLBACK TRAN
return -1
END
-- ' '? ? summary
DECLARE @.t_phonesummary table (
phone_id int
,phonenum nvrchar(20)
,phonesummary money
,phone_node_id int
)
INSERT INTO @.t_phonesummary (
phonenum
,phonesummary
,phone_node_id
)
SELECT
XMLPhone.phonenum
,XMLPhone.phonesummary
,XMLPhone.phone_node_id
FROM OPENXML (@.hdoc, '/bill/phones/phone', 2)
WITH (
phonenumber nvarchar(20) 'phonenumber'
,phonesummary money 'summary'
,phone_node_id int '@.mp:id'
) AS XMLPhone
-- insert phone numbers that don't already exist
-- if you allow duplicates you could employ a cursur for just this
table, updating
-- t_phonesummary.phone_id = SCOPE_IDENTITY() as you go, with the
remainder of the code
-- functioning without cursors.
INSERT INTO PhoneTable (
phonenum
)
SELECT DISTINCT
t_phonesummary.phonenum
FROM @.t_phonesummary t_phonesummary
WHERE NOT EXISTS (SELECT 1 FROM PhoneTable WHERE phonenum =
t_phonesummary.phonenum)
-- fetch the keys for every phone record (new and old)
UPDATE @.t_phonesummary
SET phone_id = PhoneTable.phone_id
FROM @.t_phonesummary t_phonesummary
INNER JOIN PhoneTable
ON PhoneTable.phonenum = t_phonesummary.phonenum
-- insert phone summary records
-- (assumes no conflicting records exist - you could check if you need
to)
INSERT INTO PhoneSummaryTable (
phone_id
,phonesummary
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_phonesummary.phonesummary
,@.callperiod_id
FROM @.t_phonesummary t_phonesummary
-- op aggr
DECLARE @.t_operationaggr table (
oaname nvarchar(128)
,oasum money
,phone_node_id int
)
INSERT INTO @.t_operationaggr (
oaname
,oasum
,phone_node_id
)
SELECT
XMLOpAggr.oaname
,XMLOpAggr.oasum
,XMLOpAggr.phone_node_id
FROM OPENXML (@.hdoc, '/bill/phones/phone/operaggrs/operaggr', 2)
WITH (
oaname nvarchar(128) 'oaname'
,oasum money 'oasum'
,phone_node_id int '../../@.mp:id'
) AS XMLOpAggr
INSERT INTO OperationAggrTable (
phone_id
,oaname
,oasum
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_operationaggr.oaname
,t_operationaggr.oasum
,@.callperiod_id
FROM @.t_operationaggr t_operationaggr
INNER JOIN @.t_phonesummary t_phonesummary
ON t_phonesummary.phone_node_id =
t_operationaggr.phone_node_id
--callaggr
DECLARE @.t_callaggr table (
catarget nvarchar(256)
,caquant nvarchar(256)
,casum money
,phone_node_id int
)
INSERT INTO @.t_callaggr (
catarget
,caquant
,casum
,phone_node_id
)
SELECT
XMLCallAggr.catarget
,XMLCallAggr.caquant
,XMLCallAggr.casum
,XMLCallAggr.phone_node_id
FROM OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
WITH (
catarget nvarchar(256) 'catarget'
,caquant nvarchar(256) 'caquant'
,casum money 'casum'
,phone_node_id int '../../@.mp:id'
) AS XMLCallAggr
INSERT INTO CallAggrTable (
phone_id
,catarget
,casum
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_callaggr.catarget
,t_callaggr.casum
,@.callperiod_id
FROM @.t_callaggr t_callaggr
INNER JOIN @.t_phonesummary t_phonesummary
ON t_phonesummary.phone_node_id = t_callaggr.phone_node_id
--call
-- Due to the size of this recordset, it may be better to use a regular
temp table (#t_call)
-- if you choose to do so, be sure to declare/create the temp table at
the beginning of the proc to
-- minimize recompiles.
DECLARE @.t_call table (
phone_node_id int
,price money
,pszone nvarchar(128)
,vtkzone nvarchar(128)
,ctchar char(1)
,ctype nvarchar(20)
,amount nvarchar(20)
,direct int
,dt datetime
)
INSERT INTO @.t_call (
phone_node_id
,price
,pszone
,vtkzone
,ctchar
,ctype
,amount
,direct
,dt
)
SELECT
XMLCall.phone_node_id
,XMLCall.price
,XMLCall.pszone
,XMLCall.vtkzone
,XMLCall.ctchar
,XMLCall.ctype
,XMLCall.amount
,XMLCall.direct
,cast(XMLCall.ctime as datetime)
FROM OPENXML (@.hdoc, '/bill/phones/phone/calls/call', 2)
WITH (
phone_node_id int '../../@.mp:id'
,price money 'price'
,pszone nvarchar(128) 'pszone'
,vtkzone nvarchar(128) 'vtkzone'
,ctchar char(1) 'ctchar'
,ctype nvarchar(20) 'ctype'
,amount nvarchar(20) 'amount'
,direct int 'direct'
,ctime nvarchar(30) 'ctime'
) AS XMLCall
INSERT INTO CallTable (
phone_id
,price
,pszone
,vtkzone
,ctchar
,ctype
,amount
,direct
,dt
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_call.price
,t_call.pszone
,t_call.vtkzone
,t_call.ctchar
,t_call.ctype
,t_call.amount
,t_call.direct
,t_call.dt
,@.callperiod_id
FROM @.t_call t_call
INNER JOIN @.t_phonesummary t_phonesummary
ON t_phonesummary.phone_node_id = t_call.phone_node_id
EXEC sp_xml_removedocument @.hdoc
COMMIT TRAN
return 0
END
GO
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Problems with parsing large XML data

Good day.
I'm trying to import an large XML data file (about 10M) in several tables in
MSSQL database.
There are a couple of entities in this file, one entity with large amount of
records (about 50000), other with less than 2000 records. There were no
problems with small recordsets, i've parsed and imported them using iteration
through cursors like:
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
catarget nvarchar(256) 'catarget',
caquant nvarchar(256) 'caquant',
casum money 'casum'
)
But i couldn't import large recordsets, SQL server just silently rollbacks
transaction, it even doesn't throw any errors.
I've tried cursors (several combinations of static, dynamic, locak and
global modifyers) and INSERT FROM SELECT methods of parsing. When i
experimented used cursors, maximum row numbers in file before fault was about
5000, with INSERT FROM SELECT (into temp. table) maximum rows number before
fault was like 1500 record. Approx. record size is near 1 k.
Any suggestions on this problem?
UPDATE:
Having had run the same SP on my 512 RAM notebook instead of 256RAM PC, i
didn't come across this fault.
|||It should not throw no errors, so either you have an older version or you
found a n interesting problem.
What version of SQL Server 2000 are you running (what service pack?) and how
did you install it (upgrade or fresh)?
Also, I would recommend that you copy the information into a table (could be
a temp table) from OpenXML and then do the cursor over that table (and why
do you need cursors in the first place?).
Best regards
Michael
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:D58AF7A2-1543-4EC8-8AA7-8AE763BCDD3D@.microsoft.com...
> Good day.
> I'm trying to import an large XML data file (about 10M) in several tables
> in
> MSSQL database.
> There are a couple of entities in this file, one entity with large amount
> of
> records (about 50000), other with less than 2000 records. There were no
> problems with small recordsets, i've parsed and imported them using
> iteration
> through cursors like:
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> catarget nvarchar(256) 'catarget',
> caquant nvarchar(256) 'caquant',
> casum money 'casum'
> )
> But i couldn't import large recordsets, SQL server just silently rollbacks
> transaction, it even doesn't throw any errors.
> I've tried cursors (several combinations of static, dynamic, locak and
> global modifyers) and INSERT FROM SELECT methods of parsing. When i
> experimented used cursors, maximum row numbers in file before fault was
> about
> 5000, with INSERT FROM SELECT (into temp. table) maximum rows number
> before
> fault was like 1500 record. Approx. record size is near 1 k.
> Any suggestions on this problem?
|||"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23XREgotDFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Also, I would recommend that you copy the information into a table (could
be
> a temp table) from OpenXML and then do the cursor over that table (and why
> do you need cursors in the first place?).
I was going to suggest that the OP lose the cursor altogether until I
noticed:
[vbcol=seagreen]
So apparently, the OP had a non-cursor solution to begin with...?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||"Michael Rys [MSFT]" wrote:

> It should not throw no errors, so either you have an older version or you
> found a n interesting problem.
> What version of SQL Server 2000 are you running (what service pack?) and how
> did you install it (upgrade or fresh)?
version with fault is MSDE
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
fresh installation, withous any service packs

> Also, I would recommend that you copy the information into a table (could be
> a temp table) from OpenXML and then do the cursor over that table (and why
> do you need cursors in the first place?).
>
I use cursors because each record in XML file has to be splitted between a
couple of tables, some of them have identity surrogate PK's which of course
aren't supplied in XML file. In short, this XML doesn't represents the
database design. So I couldn't just bulk insert records in tables.
Anyway, i've tried ti use temporary tables, but without much beneficial
effects.
Only running on other server that has more RAM helps.
Below is the script of SP. It fails on last OPENXML. It contsains some
cyrillic characters, so probably you couldn't read error messages and
comments. Also it uses other SP's
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc parseXML
@.XMLData ntext
as
BEGIN
BEGIN TRAN
SET DATEFORMAT dmy
declare @.phone_id int
declare @.lastphonenum nvarchar(20)
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XMLData
--CALL PERIOD
declare @.callperiod datetime
declare @.callperiod_id int
select @.callperiod = callperiod from OPENXML (@.hdoc, '/bill', 2) with
(callperiod nvarchar(20))
IF ( @.@.ROWCOUNT > 0)
BEGIN
EXEC sp_addcallperiod @.callperiod, @.callperiod_id OUTPUT
END
ELSE BEGIN
EXEC sp_xml_removedocument @.hdoc
RAISERROR ('Не найден элемент callperiod', 16, 1)
ROLLBACK TRAN
return -1
END
-- номера телефонов и summary
declare @.phonenum varchar(20)
declare @.phonesummary money
declare @.summary_id int
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone', 2)
with (phonenumber nvarchar(20) './phonenumber',
phonesummary money './summary'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--добавляем номер телефона
exec sp_addphone @.phonenum, @.phone_id OUTPUT
--добавляем summary
exec sp_addphonesummary @.phonesummary, @.phone_id, @.callperiod_id,
@.summary_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
END
CLOSE phonecursor
DEALLOCATE phonecursor
--operaggr
declare @.oaname nvarchar(128)
declare @.oasum money
declare @.aggr_id int
select @.lastphonenum = ''
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/operaggrs/operaggr', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
oaname nvarchar(128) 'oaname',
oasum money 'oasum'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--получаем ID номера телефона
IF @.lastphonenum <> @.phonenum
BEGIN
exec sp_addphone @.phonenum, @.phone_id OUTPUT
select @.lastphonenum = @.phonenum
END
--добавляем агрегаты
exec sp_addphoneoperationaggr @.oasum, @.phone_id, @.callperiod_id,
@.oaname, @.aggr_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
END
CLOSE phonecursor
DEALLOCATE phonecursor
--callaggr
declare @.catarget nvarchar(256)
declare @.caquant nvarchar(20)
declare @.casum money
declare @.caggr_id int
select @.lastphonenum = ''
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
catarget nvarchar(256) 'catarget',
caquant nvarchar(256) 'caquant',
casum money 'casum'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
--получаем ID номера телефона
IF @.lastphonenum <> @.phonenum
BEGIN
exec sp_addphone @.phonenum, @.phone_id OUTPUT
select @.lastphonenum = @.phonenum
END
--добавляем агрегаты
exec sp_addphonecallaggr @.catarget, @.caquant, @.casum, @.phone_id,
@.callperiod_id, @.caggr_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
END
CLOSE phonecursor
DEALLOCATE phonecursor
--звонки
--THIS SECTION FAILS!!!!
declare @.i int
select @.i = 0
declare @.call_id int
declare @.price money
declare @.pszone nvarchar(128)
declare @.vtkzone nvarchar(128)
declare @.ctchar char(1)
declare @.ctype nvarchar(20)
declare @.amount nvarchar(20)
declare @.direct int
declare @.ctime nvarchar(30)
declare @.dt datetime
select @.lastphonenum = ''
DECLARE phonecursor CURSOR
LOCAL FOR
select * from OPENXML (@.hdoc, '/bill/phones/phone/calls/call', 2)
with (phonenumber nvarchar(20) '../../phonenumber',
price money 'price',
pszone nvarchar(128) 'pszone',
vtkzone nvarchar(128) 'vtkzone',
ctchar char(1) 'ctchar',
ctype nvarchar(20) 'ctype',
amount nvarchar(20) 'amount',
direct int 'direct',
ctime nvarchar(30) 'ctime'
)
OPEN phonecursor
FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
@.ctchar, @.ctype, @.amount, @.direct, @.ctime
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
select @.dt = cast(@.ctime as datetime)
--получаем ID номера телефона
IF @.lastphonenum <> @.phonenum
BEGIN
exec sp_addphone @.phonenum, @.phone_id OUTPUT
select @.lastphonenum = @.phonenum
END
exec sp_addcall @.dt, @.amount, @.price, @.pszone, @.vtkzone, @.ctype, @.ctchar,
@.phone_id, @.direct, @.callperiod_id, @.call_id OUTPUT
FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
@.ctchar, @.ctype, @.amount, @.direct, @.ctime
end;
CLOSE phonecursor
DEALLOCATE phonecursor
EXEC sp_xml_removedocument @.hdoc
COMMIT TRAN
return 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Structure of XML file is like
<?xml version="1.0"?>
<bill>
<callperiod>30.11.2004</callperiod>
<phones>
<phone>
<phonenumber>0923061267</phonenumber>
<summary>241.9676</summary>
<operaggrs>
<operaggr>
<oaname>WAP (старая)</oaname>
<oasum>1.8311</oasum>
</operaggr>
<operaggr>
<oaname>Абонентская плата/ Обязательная ежемесячная
плата за трафик</oaname>
<oasum>5.5117</oasum>
</operaggr>
</operaggrs>
<callaggrs>
<callaggr>
<catarget>SMS исходящее: 12 категория</catarget>
<caquant>1 факт</caquant>
<casum>0.75</casum>
</callaggr>
<callaggr>
<catarget>SMS входящее</catarget>
<caquant>4 факт</caquant>
<casum>0</casum>
</callaggr>
</callaggrs>
<calls>
<call>
<price>0</price>
<pszone></pszone>
<vtkzone></vtkzone>
<ctchar></ctchar>
<ctype>Телеф.</ctype>
<amount>0: 01</amount>
<direct>0</direct>
<ctime>12.11.2004 15:15:00</ctime>
</call>
<call>
<price>0</price>
<pszone></pszone>
<vtkzone></vtkzone>
<ctchar></ctchar>
<ctype>Телеф.</ctype>
<amount>0: 01</amount>
<direct>1</direct>
<ctime>12.11.2004 15:15:00</ctime>
</call>
</calls>
</phone>
<phone>
........
</phone>
...........
</phones>
</bill>
there are about 120 <phone> nodes and about 50000 total <call> nodes in it
|||Have you thought about writing a transform to generate a script of insert
statements?
Then just run in the script through query analyser.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:D58AF7A2-1543-4EC8-8AA7-8AE763BCDD3D@.microsoft.com...
> Good day.
> I'm trying to import an large XML data file (about 10M) in several tables
> in
> MSSQL database.
> There are a couple of entities in this file, one entity with large amount
> of
> records (about 50000), other with less than 2000 records. There were no
> problems with small recordsets, i've parsed and imported them using
> iteration
> through cursors like:
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> catarget nvarchar(256) 'catarget',
> caquant nvarchar(256) 'caquant',
> casum money 'casum'
> )
> But i couldn't import large recordsets, SQL server just silently rollbacks
> transaction, it even doesn't throw any errors.
> I've tried cursors (several combinations of static, dynamic, locak and
> global modifyers) and INSERT FROM SELECT methods of parsing. When i
> experimented used cursors, maximum row numbers in file before fault was
> about
> 5000, with INSERT FROM SELECT (into temp. table) maximum rows number
> before
> fault was like 1500 record. Approx. record size is near 1 k.
> Any suggestions on this problem?
|||"David McNally" wrote:

> Have you thought about writing a transform to generate a script of insert
> statements?
> Then just run in the script through query analyser.
Unfortunatelly, it is against requirements specification. As a part of lare
automatization project, it should be a SP which processes XML data stream.
Application is to supply this stream via ADO.
|||Thanks for the info. I will have this looked at by our team. Note that this
looks like the RTM version of SQL Server which is vulnerable to the Slammer
virus. So you may want to try it with MSDE SP3a (and download the MSXML 2.6
SP3).
Best regards
Michael
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:28438F56-430E-45F1-AFB3-AD05548ADA24@.microsoft.com...
> "Michael Rys [MSFT]" wrote:
>
> version with fault is MSDE
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
> fresh installation, withous any service packs
> I use cursors because each record in XML file has to be splitted between a
> couple of tables, some of them have identity surrogate PK's which of
> course
> aren't supplied in XML file. In short, this XML doesn't represents the
> database design. So I couldn't just bulk insert records in tables.
> Anyway, i've tried ti use temporary tables, but without much beneficial
> effects.
> Only running on other server that has more RAM helps.
>
> Below is the script of SP. It fails on last OPENXML. It contsains some
> cyrillic characters, so probably you couldn't read error messages and
> comments. Also it uses other SP's
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
>
> CREATE proc parseXML
> @.XMLData ntext
> as
> BEGIN
> BEGIN TRAN
> SET DATEFORMAT dmy
> declare @.phone_id int
> declare @.lastphonenum nvarchar(20)
> DECLARE @.hdoc int
> EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XMLData
> --CALL PERIOD
> declare @.callperiod datetime
> declare @.callperiod_id int
> select @.callperiod = callperiod from OPENXML (@.hdoc, '/bill', 2) with
> (callperiod nvarchar(20))
> IF ( @.@.ROWCOUNT > 0)
> BEGIN
> EXEC sp_addcallperiod @.callperiod, @.callperiod_id OUTPUT
> END
> ELSE BEGIN
> EXEC sp_xml_removedocument @.hdoc
> RAISERROR ('? ? ?? callperiod', 16, 1)
> ROLLBACK TRAN
> return -1
> END
> -- ? ?? ? summary
> declare @.phonenum varchar(20)
> declare @.phonesummary money
> declare @.summary_id int
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone', 2)
> with (phonenumber nvarchar(20) './phonenumber',
> phonesummary money './summary'
> )
> OPEN phonecursor
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --?? ?? ??
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> --?? summary
> exec sp_addphonesummary @.phonesummary, @.phone_id, @.callperiod_id,
> @.summary_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.phonesummary
> END
>
> CLOSE phonecursor
> DEALLOCATE phonecursor
> --operaggr
> declare @.oaname nvarchar(128)
> declare @.oasum money
> declare @.aggr_id int
> select @.lastphonenum = ''
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/operaggrs/operaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> oaname nvarchar(128) 'oaname',
> oasum money 'oasum'
> )
> OPEN phonecursor
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --?? ID ? ??
> IF @.lastphonenum <> @.phonenum
> BEGIN
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> select @.lastphonenum = @.phonenum
> END
> --?? ??
> exec sp_addphoneoperationaggr @.oasum, @.phone_id, @.callperiod_id,
> @.oaname, @.aggr_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.oaname, @.oasum
> END
> CLOSE phonecursor
> DEALLOCATE phonecursor
>
> --callaggr
>
> declare @.catarget nvarchar(256)
> declare @.caquant nvarchar(20)
> declare @.casum money
> declare @.caggr_id int
> select @.lastphonenum = ''
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> catarget nvarchar(256) 'catarget',
> caquant nvarchar(256) 'caquant',
> casum money 'casum'
> )
> OPEN phonecursor
>
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> --?? ID ? ??
> IF @.lastphonenum <> @.phonenum
> BEGIN
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> select @.lastphonenum = @.phonenum
> END
> --?? ??
> exec sp_addphonecallaggr @.catarget, @.caquant, @.casum, @.phone_id,
> @.callperiod_id, @.caggr_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.catarget, @.caquant, @.casum
> END
>
>
> CLOSE phonecursor
> DEALLOCATE phonecursor
>
> --?
> --THIS SECTION FAILS!!!!
> declare @.i int
> select @.i = 0
> declare @.call_id int
> declare @.price money
> declare @.pszone nvarchar(128)
> declare @.vtkzone nvarchar(128)
> declare @.ctchar char(1)
> declare @.ctype nvarchar(20)
> declare @.amount nvarchar(20)
> declare @.direct int
> declare @.ctime nvarchar(30)
> declare @.dt datetime
> select @.lastphonenum = ''
>
>
> DECLARE phonecursor CURSOR
> LOCAL FOR
> select * from OPENXML (@.hdoc, '/bill/phones/phone/calls/call', 2)
> with (phonenumber nvarchar(20) '../../phonenumber',
> price money 'price',
> pszone nvarchar(128) 'pszone',
> vtkzone nvarchar(128) 'vtkzone',
> ctchar char(1) 'ctchar',
> ctype nvarchar(20) 'ctype',
> amount nvarchar(20) 'amount',
> direct int 'direct',
> ctime nvarchar(30) 'ctime'
> )
> OPEN phonecursor
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
> @.ctchar, @.ctype, @.amount, @.direct,
> @.ctime
>
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> select @.dt = cast(@.ctime as datetime)
> --?? ID ? ??
> IF @.lastphonenum <> @.phonenum
> BEGIN
> exec sp_addphone @.phonenum, @.phone_id OUTPUT
> select @.lastphonenum = @.phonenum
> END
>
> exec sp_addcall @.dt, @.amount, @.price, @.pszone, @.vtkzone, @.ctype,
> @.ctchar,
> @.phone_id, @.direct, @.callperiod_id, @.call_id OUTPUT
> FETCH NEXT FROM phonecursor INTO @.phonenum, @.price, @.pszone, @.vtkzone,
> @.ctchar, @.ctype, @.amount, @.direct,
> @.ctime
> end;
>
> CLOSE phonecursor
> DEALLOCATE phonecursor
> EXEC sp_xml_removedocument @.hdoc
> COMMIT TRAN
> return 0
> END
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> --
> Structure of XML file is like
> <?xml version="1.0"?>
> <bill>
> <callperiod>30.11.2004</callperiod>
> <phones>
> <phone>
> <phonenumber>0923061267</phonenumber>
> <summary>241.9676</summary>
> <operaggrs>
> <operaggr>
> <oaname>WAP (?)</oaname>
> <oasum>1.8311</oasum>
> </operaggr>
> <operaggr>
> <oaname>??? ??/ ?? ???
> ?? ? ?</oaname>
> <oasum>5.5117</oasum>
> </operaggr>
> </operaggrs>
> <callaggrs>
> <callaggr>
> <catarget>SMS ??: 12 ??</catarget>
> <caquant>1 ?</caquant>
> <casum>0.75</casum>
> </callaggr>
> <callaggr>
> <catarget>SMS ??</catarget>
> <caquant>4 ?</caquant>
> <casum>0</casum>
> </callaggr>
> </callaggrs>
> <calls>
> <call>
> <price>0</price>
> <pszone></pszone>
> <vtkzone></vtkzone>
> <ctchar></ctchar>
> <ctype>??.</ctype>
> <amount>0: 01</amount>
> <direct>0</direct>
> <ctime>12.11.2004 15:15:00</ctime>
> </call>
> <call>
> <price>0</price>
> <pszone></pszone>
> <vtkzone></vtkzone>
> <ctchar></ctchar>
> <ctype>??.</ctype>
> <amount>0: 01</amount>
> <direct>1</direct>
> <ctime>12.11.2004 15:15:00</ctime>
> </call>
> </calls>
> </phone>
> <phone>
> ........
> </phone>
> ...........
> </phones>
> </bill>
> --
> there are about 120 <phone> nodes and about 50000 total <call> nodes in it
|||-- You can use @.temp/#temp tables and xml "metaproperties"
-- (see SQL Help files) to avoid cursors as follows:
-- Note that this is completely untested code.
-- I have implemented the concepts successfully, and
-- the code parser doesn't complain, but I didn't
-- put forth the effort to build some test data
-- or tables.
CREATE PROCEDURE parseXML (@.XMLData ntext)
AS
BEGIN
BEGIN TRAN
SET DATEFORMAT dmy
declare @.callperiod datetime
declare @.callperiod_id int
declare @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XMLData
--CALL PERIOD
SELECT @.callperiod = callperiod from OPENXML (@.hdoc, '/bill', 2) WITH
(callperiod nvarchar(20))
IF (@.@.ROWCOUNT > 0)
BEGIN
EXEC sp_addcallperiod @.callperiod,
@.callperiod_id OUTPUT
END
ELSE
BEGIN
EXEC sp_xml_removedocument @.hdoc
RAISERROR ('? ? ?? callperiod', 16,
1)
ROLLBACK TRAN
return -1
END
-- ? ?? ? summary
DECLARE @.t_phonesummary table (
phone_id int
,phonenum nvrchar(20)
,phonesummary money
,phone_node_id int
)
INSERT INTO @.t_phonesummary (
phonenum
,phonesummary
,phone_node_id
)
SELECT
XMLPhone.phonenum
,XMLPhone.phonesummary
,XMLPhone.phone_node_id
FROM OPENXML (@.hdoc, '/bill/phones/phone', 2)
WITH (
phonenumber nvarchar(20) 'phonenumber'
,phonesummary money 'summary'
,phone_node_id int '@.mp:id'
) AS XMLPhone
-- insert phone numbers that don't already exist
-- if you allow duplicates you could employ a cursur for just this
table, updating
-- t_phonesummary.phone_id = SCOPE_IDENTITY() as you go, with the
remainder of the code
-- functioning without cursors.
INSERT INTO PhoneTable (
phonenum
)
SELECT DISTINCT
t_phonesummary.phonenum
FROM @.t_phonesummary t_phonesummary
WHERE NOT EXISTS (SELECT 1 FROM PhoneTable WHERE phonenum =
t_phonesummary.phonenum)
-- fetch the keys for every phone record (new and old)
UPDATE @.t_phonesummary
SET phone_id = PhoneTable.phone_id
FROM @.t_phonesummary t_phonesummary
INNER JOIN PhoneTable
ON PhoneTable.phonenum = t_phonesummary.phonenum
-- insert phone summary records
-- (assumes no conflicting records exist - you could check if you need
to)
INSERT INTO PhoneSummaryTable (
phone_id
,phonesummary
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_phonesummary.phonesummary
,@.callperiod_id
FROM @.t_phonesummary t_phonesummary
-- op aggr
DECLARE @.t_operationaggr table (
oaname nvarchar(128)
,oasum money
,phone_node_id int
)
INSERT INTO @.t_operationaggr (
oaname
,oasum
,phone_node_id
)
SELECT
XMLOpAggr.oaname
,XMLOpAggr.oasum
,XMLOpAggr.phone_node_id
FROM OPENXML (@.hdoc, '/bill/phones/phone/operaggrs/operaggr', 2)
WITH (
oaname nvarchar(128) 'oaname'
,oasum money 'oasum'
,phone_node_id int '../../@.mp:id'
) AS XMLOpAggr
INSERT INTO OperationAggrTable (
phone_id
,oaname
,oasum
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_operationaggr.oaname
,t_operationaggr.oasum
,@.callperiod_id
FROM @.t_operationaggr t_operationaggr
INNER JOIN @.t_phonesummary t_phonesummary
ON t_phonesummary.phone_node_id =
t_operationaggr.phone_node_id
--callaggr
DECLARE @.t_callaggr table (
catarget nvarchar(256)
,caquant nvarchar(256)
,casum money
,phone_node_id int
)
INSERT INTO @.t_callaggr (
catarget
,caquant
,casum
,phone_node_id
)
SELECT
XMLCallAggr.catarget
,XMLCallAggr.caquant
,XMLCallAggr.casum
,XMLCallAggr.phone_node_id
FROM OPENXML (@.hdoc, '/bill/phones/phone/callaggrs/callaggr', 2)
WITH (
catarget nvarchar(256) 'catarget'
,caquant nvarchar(256) 'caquant'
,casum money 'casum'
,phone_node_id int '../../@.mp:id'
) AS XMLCallAggr
INSERT INTO CallAggrTable (
phone_id
,catarget
,casum
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_callaggr.catarget
,t_callaggr.casum
,@.callperiod_id
FROM @.t_callaggr t_callaggr
INNER JOIN @.t_phonesummary t_phonesummary
ON t_phonesummary.phone_node_id = t_callaggr.phone_node_id
--call
-- Due to the size of this recordset, it may be better to use a regular
temp table (#t_call)
-- if you choose to do so, be sure to declare/create the temp table at
the beginning of the proc to
-- minimize recompiles.
DECLARE @.t_call table (
phone_node_id int
,price money
,pszone nvarchar(128)
,vtkzone nvarchar(128)
,ctchar char(1)
,ctype nvarchar(20)
,amount nvarchar(20)
,direct int
,dt datetime
)
INSERT INTO @.t_call (
phone_node_id
,price
,pszone
,vtkzone
,ctchar
,ctype
,amount
,direct
,dt
)
SELECT
XMLCall.phone_node_id
,XMLCall.price
,XMLCall.pszone
,XMLCall.vtkzone
,XMLCall.ctchar
,XMLCall.ctype
,XMLCall.amount
,XMLCall.direct
,cast(XMLCall.ctime as datetime)
FROM OPENXML (@.hdoc, '/bill/phones/phone/calls/call', 2)
WITH (
phone_node_id int '../../@.mp:id'
,price money 'price'
,pszone nvarchar(128) 'pszone'
,vtkzone nvarchar(128) 'vtkzone'
,ctchar char(1) 'ctchar'
,ctype nvarchar(20) 'ctype'
,amount nvarchar(20) 'amount'
,direct int 'direct'
,ctime nvarchar(30) 'ctime'
) AS XMLCall
INSERT INTO CallTable (
phone_id
,price
,pszone
,vtkzone
,ctchar
,ctype
,amount
,direct
,dt
,callperiod_id
)
SELECT
t_phonesummary.phone_id
,t_call.price
,t_call.pszone
,t_call.vtkzone
,t_call.ctchar
,t_call.ctype
,t_call.amount
,t_call.direct
,t_call.dt
,@.callperiod_id
FROM @.t_call t_call
INNER JOIN @.t_phonesummary t_phonesummary
ON t_phonesummary.phone_node_id = t_call.phone_node_id
EXEC sp_xml_removedocument @.hdoc
COMMIT TRAN
return 0
END
GO
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!