InfoPath XML importing to SQL 2000

Hi all,

We are having issues in getting a Bulk Upload for an XML which has been created through InfoPath 2007 (and it being accessed on SharePoint 2007).

We have found examples on how to do it with a normal XML but when it is created through InfoPath the schema does not seem to be the same as standard XML and therefore cannot work out the naming convention for importing the scripts.

If anyone has had any experience or suggestions all would be greatly appreciated.

Thanks in advance.

Hello,

If you send me the schema and the data file, I will take a look.

Regards,

Monica Frintu

InfoPath XML importing to SQL 2000

Hi all,

We are having issues in getting a Bulk Upload for an XML which has been created through InfoPath 2007 (and it being accessed on SharePoint 2007).

We have found examples on how to do it with a normal XML but when it is created through InfoPath the schema does not seem to be the same as standard XML and therefore cannot work out the naming convention for importing the scripts.

If anyone has had any experience or suggestions all would be greatly appreciated.

Thanks in advance.

Hello,

If you send me the schema and the data file, I will take a look.

Regards,

Monica Frintu

Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griffso there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/defaul...kb;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
(Please reply only to the newsgroup)

Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griffso there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
--
(Please reply only to the newsgroup)

Indexing problem

Dear all
I have a table which contains ~ 11 million rows in it.
I drop the indexes, bulk insert the data into it and then rebuild the
indexes.
Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
100 CPU on both processors for ~ 10 minutes and then the whole server
re-boots There are 3 indexes to be built, it crashes whilst building the
third.
However, if I run the three steps one at a time, the server survives.
I have backed up the database and copied it to an identical server and
loaded the same data in and again that server reboots. However, if I copy
the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
pattern. I can't get my PC to crash...
Any ideas?
Griff
so there are really two question.
1. How to optimize an index build.
2. why is the server crashing. Anything in the logs (os or SQL). Any more
details?
Unqueestionably.. .THAT is a bug. Now, it might be a MS bug, or it might be
a hardware bug. If it's a MS bug and you open a call to MS product support
the call will be free. If it's a hardware bug (perhaps your server IO isn't
on the HCL?) then you'd end up eating the cost...
You might want to take a look at SQLIO from MS. I don't have the URL, but
it's an IO stress tool. It simulates SQL Server IO... it will mostly likely
cause the server to crash if in fact SQL IO stress is causing the server to
crash under an index build...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oofq5o0PEHA.3420@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
>
|||Griff,
Are you running Enterprise Edition or Standard Edition? Enterprise
Edition supports Parallel index creation. Maybe you hit a bug, and are
not running Enterprise Edition on your PC.
Needless to say you need to install the latest service packs. Some bugs
are fixed...
http://support.microsoft.com/default...b;EN-US;279295
Gert-Jan
GriffithsJ wrote:
> Dear all
> I have a table which contains ~ 11 million rows in it.
> I drop the indexes, bulk insert the data into it and then rebuild the
> indexes.
> Whilst building the indexes, the server (dual xeon 2.8 GHz) maxes out at
> 100 CPU on both processors for ~ 10 minutes and then the whole server
> re-boots There are 3 indexes to be built, it crashes whilst building the
> third.
> However, if I run the three steps one at a time, the server survives.
> I have backed up the database and copied it to an identical server and
> loaded the same data in and again that server reboots. However, if I copy
> the DB and the data to my PC (single 2.6 GHz p4 processor) it works fine.
> It also doesn't flat line at 100% cpu but instead the CPU follows a cyclic
> pattern. I can't get my PC to crash...
> Any ideas?
> Griff
(Please reply only to the newsgroup)

Indexes slowing down BULK INSERT

I've been doing some experiments with speeding up copying tables of
approximately 1 million rows between databases using BCP and BULK INSERT.

I noticed that the total time for removing the indexes (non-clustered) and
then recreating them after the BULK INSERT was significantly less than just
doing the BULK INSERT with the indexes left there, even though I specified
TABLOCK.

I would have expected SQL Server not to update the index until the insert
completed (given the table lock) and so removing the indexes would have no
effect. Can anyone explain why removing the indexes should speed it up?

This is on SQL Server 7.

Cheers
DaveDavid Sharp (no email address supplied) writes:
> I've been doing some experiments with speeding up copying tables of
> approximately 1 million rows between databases using BCP and BULK INSERT.
> I noticed that the total time for removing the indexes (non-clustered)
> and then recreating them after the BULK INSERT was significantly less
> than just doing the BULK INSERT with the indexes left there, even though
> I specified TABLOCK.
> I would have expected SQL Server not to update the index until the insert
> completed (given the table lock) and so removing the indexes would have no
> effect. Can anyone explain why removing the indexes should speed it up?

I have not studied this case very closely. But a few observations: if
you supplied a batch size with /b, SQL Server had no choice but to
maintain the indexes while loading, since each batch is committed
separately.

When running some bulk-loading recently, I notice that when loading on
a completely unindexed table, BCP reported the copied rows swiftly, and
then completely directly, whereas on indexed tables there was a delay
from when all rows had been loaded until the command had completed,
which I supposed was spent on rebuilding indexes. I did not use TABLOCK.

I should add that I was working on SQL 2000, and the behaviour I saw
may reflect an improvement from SQL7.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns941B1079D8D3Yazorman@.127.0.0.1...
> David Sharp (no email address supplied) writes:
> > I've been doing some experiments with speeding up copying tables of
> > approximately 1 million rows between databases using BCP and BULK
INSERT.
> > I noticed that the total time for removing the indexes (non-clustered)
> > and then recreating them after the BULK INSERT was significantly less
> > than just doing the BULK INSERT with the indexes left there, even though
> > I specified TABLOCK.
> > I would have expected SQL Server not to update the index until the
insert
> > completed (given the table lock) and so removing the indexes would have
no
> > effect. Can anyone explain why removing the indexes should speed it up?
> I have not studied this case very closely. But a few observations: if
> you supplied a batch size with /b, SQL Server had no choice but to
> maintain the indexes while loading, since each batch is committed
> separately.

I have studied this somewhat closely. :-)

And what Erland says about the /b is a critical part of it. We on a
quarterly basis have to load a multimillion set of rows. As an experiment
recently (to reconfirm my thoughts) I did a test load on a backup server.
It took I believe 3 days to do the load. This was without removing the
non-clustered indices first.

For the actual load I I removed the non-clustered indices (but kept the
clustered index since the data is BCP'd out of another table already in
order). This took well under 12 hours. (Actually not 100% sure how long it
took since I started it around midnight and the scripts finished sometime
before 9:00 AM). This included re-applying the indices to the table.

I don't know how much of a difference there would be if the data had been
completely unordered.

> When running some bulk-loading recently, I notice that when loading on
> a completely unindexed table, BCP reported the copied rows swiftly, and
> then completely directly, whereas on indexed tables there was a delay
> from when all rows had been loaded until the command had completed,
> which I supposed was spent on rebuilding indexes. I did not use TABLOCK.
> I should add that I was working on SQL 2000, and the behaviour I saw
> may reflect an improvement from SQL7.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Indexes on Bulk Insert data

Any help would be appreciated.

I am running a script that does the following in succession.

1-Drop existing database and create new database
2-Defines tables, stored procedures and functions in the database
3-Imports data using bulk insert
4-Analyzes data using stored procedures

I would like to improve the performance of the analysis in step 4 by
creating indexes in step 2.

Question 1-Are indexes updated when data is bulk inserted? I know they are
when using normal insert, update, or delete T-SQL but I am not sure about
bulk insert of data.

Question 2-Do I need to update the index statistics in any way or would they
be ready to use in step 4.

Thanks,
CJI would define step 4 as create indexes, that will have your stats up to
date and save you from any performance issues during the load or having
to reindex or update the stats

I would do the analysis in step five (depending what type of analysis)

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Chris (chris@.hrn.org) writes:
> 1-Drop existing database and create new database
> 2-Defines tables, stored procedures and functions in the database
> 3-Imports data using bulk insert
> 4-Analyzes data using stored procedures
> I would like to improve the performance of the analysis in step 4 by
> creating indexes in step 2.
> Question 1-Are indexes updated when data is bulk inserted? I know they are
> when using normal insert, update, or delete T-SQL but I am not sure about
> bulk insert of data.

Yes, they are. However, you may prefer to wait with creating indexes until
you have loaded the data for best performance. You may also opt to create
clustered indexes before bulk-loading and add non-clustered indexes after.
This is particularly appealing if the order in the data files corre-
sponds to the clustered indexes.

> Question 2-Do I need to update the index statistics in any way or would
> they be ready to use in step 4.

If you create indexes after bulk-loading, SQL Server will create statistics
for you when creating the indexes.

If you create indexex before bulk-loading, the statistics will not be
correct after the load. Thus, it can be a good idea run UPDATE STATISTICS
in this situation. However, if you don't, SQL Server will auto-update
statistics, unless you have turned off this feature.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp