Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts

Friday, March 30, 2012

Problems with Ttransaction Replication

The problems I have encountered with SQL 2000 transaction replication are as
follows:
1. There a lot of tables with identity columns.
2. There are about tables that don't have a primary key, thus they
can't be replicated.
3. Replication only monitors Insert, Replace and Delete commands in
the transaction logs.
I'm not too sure what the question is here - or is it just a moan ?
Transactional replication monitors data changes to a table with a primary
key. In SQL 2005 there is also the possibility of replicating most schema
changes automatically. That is the starting point. It might be that merge
replication would be more appropriate for your requirements if it is
impossible to add PKs.
HTH,
Paul Ibison

Problems with transactional replication

Hello All!
Today I've found one strange problem ... I have tables like
something_another_2004_9 (so name_YEAR_MONTH) ... funny thing happened when
I got to the month 10,
I see that the insert/update/delete stored procedures are missing the last
digit in their name.
So, for the 2004_9 month I get sp_MSins_something_another_2004_9,
sp_MSupd_something_another_2004_9, etc .
But for the 2004_10 month I get sp_MSins_something_another_2004_1,
sp_MSupd_something_another_2004_1 ... where is the last 0!? Same happened
for 2004_11 table.
What could be the problem? Are the table names too long or what ?!
Thanks four your input!
Kind regards,
Dejan
can you post the exact names of the tables which are causing the problems
with stored procedure generation?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dejan Markic" <dejan@.akton.is> wrote in message
news:hy2fd.4744$F6.1274490@.news.siol.net...
> Hello All!
> Today I've found one strange problem ... I have tables like
> something_another_2004_9 (so name_YEAR_MONTH) ... funny thing happened
when
> I got to the month 10,
> I see that the insert/update/delete stored procedures are missing the last
> digit in their name.
> So, for the 2004_9 month I get sp_MSins_something_another_2004_9,
> sp_MSupd_something_another_2004_9, etc .
> But for the 2004_10 month I get sp_MSins_something_another_2004_1,
> sp_MSupd_something_another_2004_1 ... where is the last 0!? Same happened
> for 2004_11 table.
> What could be the problem? Are the table names too long or what ?!
> Thanks four your input!
> Kind regards,
> Dejan
>
>
|||Hello!
Sorry for the late response ...
Full table names are:
billing_record_2004_9, billing_record_2004_10 ...
Paul,
I see normal procs name (without missin last number) ... I don't know if
this makes any difference, but before I ran that I already 'repaired' this
by hand (renaming and creating new procs for this new tables). I'm just
afraid that this will happen again next month (12 = two letters ) Can I
check that somehow ? What could be the case here?
Kind regards,
Dejan
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ebe9KdouEHA.1404@.TK2MSFTNGP11.phx.gbl...
| can you post the exact names of the tables which are causing the problems
| with stored procedure generation?
|
| --
| Hilary Cotter
| Looking for a SQL Server replication book?
| http://www.nwsu.com/0974973602.html
|
|
| "Dejan Markic" <dejan@.akton.is> wrote in message
| news:hy2fd.4744$F6.1274490@.news.siol.net...
| > Hello All!
| >
| > Today I've found one strange problem ... I have tables like
| > something_another_2004_9 (so name_YEAR_MONTH) ... funny thing happened
| when
| > I got to the month 10,
| > I see that the insert/update/delete stored procedures are missing the
last
| > digit in their name.
| > So, for the 2004_9 month I get sp_MSins_something_another_2004_9,
| > sp_MSupd_something_another_2004_9, etc .
| > But for the 2004_10 month I get sp_MSins_something_another_2004_1,
| > sp_MSupd_something_another_2004_1 ... where is the last 0!? Same
happened
| > for 2004_11 table.
| > What could be the problem? Are the table names too long or what ?!
| >
| > Thanks four your input!
| >
| > Kind regards,
| > Dejan
| >
| >
| >
| >
|
|

Problems with Timeout expired

Hi

I have a SQL Server database which has a number of tables and views. Using SQL Server Management Studio Express I can open most of the views without a problem. However, some of the views cause an SQL Connection Error but not every time I open them.

Error Source: Net SqlClient Data Provider

Error Message: Timeout expired. The timeout period expired ....

I have looked at the help given for this error but have come to the conclusion that none of the suggested causes are relevant as most views open without a problem. It would appear that I need to increase the timeout value but I can't find where I do that.

It must be simple..

I have just checked that the Execution Time-out in the SSMSE options is set to 0.

Problems with tables named 'User' and 'Workflow'

Downloaded an app that scripts a Sql Server db. It works well--except with
the two aforementioned tables. These show no INSERT statements. I know
there are problems with a table named 'User'. For example, one cannot SELEC
T
* FROM User. One must SELECT * FROM [User]. I guess something similar is
happening with 'Workflow'.
Q 1: Where do I find a list of the table names that will give me trouble?
Q 2: I do I generate INSERT scripts for these tables?
Thanks,
JerryRJerryR (JerryR@.discussions.microsoft.com) writes:
> Downloaded an app that scripts a Sql Server db. It works well--except
> with the two aforementioned tables. These show no INSERT statements. I
> know there are problems with a table named 'User'. For example, one
> cannot SELECT * FROM User. One must SELECT * FROM [User]. I guess
> something similar is happening with 'Workflow'.
> Q 1: Where do I find a list of the table names that will give me trouble?
Look in Books Online under "Reserved Keyword".
However, Workflow is not a keywords, so that one is funky.

> Q 2: I do I generate INSERT scripts for these tables?
Without knowing that app, it's quite difficult to tell. Maybe you should
ask the vendor/author of the app.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Wednesday, March 28, 2012

Problems with Stored procedures

I am trying to recreate my database from work to my home machines. But use
Sql 2000.
One error I get is that bigint is invalid type - but my tables have bigints
in then
Another is that Scope_Identity is not valid - but it works fine at work.
Here is one Stored procedure (with errors at end).
****************************************
**************
CREATE PROCEDURE AddNewApplicantScreen
(
@.ClientID varChar(20),@.JobID bigInt,@.ApplicantID bigInt,@.PositionID
Int,@.Version Int, @.QuestionUnique Int, @.Answer Int,@.AnswerTime Int
)
AS
if not exists (Select ApplicantID from ftsolutions.dbo.ApplicantScreen
where ClientID = @.ClientID and JobID = @.JobID and ApplicantID =
@.ApplicantID and PositionID = @.PositionID and Version = @.Version
and QuestionUnique = @.QuestionUnique)
insert into
ApplicantScreen(ClientID,JobID,Applicant
ID,PositionID,Version,QuestionUnique
,Answer,AnswerTime)
values(@.ClientID,@.JobID,@.ApplicantID,@.Po
sitionID,@.Version,@.QuestionUnique,@.A
nswer,@.AnswerTime)
else
Update ftsolutions.dbo.ApplicantScreen set Answer=@.Answer,
AnswerTime=@.AnswerTime
where ClientID = @.ClientID and JobID = @.JobID and ApplicantID =
@.ApplicantID and PositionID = @.PositionID and Version = @.Version
and QuestionUnique = @.QuestionUnique
GO
Server: Msg 2715, Level 16, State 3, Procedure AddNewApplicantScreen, Line 0
Column or parameter #2: Cannot find data type bigint.
Server: Msg 2715, Level 16, State 1, Procedure AddNewApplicantScreen, Line 0
Column or parameter #3: Cannot find data type bigint.
Parameter '@.JobID' has an invalid data type.
Parameter '@.ApplicantID' has an invalid data type.
****************************************
************************************
***
Here is another:
****************************************
************************************
***
CREATE PROCEDURE spAddNewResume
(
@.ClientID varChar(20),@.PositionID Int,@.FirstName varChar(30),@.LastName
varChar(30),@.Email varChar(45),@.TicklerPhrase varChar(45),@.ResumeText
text,@.CoverSheet text
)
AS
declare @.ApplicantID int, @.JobID bigInt
if not exists (Select ApplicantID from ftsolutions.dbo.Applicant where
ClientID = @.ClientID and
LastName = @.LastName and FirstName = @.FirstName and Email = @.Email)
begin
begin tran
INSERT INTO Applicant
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
Select @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
Select @.JobID = Scope_Identity()
Select @.ApplicantID=ApplicantID from ftsolutions.dbo.Applicant where JobID
= @.JobID
commit tran
end
else
begin
Select @.ApplicantID = ApplicantID from Applicant where ClientID = @.ClientID
and
LastName = @.LastName and FirstName = @.FirstName and Email = @.Email
INSERT INTO Applicant
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted) values
(@.ClientID,@.ApplicantID,@.PositionID,@.Fir
stName,
@.LastName,@.Email,getdate() )
Select @.JobID = Scope_Identity()
end
INSERT INTO ApplicantResume
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,TicklerPhrase,Resu
meText,CoverSheet,JobID) values
(
@.ClientID,@.ApplicantID,@.PositionID,@.Firs
tName,@.LastName,@.Email,@.TicklerPhras
e,@.ResumeText,@.CoverSheet,@.JobID)
INSERT INTO JobApplicant (ClientID,ApplicantID,PositionID,JobID,R
esume)
Values(@.ClientID,@.ApplicantID,@.PositionI
D,@.JobID,getdate() )
select @.ApplicantID as ApplicantID,@.JobID as JobID
GO
Server: Msg 195, Level 15, State 10, Procedure spAddNewResume, Line 17
'Scope_Identity' is not a recognized function name.
Server: Msg 195, Level 15, State 1, Procedure spAddNewResume, Line 29
'Scope_Identity' is not a recognized function name.
****************************************
************************************
**
Why would that happen?
Thanks,
TomWhat does the following return on your home machine?
SELECT @.@.VERSION
Hope this helps.
Dan Guzman
SQL Server MVP
"tshad" <tfs@.dslextreme.com> wrote in message
news:OawFG9uGFHA.3156@.TK2MSFTNGP10.phx.gbl...
>I am trying to recreate my database from work to my home machines. But use
> Sql 2000.
> One error I get is that bigint is invalid type - but my tables have
> bigints
> in then
> Another is that Scope_Identity is not valid - but it works fine at work.
> Here is one Stored procedure (with errors at end).
> ****************************************
**************
> CREATE PROCEDURE AddNewApplicantScreen
> (
> @.ClientID varChar(20),@.JobID bigInt,@.ApplicantID bigInt,@.PositionID
> Int,@.Version Int, @.QuestionUnique Int, @.Answer Int,@.AnswerTime Int
> )
> AS
> if not exists (Select ApplicantID from ftsolutions.dbo.ApplicantScreen
> where ClientID = @.ClientID and JobID = @.JobID and ApplicantID =
> @.ApplicantID and PositionID = @.PositionID and Version = @.Version
> and QuestionUnique = @.QuestionUnique)
> insert into
> ApplicantScreen(ClientID,JobID,Applicant
ID,PositionID,Version,QuestionUniq
ue
> ,Answer,AnswerTime)
> values(@.ClientID,@.JobID,@.ApplicantID,@.Po
sitionID,@.Version,@.QuestionUnique,
@.A
> nswer,@.AnswerTime)
> else
> Update ftsolutions.dbo.ApplicantScreen set Answer=@.Answer,
> AnswerTime=@.AnswerTime
> where ClientID = @.ClientID and JobID = @.JobID and ApplicantID =
> @.ApplicantID and PositionID = @.PositionID and Version = @.Version
> and QuestionUnique = @.QuestionUnique
> GO
>
> Server: Msg 2715, Level 16, State 3, Procedure AddNewApplicantScreen, Line
> 0
> Column or parameter #2: Cannot find data type bigint.
> Server: Msg 2715, Level 16, State 1, Procedure AddNewApplicantScreen, Line
> 0
> Column or parameter #3: Cannot find data type bigint.
> Parameter '@.JobID' has an invalid data type.
> Parameter '@.ApplicantID' has an invalid data type.
> ****************************************
**********************************
**
> ***
> Here is another:
> ****************************************
**********************************
**
> ***
> CREATE PROCEDURE spAddNewResume
> (
> @.ClientID varChar(20),@.PositionID Int,@.FirstName varChar(30),@.LastName
> varChar(30),@.Email varChar(45),@.TicklerPhrase varChar(45),@.ResumeText
> text,@.CoverSheet text
> )
> AS
> declare @.ApplicantID int, @.JobID bigInt
> if not exists (Select ApplicantID from ftsolutions.dbo.Applicant where
> ClientID = @.ClientID and
> LastName = @.LastName and FirstName = @.FirstName and Email = @.Email)
> begin
> begin tran
> INSERT INTO Applicant
> (ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
> Select
> @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,@.FirstName,
> @.LastName,@.Email,getdate()
> from ftsolutions.dbo.Applicant
> where ClientID = @.ClientID
> Select @.JobID = Scope_Identity()
> Select @.ApplicantID=ApplicantID from ftsolutions.dbo.Applicant where
> JobID
> = @.JobID
> commit tran
> end
> else
> begin
> Select @.ApplicantID = ApplicantID from Applicant where ClientID =
> @.ClientID
> and
> LastName = @.LastName and FirstName = @.FirstName and Email = @.Email
> INSERT INTO Applicant
> (ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
> values
> (@.ClientID,@.ApplicantID,@.PositionID,@.Fir
stName,
> @.LastName,@.Email,getdate() )
> Select @.JobID = Scope_Identity()
> end
> INSERT INTO ApplicantResume
> (ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,TicklerPhrase,Re
su
> meText,CoverSheet,JobID) values
> (
> @.ClientID,@.ApplicantID,@.PositionID,@.Firs
tName,@.LastName,@.Email,@.TicklerPhr
as
> e,@.ResumeText,@.CoverSheet,@.JobID)
> INSERT INTO JobApplicant (ClientID,ApplicantID,PositionID,JobID,R
esume)
> Values(@.ClientID,@.ApplicantID,@.PositionI
D,@.JobID,getdate() )
> select @.ApplicantID as ApplicantID,@.JobID as JobID
> GO
>
> Server: Msg 195, Level 15, State 10, Procedure spAddNewResume, Line 17
> 'Scope_Identity' is not a recognized function name.
> Server: Msg 195, Level 15, State 1, Procedure spAddNewResume, Line 29
> 'Scope_Identity' is not a recognized function name.
> ****************************************
**********************************
**
> **
> Why would that happen?
> Thanks,
> Tom
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%233HvNAvGFHA.2360@.TK2MSFTNGP12.phx.gbl...
> What does the following return on your home machine?
> SELECT @.@.VERSION
I couldn't remember how to get the version.
I did have both Sql Server 7 and 2k on my machine, but I now remember I took
the 2k off when testing the trial version and converting to the real version
before I did it at work.
I just reinstalled 2k and it works fine now.
How stupid.
Thanks,
Tom
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "tshad" <tfs@.dslextreme.com> wrote in message
> news:OawFG9uGFHA.3156@.TK2MSFTNGP10.phx.gbl...
use
ApplicantScreen(ClientID,JobID,Applicant
ID,PositionID,Version,QuestionUnique[col
or=darkred]
values(@.ClientID,@.JobID,@.ApplicantID,@.Po
sitionID,@.Version,@.QuestionUnique,@.A[col
or=darkred]
Line
Line
****************************************
************************************[col
or=darkred]
****************************************
************************************[col
or=darkred]
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,TicklerPhrase,Resu[col
or=darkred]
@.ClientID,@.ApplicantID,@.PositionID,@.Firs
tName,@.LastName,@.Email,@.TicklerPhras[col
or=darkred]
****************************************
************************************[col
or=darkred]
>

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 23, 2012

problems with query

I am trying to tie two tables of data together and I'm having some
issues.

table 1 has about 1700 rows
table 2 has about 1000 rows
table 3 has about 70

they all have one column in common, provider number.

select a.provider, a.[survey-date], a.tag, a.code, a.[scope-severity],
a.state, a.facilityname,
c.prov, c.defic_dt, c.tag_no, c.scopesev, c.theirnam, b.[name]
from table1 a
inner join table3 b on b.prov = a.provider
inner join table2 c on b.prov = c.prov

problem is, I am getting 35000 rows instead of 2700.

What am I missing?
thanks,
M@.On 12 Jul 2006 13:42:50 -0700, M@. wrote:

Quote:

Originally Posted by

>I am trying to tie two tables of data together and I'm having some
>issues.
>
>table 1 has about 1700 rows
>table 2 has about 1000 rows
>table 3 has about 70
>
>they all have one column in common, provider number.
>
>select a.provider, a.[survey-date], a.tag, a.code, a.[scope-severity],
>a.state, a.facilityname,
>c.prov, c.defic_dt, c.tag_no, c.scopesev, c.theirnam, b.[name]
>from table1 a
>inner join table3 b on b.prov = a.provider
>inner join table2 c on b.prov = c.prov
>
>problem is, I am getting 35000 rows instead of 2700.
>
>What am I missing?
>thanks,
>M@.


Hi M@.,

Let's say that for a specific provider, there are 6 rows in table 1, 4
rows in table 2 and 5 rows in table 3. Since your join conditions only
specify that the "provider" values should be equal, each of the 6 rows
for this provider will be joined to each of the 5 in table 3, yielding
30 rows in the intermediate result - and each of those 30 will be
combined with each of the 4 rows for this provider in table 2. The end
result will have 4 * 30 = 120 rows for this single provider!

If you need help correcting the query, then you'll have to post more
details: the structure of your tables (as CREATE TABLE statements,
including all constraints, properties and indexes), some well-chosen
rows of sample data (posted as INSERT statements) and the exected
results.

--
Hugo Kornelis, SQL Server MVP

Wednesday, March 21, 2012

Problems with PIVOT tables

I'm a having a problem with PIVOT tables.

I have a table that stores the results from multiple surveys, (see format below)...

 
SurveyID RespondantID QuestionID Answer========================================================= 1 1 1 X 1 1 2 Y 1 1 3 Z 1 2 1 R 1 2 2 S 1 2 3 T 14 10 1 A 14 10 2 B 14 11 1 C 14 11 2 D

I want to use a pivot table to convert the data it to a format like the following (for surveyID = 1)...

 
RespondantID Q1 Q2 Q3 =============================== 1 A B C 1 R S T

The 2 differences I need from a normal pivot query are...

(a) I don't want to summarise my results at all(b) I have multiple surveys in the same table so need to use an extra clause to pick out info for the survey I am interested in.

So far I have come up with...

SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantlD, QuestionlD, Answer FROM "3_Temp" WHERE SurveylD=3) AS preData PIVOT (
COUNT(Answer) FOR QuestionlD IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS data ORDER BV RespondantlD

But it doesn't work and I can't figure out why (error message when executing in VBA = "RunSQL function requires a SQL argument"

Can anyone help?

Thanks for posting sample data.

The PIVOT function is for SQL Server 2005, but you mention that you are excuting in VBA?

By the way, is this correct?

RespondantID Q1 Q2 Q3
===============================
1 A B C
1 2 R S T

If you need help for your question in either SQL Server 2005 or 2000, please post back.

|||

Yes that should be respondantID 2.

I am building an access data project (.adp) which links to tables in SQL Server 2005 so it is a SQL issue.

Thanks,

Martin

|||

Answer to your Question q: The aggregate function is required here, but you can use MAX instead of COUNT.

Answer to your Question b: No problem. As as you did in your query.

I don't know whether you can run this with your setup. Let us know if you find out.

I can run this in SQL Server 2005 Management Studio:

SELECT RespondantID, [1]As Q1, [2]As Q2, [3]As Q3, [4]As Q4, [5]As Q5, [6]As Q6, [7]As Q7, [8]As Q8, [9]As Q9, [10]

As Q10FROM(SELECT RespondantID, QuestionID, AnswerFROM [3_Temp]

WHERE SurveyID=1)AS preDataPIVOT

(MAX(Answer)FOR QuestionIDIN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))

AS data

ORDERBY RespondantID

|||

Thanks for correcting my code Limno!

The only problem now is that I need to export the data to excel, but access will only do this from a table. I tried saving the above code as a view and then doing

SELECT *INTO tablenameFROM PivotedView
but I get an error saying "could not find PivotedView". Do you know how I can modify the pivot query directly, i.e.
SELECT RespondantID, [1]As Q1 ...INTO PivotedTable
Thanks!|||

This was resolved in post:http://forums.asp.net/t/1185101.aspx

Problems with partition view because it is evaluation version?

Hi
I am trying to get a partition view to work. The
partition view is made up of tables that as far as I can
tell qualify for having a partion column. Each tables is
identical except for the check constraint.
If I try to update a row I get an error that partioning
column was not found.
Updateable partition views only work on the enterprise
edition as I understand it. I am trying this out on the
evaluation edition. Could this be the problem or is it
more likely to be in my view construction?
Sql is:
CREATE TABLE [dbo].[bb_chat_200309] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bb_chat_200310] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[bb_chat_200309] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200309] PRIMARY KEY
CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200309)
GO
ALTER TABLE [dbo].[bb_chat_200310] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200310] PRIMARY KEY
CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200310)
GO
create view dbo.bb_chat as
select
chatID
, yearMonth
, originatedBy
, originatedDate
, terminatedBy
, terminatedDate
from bb_chat_200405
union all
select
chatID
, yearMonth
, originatedBy
, originatedDate
, terminatedBy
, terminatedDate
from bb_chat_200404
Thanks for any help
GlynTry specifying the check constraint as part of the CREATE TABLE. For
example:
CREATE TABLE [dbo].[bb_chat_200309] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL
CONSTRAINT CK_bb_chat_200309_yearmonth CHECK ([yearMonth] = '200309') ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Glyn Walters" <glyn_walters@.hotmail.com> wrote in message
news:f9b601c43e65$9cba4a90$a601280a@.phx.gbl...
> Hi
> I am trying to get a partition view to work. The
> partition view is made up of tables that as far as I can
> tell qualify for having a partion column. Each tables is
> identical except for the check constraint.
> If I try to update a row I get an error that partioning
> column was not found.
> Updateable partition views only work on the enterprise
> edition as I understand it. I am trying this out on the
> evaluation edition. Could this be the problem or is it
> more likely to be in my view construction?
> Sql is:
> CREATE TABLE [dbo].[bb_chat_200309] (
> [chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
> NULL ,
> [yearMonth] [varchar] (6) NOT NULL ,
> [originatedBy] [varchar] (12) NULL ,
> [originatedDate] [datetime] NULL ,
> [terminatedBy] [varchar] (12) NULL ,
> [terminatedDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[bb_chat_200310] (
> [chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
> NULL ,
> [yearMonth] [varchar] (6) NOT NULL ,
> [originatedBy] [varchar] (12) NULL ,
> [originatedDate] [datetime] NULL ,
> [terminatedBy] [varchar] (12) NULL ,
> [terminatedDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[bb_chat_200309] WITH NOCHECK ADD
> CONSTRAINT [pk_bb_chat_200309] PRIMARY KEY
> CLUSTERED
> (
> [chatID],
> [yearMonth]
> ) ON [PRIMARY] ,
> CHECK ([yearMonth] = 200309)
> GO
> ALTER TABLE [dbo].[bb_chat_200310] WITH NOCHECK ADD
> CONSTRAINT [pk_bb_chat_200310] PRIMARY KEY
> CLUSTERED
> (
> [chatID],
> [yearMonth]
> ) ON [PRIMARY] ,
> CHECK ([yearMonth] = 200310)
> GO
> create view dbo.bb_chat as
> select
> chatID
> , yearMonth
> , originatedBy
> , originatedDate
> , terminatedBy
> , terminatedDate
> from bb_chat_200405
> union all
> select
> chatID
> , yearMonth
> , originatedBy
> , originatedDate
> , terminatedBy
> , terminatedDate
> from bb_chat_200404
> Thanks for any help
> Glyn|||Hi. Thanks for the advice. Howvere I do create the tables
like that. The output above was from exporting sql. I
create the tables using this script:
set @.sql = 'create table dbo.bb_chatUser_' + @.YYYYMM + '(
chatID numeric(18, 0) not null
, yearMonth varchar(6) not null constraint
ck_bb_chatUser_' + @.YYYYMM + '_yearmonth check (yearMonth
= ' + @.YYYYMM + ')
, invitedBy varchar (12) not null
, invitedDate datetime not null
, guestID varchar(12) not null
, answeredDate datetime null
, leftDate datetime
)'
exec(@.sql);
I think my tables and view qualify as yearMonth has a non-
overlapping check constraint and is part of the primary
key. Are there any other things I need to do to make the
view updateable?|||drop and re-create the constraints, there is an article by MS
http://support.microsoft.com/defaul...kb;en-us;270013|||Thanks for the advice. If was my mistake in the end the
check constraints were yearMonth = 200310 rather
than '200310'.

Problems with partition view because it is evaluation version?

Hi
I am trying to get a partition view to work. The
partition view is made up of tables that as far as I can
tell qualify for having a partion column. Each tables is
identical except for the check constraint.
If I try to update a row I get an error that partioning
column was not found.
Updateable partition views only work on the enterprise
edition as I understand it. I am trying this out on the
evaluation edition. Could this be the problem or is it
more likely to be in my view construction?
Sql is:
CREATE TABLE [dbo].[bb_chat_200309] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bb_chat_200310] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[bb_chat_200309] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200309] PRIMARY KEY
CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200309)
GO
ALTER TABLE [dbo].[bb_chat_200310] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200310] PRIMARY KEY
CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200310)
GO
create view dbo.bb_chat as
select
chatID
, yearMonth
, originatedBy
, originatedDate
, terminatedBy
, terminatedDate
from bb_chat_200405
union all
select
chatID
, yearMonth
, originatedBy
, originatedDate
, terminatedBy
, terminatedDate
from bb_chat_200404
Thanks for any help
GlynTry specifying the check constraint as part of the CREATE TABLE. For
example:
CREATE TABLE [dbo].[bb_chat_200309] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL
CONSTRAINT CK_bb_chat_200309_yearmonth CHECK ([yearMonth] = '200309') ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Glyn Walters" <glyn_walters@.hotmail.com> wrote in message
news:f9b601c43e65$9cba4a90$a601280a@.phx.gbl...
> Hi
> I am trying to get a partition view to work. The
> partition view is made up of tables that as far as I can
> tell qualify for having a partion column. Each tables is
> identical except for the check constraint.
> If I try to update a row I get an error that partioning
> column was not found.
> Updateable partition views only work on the enterprise
> edition as I understand it. I am trying this out on the
> evaluation edition. Could this be the problem or is it
> more likely to be in my view construction?
> Sql is:
> CREATE TABLE [dbo].[bb_chat_200309] (
> [chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
> NULL ,
> [yearMonth] [varchar] (6) NOT NULL ,
> [originatedBy] [varchar] (12) NULL ,
> [originatedDate] [datetime] NULL ,
> [terminatedBy] [varchar] (12) NULL ,
> [terminatedDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[bb_chat_200310] (
> [chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
> NULL ,
> [yearMonth] [varchar] (6) NOT NULL ,
> [originatedBy] [varchar] (12) NULL ,
> [originatedDate] [datetime] NULL ,
> [terminatedBy] [varchar] (12) NULL ,
> [terminatedDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[bb_chat_200309] WITH NOCHECK ADD
> CONSTRAINT [pk_bb_chat_200309] PRIMARY KEY
> CLUSTERED
> (
> [chatID],
> [yearMonth]
> ) ON [PRIMARY] ,
> CHECK ([yearMonth] = 200309)
> GO
> ALTER TABLE [dbo].[bb_chat_200310] WITH NOCHECK ADD
> CONSTRAINT [pk_bb_chat_200310] PRIMARY KEY
> CLUSTERED
> (
> [chatID],
> [yearMonth]
> ) ON [PRIMARY] ,
> CHECK ([yearMonth] = 200310)
> GO
> create view dbo.bb_chat as
> select
> chatID
> , yearMonth
> , originatedBy
> , originatedDate
> , terminatedBy
> , terminatedDate
> from bb_chat_200405
> union all
> select
> chatID
> , yearMonth
> , originatedBy
> , originatedDate
> , terminatedBy
> , terminatedDate
> from bb_chat_200404
> Thanks for any help
> Glyn|||Hi. Thanks for the advice. Howvere I do create the tables
like that. The output above was from exporting sql. I
create the tables using this script:
set @.sql = 'create table dbo.bb_chatUser_' + @.YYYYMM + '(
chatID numeric(18, 0) not null
, yearMonth varchar(6) not null constraint
ck_bb_chatUser_' + @.YYYYMM + '_yearmonth check (yearMonth
= ' + @.YYYYMM + ')
, invitedBy varchar (12) not null
, invitedDate datetime not null
, guestID varchar(12) not null
, answeredDate datetime null
, leftDate datetime
)'
exec(@.sql);
I think my tables and view qualify as yearMonth has a non-
overlapping check constraint and is part of the primary
key. Are there any other things I need to do to make the
view updateable?|||drop and re-create the constraints, there is an article by MS
http://support.microsoft.com/default.aspx?scid=kb;en-us;270013|||Thanks for the advice. If was my mistake in the end the
check constraints were yearMonth = 200310 rather
than '200310'.sql

Problems with partition view because it is evaluation version?

Hi
I am trying to get a partition view to work. The
partition view is made up of tables that as far as I can
tell qualify for having a partion column. Each tables is
identical except for the check constraint.
If I try to update a row I get an error that partioning
column was not found.
Updateable partition views only work on the enterprise
edition as I understand it. I am trying this out on the
evaluation edition. Could this be the problem or is it
more likely to be in my view construction?
Sql is:
CREATE TABLE [dbo].[bb_chat_200309] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bb_chat_200310] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[bb_chat_200309] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200309] PRIMARY KEY
CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200309)
GO
ALTER TABLE [dbo].[bb_chat_200310] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200310] PRIMARY KEY
CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200310)
GO
create view dbo.bb_chat as
select
chatID
,yearMonth
,originatedBy
,originatedDate
,terminatedBy
,terminatedDate
from bb_chat_200405
union all
select
chatID
,yearMonth
,originatedBy
,originatedDate
,terminatedBy
,terminatedDate
from bb_chat_200404
Thanks for any help
Glyn
Try specifying the check constraint as part of the CREATE TABLE. For
example:
CREATE TABLE [dbo].[bb_chat_200309] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL
CONSTRAINT CK_bb_chat_200309_yearmonth CHECK ([yearMonth] = '200309') ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Glyn Walters" <glyn_walters@.hotmail.com> wrote in message
news:f9b601c43e65$9cba4a90$a601280a@.phx.gbl...
> Hi
> I am trying to get a partition view to work. The
> partition view is made up of tables that as far as I can
> tell qualify for having a partion column. Each tables is
> identical except for the check constraint.
> If I try to update a row I get an error that partioning
> column was not found.
> Updateable partition views only work on the enterprise
> edition as I understand it. I am trying this out on the
> evaluation edition. Could this be the problem or is it
> more likely to be in my view construction?
> Sql is:
> CREATE TABLE [dbo].[bb_chat_200309] (
> [chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
> NULL ,
> [yearMonth] [varchar] (6) NOT NULL ,
> [originatedBy] [varchar] (12) NULL ,
> [originatedDate] [datetime] NULL ,
> [terminatedBy] [varchar] (12) NULL ,
> [terminatedDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[bb_chat_200310] (
> [chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
> NULL ,
> [yearMonth] [varchar] (6) NOT NULL ,
> [originatedBy] [varchar] (12) NULL ,
> [originatedDate] [datetime] NULL ,
> [terminatedBy] [varchar] (12) NULL ,
> [terminatedDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[bb_chat_200309] WITH NOCHECK ADD
> CONSTRAINT [pk_bb_chat_200309] PRIMARY KEY
> CLUSTERED
> (
> [chatID],
> [yearMonth]
> ) ON [PRIMARY] ,
> CHECK ([yearMonth] = 200309)
> GO
> ALTER TABLE [dbo].[bb_chat_200310] WITH NOCHECK ADD
> CONSTRAINT [pk_bb_chat_200310] PRIMARY KEY
> CLUSTERED
> (
> [chatID],
> [yearMonth]
> ) ON [PRIMARY] ,
> CHECK ([yearMonth] = 200310)
> GO
> create view dbo.bb_chat as
> select
> chatID
> , yearMonth
> , originatedBy
> , originatedDate
> , terminatedBy
> , terminatedDate
> from bb_chat_200405
> union all
> select
> chatID
> , yearMonth
> , originatedBy
> , originatedDate
> , terminatedBy
> , terminatedDate
> from bb_chat_200404
> Thanks for any help
> Glyn
|||Hi. Thanks for the advice. Howvere I do create the tables
like that. The output above was from exporting sql. I
create the tables using this script:
set @.sql = 'create table dbo.bb_chatUser_' + @.YYYYMM + '(
chatID numeric(18, 0) not null
,yearMonth varchar(6) not null constraint
ck_bb_chatUser_' + @.YYYYMM + '_yearmonth check (yearMonth
= ' + @.YYYYMM + ')
,invitedBy varchar (12) not null
,invitedDate datetime not null
,guestID varchar(12) not null
,answeredDate datetime null
,leftDate datetime
)'
exec(@.sql);
I think my tables and view qualify as yearMonth has a non-
overlapping check constraint and is part of the primary
key. Are there any other things I need to do to make the
view updateable?
|||drop and re-create the constraints, there is an article by MS
http://support.microsoft.com/default...b;en-us;270013
|||Thanks for the advice. If was my mistake in the end the
check constraints were yearMonth = 200310 rather
than '200310'.

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!