build sql script: rows

Showing posts with label rows. Show all posts

Tuesday, March 27, 2012

Bulk load multiple rows

Hi
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType> </xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when havin
g
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel Nhttp://msdn.microsoft.com/library/d... />
sqlxml.asp|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .

Bulk load multiple rows

Hi
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType></xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when having
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel N
http://msdn.microsoft.com/library/de...exchsqlxml.asp
|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .

Bulk load doesn't preserve element order?

Hi,
I have an interesing issue with sqlxml bulk load. It's about the order in which bulk load inserts rows in the database. I assumed that the rows should be inserted in the same order as the elements appear in the xml file, but looks like it is not always the case.
We use SQL server 2000 and SQLXML 3.0 SP 3. The files are large(2GB) and look something like this:
<Betalingskrav>
<Krav>
<BetalingsKravStatus><![CDATA[1]]></BetalingsKravStatus>
<FakturaDato><![CDATA[04.02.2006]]></FakturaDato>
<CustomerAgreementReference>
<![CDATA[12345689]]></CustomerAgreementReference>
....
<Samtaler>
<Samtale_Gruppeheader>
<SamtaleType><![CDATA[Mobilsamtaler]]></SamtaleType>
<Benevning><![CDATA[Abonnentnr]]></Benevning>
<Telefonnr><![CDATA[12 34 56 78]]></Telefonnr>
<Periode><![CDATA[Periode03.01.06-02.02.06]]></Periode>
<Etikett_Dest><![CDATA[Destinasjon/Operator]]></Etikett_Dest>
<Etikett_Oppr_nr><![CDATA[Oppringt nummer]]></Etikett_Oppr_nr>
<Etikett_SamtDato><![CDATA[Dato]]></Etikett_SamtDato>
<Etikett_Samtstart><![CDATA[Start]]></Etikett_Samtstart>
<Etikett_lngd><![CDATA[Samtalelengde]]></Etikett_lngd>
<Etikett_Kost><![CDATA[Kostnad]]></Etikett_Kost>
</Samtale_Gruppeheader>
<Samtale_Data>
<Dta_Dest><![CDATA[Tele2 GSM]]></Dta_Dest>
<Dta_Oppr_nr><![CDATA[12345678]]></Dta_Oppr_nr>
<Dta_SamtDato><![CDATA[02.01.06]]></Dta_SamtDato>
<Dta_Samtsstart><![CDATA[11:13]]></Dta_Samtsstart>
<Dta_lngd><![CDATA[ 0:00:37]]></Dta_lngd>
<Dta_Kost><![CDATA[0.81]]></Dta_Kost>
</Samtale_Data>
<Samtale_Data>
....
</Samtale_Data>
...
</Samtaler>
</Krav>
...
</Betalingskrav>

The schema (i list a shortened version):
<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="Krav_T2KravLinje"
parent="Betalingskrav"
parent-key="BetalingskravID"
child="T2BetalingskravLinje"
child-key="BetalingskravID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="KravType">
<xsd:all>
<xsd:element name="BetalerID" default="9999" />
<xsd:element name="FakturaTypeID" default="0" />
<xsd:element name="MalID" default="0" />
<xsd:element name="BuntID" default="0" />
<xsd:element name="UtstederID" default="28" />
...
<xsd:element name="Samtaler" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Samtale_Gruppeheader" type="Samtale_GruppeheaderType"
sql:relation="T2BetalingskravLinje" sql:relationship="Krav_T2KravLinje" />
<xsd:element name="Samtale_Data" type="Samtale_DataType"
sql:relation="T2BetalingskravLinje" sql:relationship="Krav_T2KravLinje"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="MVATekst1" type="xsd:string" />
<xsd:element name="MVATekst2" type="xsd:string" />
...
</xsd:all>
</xsd:complexType>
<xsd:complexType name="Samtale_GruppeheaderType">
<xsd:sequence>
<xsd:element name="SamtaleType" type="xsd:string" />
<xsd:element name="Benevning" type="xsd:string" />
<xsd:element name="Telefonnr" type="xsd:string" />
<xsd:element name="Periode" type="xsd:string" />
<xsd:element name="Etikett_Dest" type="xsd:string" />
<xsd:element name="Etikett_Oppr_nr" type="xsd:string" />
<xsd:element name="Etikett_Samtant" type="xsd:string" />
<xsd:element name="Etikett_SamtDato" type="xsd:string" />
<xsd:element name="Etikett_Samtstart" type="xsd:string" />
<xsd:element name="Etikett_lngd" type="xsd:string" />
<xsd:element name="Etikett_Kost" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Samtale_DataType">
<xsd:sequence>
<xsd:element name="Dta_Dest" type="xsd:string" />
<xsd:element name="Dta_Oppr_nr" type="xsd:string" />
<xsd:element name="Dta_Samtant" type="xsd:string" />
<xsd:element name="Dta_SamtDato" type="xsd:string" />
<xsd:element name="Dta_Samtsstart" type="xsd:string" />
<xsd:element name="Dta_lngd" type="xsd:string" />
<xsd:element name="Dta_Kost" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:element name="Betalingskrav" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Krav" type="KravType" sql:relation="Betalingskrav" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
XML data represents telephone bills with a listing of all calls, sms's, data services and so on. Samtale_Gruppheader elements define headers, and Samtale_Data elements contain the items that go below the headings. The point is that the sequence of elements inside <Samtaler> must be preserved in the database via identity column to provide correct formatting of bills. Not a good approach, I understand, but we are dealing with a legacy system that is not easy to modify...
The shema above maps xml data to two tables in the database that have primary-foreign relationship. The foreign-key table(T2BetalingskravLinje) contains both contents of Samtale_Gruppeheader and Samtale_Data elements. When the bills are rendered, rows in this table are shown in the sequence given by the identity column, in other words in the order they were inserted into the table.
KeepIdentity property is set to false on the bulk load component, the SQL server generates identity keys itself. This works fine, foreign keys are propagated correctly. But the order of rows is not the same as the order of elements in <Samtaler>! Not all the times, but in about 5 to 10% of the cases and I don't see any pattern here. I extracted a <krav> element that had this problem from a real file, and tried to bulk load the resulting short file. The effect disappered, rows were in correct order...
So the question is: Is this behaiviour normal for bulk load component and the order of row insertion into foreign-key tables is not guaranteed to be the same as the order of elements in the xml file? Is it a bug or a feature? :-) Is there any way to enable order preservation?
Thanks in advance for any ideas or suggestions!

The bulkloading component on which the SQLXML bulkloader is built does not guarantee order. So the behaviour is normal.

Best regards

Michael

sql

bulk insertion

hi i have common problem.

which is the best way to insert 1000 rows at a time in sql server 2000.

From where you want to insert?

You can use the following approaches

apporach 1:

Use bcp to backup the data on text file (delimited file) - from soruce server

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Use BULK INSERT to reload the data on the target server

http://msdn2.microsoft.com/en-us/library/ms188365.aspx

approach 2:

Use DTS to load the data from one server to another server

http://msdn2.microsoft.com/en-us/library/aa902640(SQL.80).aspx

approach 3:

Use linked server to get the source data on the target server

http://msdn2.microsoft.com/en-us/library/aa213778(SQL.80).aspx

|||

If the data is in a delimited text file, use bcp; it's quick and flexible.

Read up on [bcp] in Books Online.

Sunday, March 25, 2012

BULK INSERT: high disk queues (SQL Server 2000 SP4)

Hi,
I am using BUK INSERT to fill a table that usually contains about 100
mio. rows with about 10000 new rows per batch. This usually works fine
and takes about 1-2 seconds. Such bulk insert operation is executed
about every five seconds.
The problem is, that after about every 2 minutes one bulk exec will
take about 75 seconds, causing enourmous disk queues. After that
everything is fine again - for about two minutes.
There are some other strange facts:
1) this behaviour did only show up after the installation of service
pack 4;
2) this behaviour usually vanished after a couple of days with
continuous bulk inserts and would then only show up again after a sql
server server restart (but currently I have one server that shows the
problem since three weeks already);
3) I have this behaviour on both of our production databases but on one
server it simply disappeared (but will probably be there again after a
server restart) and on the other server it won't go away now.
I am quiet desperate with this situation and would be most thankful for
any hint.
Regards
DCIs this slowdown and high disk queues associated with a checkpoint?
Checkthe Sql perfmon counter for Checkpoint Pages Per second.
--
Andrew J. Kelly SQL MVP
<dc@.upsize.de> wrote in message
news:1147686058.831935.28980@.v46g2000cwv.googlegroups.com...
> Hi,
> I am using BUK INSERT to fill a table that usually contains about 100
> mio. rows with about 10000 new rows per batch. This usually works fine
> and takes about 1-2 seconds. Such bulk insert operation is executed
> about every five seconds.
> The problem is, that after about every 2 minutes one bulk exec will
> take about 75 seconds, causing enourmous disk queues. After that
> everything is fine again - for about two minutes.
> There are some other strange facts:
> 1) this behaviour did only show up after the installation of service
> pack 4;
> 2) this behaviour usually vanished after a couple of days with
> continuous bulk inserts and would then only show up again after a sql
> server server restart (but currently I have one server that shows the
> problem since three weeks already);
> 3) I have this behaviour on both of our production databases but on one
> server it simply disappeared (but will probably be there again after a
> server restart) and on the other server it won't go away now.
> I am quiet desperate with this situation and would be most thankful for
> any hint.
> Regards
> DC
>|||Dear Andrew,
I added the counter and yes: the very moment when the disk queues
start, the number of checkpoints per seconds jumps right up from about
0-2 per second to a number of 500-1000 checkpoints per second. I don't
know what that means - but sure hope you have another clue for me.
Regards
DC|||May I add to this: the secondary server, which is identical (physical
and configuration-wise) to the queuing server and also receives the
exact same bulk inserts, does only show a few checkpoint pages per
second and only every few minutes for only two or three seconds.|||Are both databases using the same recovery model ..?
SELECT DATABASEPROPERTYEX('MyDatabase', 'Recovery')
Also, is the recovery interval the same for both databases, sp_configure
--
HTH. Ryan
<dc@.upsize.de> wrote in message
news:1147700793.823997.289510@.g10g2000cwb.googlegroups.com...
> May I add to this: the secondary server, which is identical (physical
> and configuration-wise) to the queuing server and also receives the
> exact same bulk inserts, does only show a few checkpoint pages per
> second and only every few minutes for only two or three seconds.
>|||Both servers use the simple recovery model.
sp_configure output is also identical on both server, recovery interval
(min) is set to the default 0.|||In simple recovery mode CHECKPOINT happens automatically when the log
reaches 70% full. Are the log files the same size ..?
dbcc sqlperf(logspace)
HTH. Ryan
<dc@.upsize.de> wrote in message
news:1147703050.779437.211430@.u72g2000cwu.googlegroups.com...
> Both servers use the simple recovery model.
> sp_configure output is also identical on both server, recovery interval
> (min) is set to the default 0.
>|||Both machines are configured to use up to 50 GB of logspace (on three
different harddrives; harddrive size and raid type is the same on both
boxes).
The server with the massive queuing currently utilizes only 500 MB of
transaction log size (and displays that 20% of that is being used),
while the server which is working fine utilizes 37 GB of log files (and
reports that 97% of that is being used).|||> The server with the massive queuing currently utilizes only 500 MB of
> transaction log size (and displays that 20% of that is being used),
> while the server which is working fine utilizes 37 GB of log files (and
> reports that 97% of that is being used).
Seems that the physical file size for the log files are not the same, and you are seeing a side
effect of having a "too small" log file. SQL Server does a checkpoint when the log file is 70% full,
which turn out to be very frequently on the system with the smaller file. Grow the file and you will
probably see a difference.
Good catch by Ryan, btw...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<dc@.upsize.de> wrote in message news:1147706961.661923.196630@.u72g2000cwu.googlegroups.com...
> Both machines are configured to use up to 50 GB of logspace (on three
> different harddrives; harddrive size and raid type is the same on both
> boxes).
> The server with the massive queuing currently utilizes only 500 MB of
> transaction log size (and displays that 20% of that is being used),
> while the server which is working fine utilizes 37 GB of log files (and
> reports that 97% of that is being used).
>|||How can I force the log file to grow? I thought it would grow
automatically on demand. Can I for example set the log file to a fixed
size?
BTW: I have been watching dbcc sqlperf(logspace) on the server with the
massive checkpoints and queueing. The log file utilization was growing
from around 10 to 20 percent when the queueing started - it definitely
did not hit 70%.|||If you do enough changes the recovery interval can force a checkpoint before
the 70% fullness is reached. You can change the size of the log file with
alter database command or thru Enterprise Manager. I would make both of them
the same size so you can rule that part out. Just because the machines seem
identical they may not be. One thing that affects how long a checkpoint
takes is the size and configuration of the cache on the disk controllers or
the SAN. IS the cache the same on both servers? Do you have write back
cache enabled and if so what is the read to write ratio?
--
Andrew J. Kelly SQL MVP
<dc@.upsize.de> wrote in message
news:1147726750.397611.19450@.i39g2000cwa.googlegroups.com...
> How can I force the log file to grow? I thought it would grow
> automatically on demand. Can I for example set the log file to a fixed
> size?
> BTW: I have been watching dbcc sqlperf(logspace) on the server with the
> massive checkpoints and queueing. The log file utilization was growing
> from around 10 to 20 percent when the queueing started - it definitely
> did not hit 70%.
>|||A slightly different approach would be to take control of the CHECKPOINT
yourself.
Try a different batch size (say 1000) and issue a CHECKPOINT after each
batch, that way the disk controller should receive a steady trickle of data
rather than being swamped
--
HTH. Ryan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ePFexUGeGHA.2188@.TK2MSFTNGP05.phx.gbl...
> If you do enough changes the recovery interval can force a checkpoint
> before the 70% fullness is reached. You can change the size of the log
> file with alter database command or thru Enterprise Manager. I would make
> both of them the same size so you can rule that part out. Just because the
> machines seem identical they may not be. One thing that affects how long a
> checkpoint takes is the size and configuration of the cache on the disk
> controllers or the SAN. IS the cache the same on both servers? Do you
> have write back cache enabled and if so what is the read to write ratio?
> --
> Andrew J. Kelly SQL MVP
>
> <dc@.upsize.de> wrote in message
> news:1147726750.397611.19450@.i39g2000cwa.googlegroups.com...
>> How can I force the log file to grow? I thought it would grow
>> automatically on demand. Can I for example set the log file to a fixed
>> size?
>> BTW: I have been watching dbcc sqlperf(logspace) on the server with the
>> massive checkpoints and queueing. The log file utilization was growing
>> from around 10 to 20 percent when the queueing started - it definitely
>> did not hit 70%.
>|||Coincidentally this morning the queueing on the problematic server has
disappeared. Without obvious reason, after three weeks of problems.
However, I am sure that the problems will reappear once I restart the
server, since that has happened in the past.
When I manually launch a CHECKPOINT command on the server, it will
queue for about five minutes. And when I launch another checkpoint just
a few seconds later, it will again cause the disks to spin for a couple
of minutes.
dbcc sqlperf(logspace) now shows, that the database on the problematic
server is using 20 GB of log space. Maybe
ALTER DATABASE MyDB
MODIFY FILE
(NAME = MyLogfile,
SIZE = 40GB)
will help to avoid the automatic checkpoints once I have to restart one
of the servers.
I will also try to install the post SP4 hotfixes next time. I think
that these bulk insert related queues / checkpoints are a buggy sql
server behaviour since it did not appear before SP4.|||Today the problematic server started unwanted checkpoints and queueing
again. Which is strange, since in the past the servers were fine once
the queueing ceased - until the server had to be restarted.
I increased the logfile size with ALTER DATABASE - no change. I
installed the post-SP4 hotfixed - still no success.
The only countermeasure that had an effect is
sp_configure "recovery_interval", 60
RECONFIGURE WITH OVERRIDE
but this will still result in longer (about 3 minutes) queueing in
bigger intervals (~20 minutes).
Can I somehow find out what exactly is causing the checkpoint? Can I
see the checkpoint time and reason somewhere in the system tables? The
only difference between the two servers that I can see now, is that the
problematic server uses only a few percent of logspace (never hitting
70%), while the OK server always uses > 95% of the log space.
It is too bad that I cannot use non-logged or minimally-logged BULK
INSERTS (I cannot use TABLOCK because the table is also permanently
being queryied) since I believe that would make a difference in my
scenario.
I am wondering if SQL Server 2005 will perform better in my scenario
with permanent bulk inserts and also massive updating and simultaneous
querying. Data integrity is really not that important for my
application, but speed is. I wish I could deactivate all logging.|||Are you sure they are all in the same recovery model? In any case I really
think you are going down the wrong road. The bottom line is that if
checkpoints cause too much pain and last too long you do not have a proper
disk subsystem or configuration to handle the load.IS the log file on a RAID
1 or RAID 10 with no other types of files? What is the array configuration
for the data? How much and what is the configuration for the cache on the
controller or SAN processor?
--
Andrew J. Kelly SQL MVP
<dc@.upsize.de> wrote in message
news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
> Today the problematic server started unwanted checkpoints and queueing
> again. Which is strange, since in the past the servers were fine once
> the queueing ceased - until the server had to be restarted.
> I increased the logfile size with ALTER DATABASE - no change. I
> installed the post-SP4 hotfixed - still no success.
> The only countermeasure that had an effect is
> sp_configure "recovery_interval", 60
> RECONFIGURE WITH OVERRIDE
> but this will still result in longer (about 3 minutes) queueing in
> bigger intervals (~20 minutes).
> Can I somehow find out what exactly is causing the checkpoint? Can I
> see the checkpoint time and reason somewhere in the system tables? The
> only difference between the two servers that I can see now, is that the
> problematic server uses only a few percent of logspace (never hitting
> 70%), while the OK server always uses > 95% of the log space.
> It is too bad that I cannot use non-logged or minimally-logged BULK
> INSERTS (I cannot use TABLOCK because the table is also permanently
> being queryied) since I believe that would make a difference in my
> scenario.
> I am wondering if SQL Server 2005 will perform better in my scenario
> with permanent bulk inserts and also massive updating and simultaneous
> querying. Data integrity is really not that important for my
> application, but speed is. I wish I could deactivate all logging.
>|||====================================="Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#jJQM#teGHA.4828@.TK2MSFTNGP05.phx.gbl...
> Are you sure they are all in the same recovery model? In any case I
really
> think you are going down the wrong road. The bottom line is that if
> checkpoints cause too much pain and last too long you do not have a proper
> disk subsystem or configuration to handle the load.IS the log file on a
RAID
> 1 or RAID 10 with no other types of files? What is the array
configuration
> for the data? How much and what is the configuration for the cache on the
> controller or SAN processor?
> --
> Andrew J. Kelly SQL MVP
>
> <dc@.upsize.de> wrote in message
> news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
> > Today the problematic server started unwanted checkpoints and queueing
> > again. Which is strange, since in the past the servers were fine once
> > the queueing ceased - until the server had to be restarted.
> >
> > I increased the logfile size with ALTER DATABASE - no change. I
> > installed the post-SP4 hotfixed - still no success.
> >
> > The only countermeasure that had an effect is
> >
> > sp_configure "recovery_interval", 60
> >
> > RECONFIGURE WITH OVERRIDE
> >
> > but this will still result in longer (about 3 minutes) queueing in
> > bigger intervals (~20 minutes).
> >
> > Can I somehow find out what exactly is causing the checkpoint? Can I
> > see the checkpoint time and reason somewhere in the system tables? The
> > only difference between the two servers that I can see now, is that the
> > problematic server uses only a few percent of logspace (never hitting
> > 70%), while the OK server always uses > 95% of the log space.
> >
> > It is too bad that I cannot use non-logged or minimally-logged BULK
> > INSERTS (I cannot use TABLOCK because the table is also permanently
> > being queryied) since I believe that would make a difference in my
> > scenario.
> >
> > I am wondering if SQL Server 2005 will perform better in my scenario
> > with permanent bulk inserts and also massive updating and simultaneous
> > querying. Data integrity is really not that important for my
> > application, but speed is. I wish I could deactivate all logging.
> >
>|||I am ruling out disk performance, since the problem occurs on two
servers with identical hardware and configuration (including recovery
model) and it only occurs after the servers have been restarted. And on
one of the servers the problem seems to have manifested since it is
still there even weeks after restarting.
I never had this "too many checkpoints" problem before SP4. I am
thinking about going back to SP3. However, if I could somehow diagnose
why the checkpoints are occuring that would help. As mentioned before,
the situation got better with changing the "recovery_interval" to 300.
However, if I change the interval to 3000 or 30000 that does not seem
to make much of a difference, still a checkpoint will occur in 20-30
minute intervals.|||I'm not sure if this posted since it's been over 10 minutes and I got a blank
page after clicking the post button, so please ignore if my response is
already there:
Just out of curiosity, is there any reason why you're using the Simple
Recovery Model on a Production server that also receives a majority of their
inserts through Bulk-Insert? Why aren't you using the Bulk-Logged Recovery
Model which is designed for this particular situation? Each time you do a
Bulk-Insert of any magnitude, it is being logged, which will cause your
transaction logs to grow because each row that's being inserted is being
recorded, whereas using the Bulk-Logged Recovery will only record that you're
doing a Bulk-Insert, which will be less writes to your transaction log, less
auto-growth, and possibly less automatic checkpoints. I would follow the
suggestion of doing a manual checkpoint before each Bulk-Insert for piece of
mind though. Hopefully this helps.
Laurence
"Andrew J. Kelly" wrote:
> Are you sure they are all in the same recovery model? In any case I really
> think you are going down the wrong road. The bottom line is that if
> checkpoints cause too much pain and last too long you do not have a proper
> disk subsystem or configuration to handle the load.IS the log file on a RAID
> 1 or RAID 10 with no other types of files? What is the array configuration
> for the data? How much and what is the configuration for the cache on the
> controller or SAN processor?
> --
> Andrew J. Kelly SQL MVP
>
> <dc@.upsize.de> wrote in message
> news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
> > Today the problematic server started unwanted checkpoints and queueing
> > again. Which is strange, since in the past the servers were fine once
> > the queueing ceased - until the server had to be restarted.
> >
> > I increased the logfile size with ALTER DATABASE - no change. I
> > installed the post-SP4 hotfixed - still no success.
> >
> > The only countermeasure that had an effect is
> >
> > sp_configure "recovery_interval", 60
> >
> > RECONFIGURE WITH OVERRIDE
> >
> > but this will still result in longer (about 3 minutes) queueing in
> > bigger intervals (~20 minutes).
> >
> > Can I somehow find out what exactly is causing the checkpoint? Can I
> > see the checkpoint time and reason somewhere in the system tables? The
> > only difference between the two servers that I can see now, is that the
> > problematic server uses only a few percent of logspace (never hitting
> > 70%), while the OK server always uses > 95% of the log space.
> >
> > It is too bad that I cannot use non-logged or minimally-logged BULK
> > INSERTS (I cannot use TABLOCK because the table is also permanently
> > being queryied) since I believe that would make a difference in my
> > scenario.
> >
> > I am wondering if SQL Server 2005 will perform better in my scenario
> > with permanent bulk inserts and also massive updating and simultaneous
> > querying. Data integrity is really not that important for my
> > application, but speed is. I wish I could deactivate all logging.
> >
>
>|||It is always OK for us to go back to the last backup. I thought that in
that scenario "simple" is the least demanding recovery model in terms
of logging. My understanding is that "bulk logged" mimics "simple" for
the bulk operations, but "full" for other operations. Since one of our
tables also receives a massive amount of updates, "bulk logged" mode
should result in more log file action.
However, out of desperation I switched to "bulk logged" on the
problematic server. That did not fix the problem, there is still a
checkpoint about every two minutes. I am currently working around by
setting the recovery_interval to 32767, which will still issue a
checkpoint or two per day and then block the server for about five
minutes (I wonder why the checkpoint does not take 32767 minutes since
that is what I thought the recovery_interval option indicates). I am
quiet sure that it will not take my database 32767 minutes to recover
after restarting the server but I am not willing to try.
I think that Sql Server is not calculating the time for a checkpoint
correctly under some circumstances with SP4 (don't know what exactly
the circumstances are since the other server is still operating
perfectly). I believe that the rows inserted with a bulk insert should
not trigger a checkpoint as quickly as they currently do, in other
words (sorry for my naive assumption) Sql Server takes to bulk inserted
rows for a reason to make a checkpoint appear.
Laurence schrieb:
> I'm not sure if this posted since it's been over 10 minutes and I got a blank
> page after clicking the post button, so please ignore if my response is
> already there:
> Just out of curiosity, is there any reason why you're using the Simple
> Recovery Model on a Production server that also receives a majority of their
> inserts through Bulk-Insert? Why aren't you using the Bulk-Logged Recovery
> Model which is designed for this particular situation? Each time you do a
> Bulk-Insert of any magnitude, it is being logged, which will cause your
> transaction logs to grow because each row that's being inserted is being
> recorded, whereas using the Bulk-Logged Recovery will only record that you're
> doing a Bulk-Insert, which will be less writes to your transaction log, less
> auto-growth, and possibly less automatic checkpoints. I would follow the
> suggestion of doing a manual checkpoint before each Bulk-Insert for piece of
> mind though. Hopefully this helps.
> Laurence
> "Andrew J. Kelly" wrote:
> > Are you sure they are all in the same recovery model? In any case I really
> > think you are going down the wrong road. The bottom line is that if
> > checkpoints cause too much pain and last too long you do not have a proper
> > disk subsystem or configuration to handle the load.IS the log file on a RAID
> > 1 or RAID 10 with no other types of files? What is the array configuration
> > for the data? How much and what is the configuration for the cache on the
> > controller or SAN processor?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > <dc@.upsize.de> wrote in message
> > news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
> > > Today the problematic server started unwanted checkpoints and queueing
> > > again. Which is strange, since in the past the servers were fine once
> > > the queueing ceased - until the server had to be restarted.
> > >
> > > I increased the logfile size with ALTER DATABASE - no change. I
> > > installed the post-SP4 hotfixed - still no success.
> > >
> > > The only countermeasure that had an effect is
> > >
> > > sp_configure "recovery_interval", 60
> > >
> > > RECONFIGURE WITH OVERRIDE
> > >
> > > but this will still result in longer (about 3 minutes) queueing in
> > > bigger intervals (~20 minutes).
> > >
> > > Can I somehow find out what exactly is causing the checkpoint? Can I
> > > see the checkpoint time and reason somewhere in the system tables? The
> > > only difference between the two servers that I can see now, is that the
> > > problematic server uses only a few percent of logspace (never hitting
> > > 70%), while the OK server always uses > 95% of the log space.
> > >
> > > It is too bad that I cannot use non-logged or minimally-logged BULK
> > > INSERTS (I cannot use TABLOCK because the table is also permanently
> > > being queryied) since I believe that would make a difference in my
> > > scenario.
> > >
> > > I am wondering if SQL Server 2005 will perform better in my scenario
> > > with permanent bulk inserts and also massive updating and simultaneous
> > > querying. Data integrity is really not that important for my
> > > application, but speed is. I wish I could deactivate all logging.
> > >
> >
> >
> >|||Hi
Don't forget, a checkpoint is a flush of dirty buffers to disk. This flush
will take a lot less time than the recovery interval setting. The recovery
interval setting is based on a log replay, not a simple flush to disk.
The amount of available RAM also play a role in the checkpoint interval
(e.g. if your DB is 20GB, and you have 2GB buffer RAM, if every page was
modified, ideally at least 10 checkpoints have to occur). If you are in
simple recovery mode, 70% log full generates a checkpoint.
Making sure that your disk subsystem is optimal is the answer to checkpoint
performance problems. Andrew listed the points to look for.
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<dc@.upsize.de> wrote in message
news:1149591061.614095.295260@.f6g2000cwb.googlegroups.com...
> It is always OK for us to go back to the last backup. I thought that in
> that scenario "simple" is the least demanding recovery model in terms
> of logging. My understanding is that "bulk logged" mimics "simple" for
> the bulk operations, but "full" for other operations. Since one of our
> tables also receives a massive amount of updates, "bulk logged" mode
> should result in more log file action.
> However, out of desperation I switched to "bulk logged" on the
> problematic server. That did not fix the problem, there is still a
> checkpoint about every two minutes. I am currently working around by
> setting the recovery_interval to 32767, which will still issue a
> checkpoint or two per day and then block the server for about five
> minutes (I wonder why the checkpoint does not take 32767 minutes since
> that is what I thought the recovery_interval option indicates). I am
> quiet sure that it will not take my database 32767 minutes to recover
> after restarting the server but I am not willing to try.
> I think that Sql Server is not calculating the time for a checkpoint
> correctly under some circumstances with SP4 (don't know what exactly
> the circumstances are since the other server is still operating
> perfectly). I believe that the rows inserted with a bulk insert should
> not trigger a checkpoint as quickly as they currently do, in other
> words (sorry for my naive assumption) Sql Server takes to bulk inserted
> rows for a reason to make a checkpoint appear.
>
> Laurence schrieb:
>> I'm not sure if this posted since it's been over 10 minutes and I got a
>> blank
>> page after clicking the post button, so please ignore if my response is
>> already there:
>> Just out of curiosity, is there any reason why you're using the Simple
>> Recovery Model on a Production server that also receives a majority of
>> their
>> inserts through Bulk-Insert? Why aren't you using the Bulk-Logged
>> Recovery
>> Model which is designed for this particular situation? Each time you do
>> a
>> Bulk-Insert of any magnitude, it is being logged, which will cause your
>> transaction logs to grow because each row that's being inserted is being
>> recorded, whereas using the Bulk-Logged Recovery will only record that
>> you're
>> doing a Bulk-Insert, which will be less writes to your transaction log,
>> less
>> auto-growth, and possibly less automatic checkpoints. I would follow the
>> suggestion of doing a manual checkpoint before each Bulk-Insert for piece
>> of
>> mind though. Hopefully this helps.
>> Laurence
>> "Andrew J. Kelly" wrote:
>> > Are you sure they are all in the same recovery model? In any case I
>> > really
>> > think you are going down the wrong road. The bottom line is that if
>> > checkpoints cause too much pain and last too long you do not have a
>> > proper
>> > disk subsystem or configuration to handle the load.IS the log file on a
>> > RAID
>> > 1 or RAID 10 with no other types of files? What is the array
>> > configuration
>> > for the data? How much and what is the configuration for the cache on
>> > the
>> > controller or SAN processor?
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > <dc@.upsize.de> wrote in message
>> > news:1147959206.497801.43880@.j55g2000cwa.googlegroups.com...
>> > > Today the problematic server started unwanted checkpoints and
>> > > queueing
>> > > again. Which is strange, since in the past the servers were fine once
>> > > the queueing ceased - until the server had to be restarted.
>> > >
>> > > I increased the logfile size with ALTER DATABASE - no change. I
>> > > installed the post-SP4 hotfixed - still no success.
>> > >
>> > > The only countermeasure that had an effect is
>> > >
>> > > sp_configure "recovery_interval", 60
>> > >
>> > > RECONFIGURE WITH OVERRIDE
>> > >
>> > > but this will still result in longer (about 3 minutes) queueing in
>> > > bigger intervals (~20 minutes).
>> > >
>> > > Can I somehow find out what exactly is causing the checkpoint? Can I
>> > > see the checkpoint time and reason somewhere in the system tables?
>> > > The
>> > > only difference between the two servers that I can see now, is that
>> > > the
>> > > problematic server uses only a few percent of logspace (never hitting
>> > > 70%), while the OK server always uses > 95% of the log space.
>> > >
>> > > It is too bad that I cannot use non-logged or minimally-logged BULK
>> > > INSERTS (I cannot use TABLOCK because the table is also permanently
>> > > being queryied) since I believe that would make a difference in my
>> > > scenario.
>> > >
>> > > I am wondering if SQL Server 2005 will perform better in my scenario
>> > > with permanent bulk inserts and also massive updating and
>> > > simultaneous
>> > > querying. Data integrity is really not that important for my
>> > > application, but speed is. I wish I could deactivate all logging.
>> > >
>> >
>> >
>> >
>|||rahul sharma
rahul.sharma822@.gmail.com
*** Sent via Developersdex http://www.developersdex.com ***

Bulk insert, skip rows with duplicate key error?

Does sql server have a way to handle errors in a sproc which would allow
one to insert rows, ignoring rows which would create a duplicate key
violation? I know if one loops one can handle the error on a row by row
basis. But is there a way to skip the loop and do it as a bulk insert?
It's easy to do in Access, but I'm curious to know if SQL Server proper
can handle like this. I am guessing that a looping operation would be
slower to execute?nano (nano@.nano.ono) writes:

Quote:

Originally Posted by

I'm a little uncertain what you are talking about. In SQL Server "bulk
insert" is a special operation where you load many rows direct from a
file. Or are you still talking about regular SQL statements?

In the latter case, use

INSERT tbl
SELECT ...
FROM src
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE tbl.keycol = src.keycol)

which should be the normal way to do it in Access - or any other SQL engine
for that matter - as well.

If you are specifically talking bulk load from file, then above is still
possible in SQL 2005 if you use OPENROWSET(BULK) as the table source. If
you use BULK INSERT or BCP (the only options on SQL 2000), I believe it's
possible by using the IGNORE_DUP_KEY option on the index, but a more
common procedure is to load the file to staging table and move on from
there.

--
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|||Thanks Erland. I meant a regular sql operation. I will take a look at
your suggestion, it looks good. Access has another way of handling this
(non-sql) and while the syntax you suggest probably works in Access,
I've never tried it.

Bulk Insert ZERO rows returned...

I have poured thru notes, sites, etc. and can't seem to figure what I might be doing wrong - which most likely is something very easy.

BULK INSERT

dbo.tbCheckpoint

FROM 'c:\inetpub\wwwroot\upload\maxie_task_aging.csv'

WITH

( DATAFILETYPE = 'char', FIELDTERMINATOR = ', ',

FIRSTROW = 3, KEEPNULLS, ROWTERMINATOR = '\t\n')

I am doing a bulk insert where I am pulling from .csv but the msg I get when running it is

(0 row(s) affected)

There is data in the file, it starts on the 3rd row and terminators are set correctly.

Any thoughts?

Moving to the transact-SQL forum.|||Have you checked to see if the .csv file ends with a carriage return/line feed? I have seen cases where the insert would fail because the csv, as a whole, was not terminated properly. It's definitely worth a "look see", so to speak. If the file is missing a carriage return, add it, then try the import again.|||Thanks! That was it.

BULK INSERT with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dkJesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.

> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
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.mspxsql

BULK INSERT with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
Jesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.

> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

BULK INSERT with leading/trailing quotes

I am using BULK INSERT to load data from some CSV-file into SQL-server 7.0.
The files use TAB to seperate fields and NEWLINE to seperate rows.
However - we would like to be able to have these characters in the fields
themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
the users.
The standard way to do this is to enclose the fields in e.g. double quotes
'"' and escape any double quotes within these fields, so that the CSV-row
JESPER STOC"HOLM
becomes
"JESPER" "STOC""HOLM"
However, BULK INSERT doesn't seem to support this.
Can you guys confirm this?
I have looked at the option to use a FORMATFILE from BCP, but is seems that
these files are table-specific, meaning that as we need to upload to 315+
tables, we need 315+ format files.
Is it possible to create a standard FORMAT file that only contains
information about the trailing/leading double quotes and how to interpret
the escaped quotes? This file should be used for all tables.
Before I dig even further down into the doc (BOL), can you guys also
confirm this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dkJesper Stocholm (j@.stocholm.invalid) writes:
> However - we would like to be able to have these characters in the fields
> themselves, e.g. a TAB or NEWLINE in a field which contain free-text from
> the users.
> The standard way to do this is to enclose the fields in e.g. double quotes
> '"' and escape any double quotes within these fields, so that the CSV-row
> JESPER STOC"HOLM
> becomes
> "JESPER" "STOC""HOLM"
> However, BULK INSERT doesn't seem to support this.
Yes and no. You can sort it out with a format file, STOC""HOLM would
remain STOC""HOLM.
> I have looked at the option to use a FORMATFILE from BCP, but is seems
> that these files are table-specific, meaning that as we need to upload
> to 315+ tables, we need 315+ format files.
That is correct. There is no built in generic mechanism to handle what
you ask for. You would have to write a program that generates the format
files. Or a program that reads the import file, and then feeds it to BCP.
You may want to look at DTS and the import wizard, and see what it offers.
I have not used DTS myself, though, so I am not able to answer any questions
for it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thursday, March 22, 2012

BULK INSERT stop inserting rows

All,

I am just having this weird issue recently with the BULK INSERT command on one of our SQL2K servers which always returns the message "command(s) completed successfully" (Query Analyzer) but no rows get actually inserted into the bulk table.

BULK INSERT LOADTMP FROM 'D:\TEMP\MYFILE.CSV' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', MAXERRORS = 20)

> The command(s) completed successfully.

The same BULK INSERT command used to work for the past year or so and still works just fine on the other servers but for some unknown reasons, it just stopped working on this one box while always returning the message: "the command(s) completed successfully"...

I did try to provide some bogus non-existent filename in the BULK INSERT command and it would also return "the command(s) completed successfully" as well - as long as the entire BULK INSERT command syntax and arguments are correct !!!

I thought that I found some kind of solution in the msdn knowledge database related to the issue by re-applying the SQL2K service pack 4 but it didn't fix it. Rebooting the instance and the server several times did not help either.

Have any of you had encountered similar issues before and how to resolve it? Would appreciate all you inputs...

Thanks,
JohnSince you have "MAXERRORS = 20" in your statement, it returns success unless 21 errors occur.

Try setting maxerrors = 1 and see what message you get.

Bulk insert skips rows

Hi Guys,

My little bulk insert is only bringing every second row of a CSV file. this is not good as i need every row.

My SQLcommand is thus.

InsertCommand="BULK INSERT TBL_Unitel_services FROM 'C:/webroot/servicedesk/csvs_Services/csv.csv' WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', MAXERRORS = 0) "

Every thing looks fine, just change this

ROWTERMINATOR = '\n'

ROWTERMINATOR = '\r\n'

Let me know if you still have issues or Mark the thread as answered if it answers your issue.

|||

no joy.

I am sure its becasue i have a computed column at the end of the table. If i remove it all the rows are entered, with it only half come in GAHHHH!!

sql

bulk insert question? Another way?

hi guys,
I have a text file contains about 1 million rows, I tried to import to a
table but I received data conversion errors for some rows, however, the row
number is like 504500 for example, I want to troubleshoot this row but the
only way I know is to open the text file (300MB) which will take forever to
open on notepad, I'm not sure if there is an easy way to troubleshoot such
a problem. Another thing is, I set Batchsize=10000, so I guess if I
received errors for 20 rows, it would mean that 200000 rows (20 *10000)
didn't get inserted into table, correct?
ThanksKevin,
Get a copy of TextPad or one of the many shareware or freeware text editors
that can handle large files with ease.
Another alternative is to import the file into a one-column staging table on
your SQL Server, and then scrub the data before inserting it into the target
table.
Steve Kass
Drew University
kevin wrote:

>hi guys,
> I have a text file contains about 1 million rows, I tried to import to
a
>table but I received data conversion errors for some rows, however, the ro
w
>number is like 504500 for example, I want to troubleshoot this row but the
>only way I know is to open the text file (300MB) which will take forever to
>open on notepad, I'm not sure if there is an easy way to troubleshoot such
>a problem. Another thing is, I set Batchsize=10000, so I guess if I
>received errors for 20 rows, it would mean that 200000 rows (20 *10000)
>didn't get inserted into table, correct?
>Thanks
>
>
>

Bulk Insert Question

I have a simple table I have created to do a bulk insert with a SP. Everything works fine but when the sp runs, the first column in the rows are like

"0243567

My bulk insert

BULK INSERT [Mas500_app].[dbo].[Test] FROM 'C:\Documents and Settings\Chris\Desktop\testbulk1.txt' WITH (FIELDTERMINATOR = '"~"', ROWTERMINATOR = '"')

the testbulk.txt file

"0243567"~"08/26/06"

How would I go about in my bulk statement to remove the " from the first column in the row.

ThanksWell there is no such options in bulk statement to remove the " from "0243567

the trailing " is removed becuase you have specified it as the field terminator

however you have two options
1) Change the txt file to remove the " from the first row data
2) issue the follwoing update statement immediately after the bulk insert statement

update [Mas500_app].[dbo].[Test]
set column1 = replace( column1 , " , '' )

HOwever the above replace statement is helpful only if your data in column1 does not contain any " else you will have to go for more elaborate procedures|||I think this might do what you want (it trims the first character off a field value).

UPDATE MyTable
SET MyField = SubString(MyField,2,Len(MyField))

HTH ~Georgesql

Tuesday, March 20, 2012

bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.
thanksIf you use the Native mode I don't think you can do it but have you actually
tried? BCP out a few thousand rows, create anew table and Bulk Insert it
back in. If native wont work I am pretty sure char mode will.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
> thanks
>|||I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
actually
> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
> back in. If native wont work I am pretty sure char mode will.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> > Hi! We are in the process of adding few columns to our large tables (200
> > million rows) and altering few columns from varchar to char. One of the
> > option I am thinking is to bcp data out, change the schema and bulk
insert
> > data in. Looks like with either bcp.exe or bulk insert command, you
won't
> > be
> > able to load the data in, if the schema of table get changed. Is this
> > true?
> > I know that I can use DTS export/import to do this task but since bulk
> > insert is the fastest method I would like to try that option if
possible.
> > Besides, Bulk insert I could also change schema with Alter table
command.
> > I
> > don't know if thats better than unloading/change/reload method that I
> > mentioned above.
> > I would appreiciate it, if anyone who have export/change schema/import
> > large
> > table, give me some direction here.
> >
> > thanks
> >
> >
>|||Yes if you change the columns around you need to use a format file. But I
don't see why it wont work with the format file.
--
Andrew J. Kelly SQL MVP
"james" <kush@.brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
>> If you use the Native mode I don't think you can do it but have you
> actually
>> tried? BCP out a few thousand rows, create anew table and Bulk Insert it
>> back in. If native wont work I am pretty sure char mode will.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "james" <kush@.brandes.com> wrote in message
>> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
>> > Hi! We are in the process of adding few columns to our large tables
>> > (200
>> > million rows) and altering few columns from varchar to char. One of the
>> > option I am thinking is to bcp data out, change the schema and bulk
> insert
>> > data in. Looks like with either bcp.exe or bulk insert command, you
> won't
>> > be
>> > able to load the data in, if the schema of table get changed. Is this
>> > true?
>> > I know that I can use DTS export/import to do this task but since bulk
>> > insert is the fastest method I would like to try that option if
> possible.
>> > Besides, Bulk insert I could also change schema with Alter table
> command.
>> > I
>> > don't know if thats better than unloading/change/reload method that I
>> > mentioned above.
>> > I would appreiciate it, if anyone who have export/change schema/import
>> > large
>> > table, give me some direction here.
>> >
>> > thanks
>> >
>> >
>>
>|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ov1hlWrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Yes if you change the columns around you need to use a format file. But I
> don't see why it wont work with the format file.
> --
> Andrew J. Kelly SQL MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >I have tried both native and character mode and both erroed out. Which is
> > kind of expected, since schema got changed, the program doesn't have any
> > way
> > of knowing which column in datafile (native or char) maps to which
column
> > in
> > table.
> > I haven't tried format file so far, which is what I am going to do next
> > and
> > see if I can alter format file and make this thing work.
> > Thanks for your Input.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:uWZ$MoLrFHA.2624@.TK2MSFTNGP15.phx.gbl...
> >> If you use the Native mode I don't think you can do it but have you
> > actually
> >> tried? BCP out a few thousand rows, create anew table and Bulk Insert
it
> >> back in. If native wont work I am pretty sure char mode will.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "james" <kush@.brandes.com> wrote in message
> >> news:%23j3fNPKrFHA.3640@.tk2msftngp13.phx.gbl...
> >> > Hi! We are in the process of adding few columns to our large tables
> >> > (200
> >> > million rows) and altering few columns from varchar to char. One of
the
> >> > option I am thinking is to bcp data out, change the schema and bulk
> > insert
> >> > data in. Looks like with either bcp.exe or bulk insert command, you
> > won't
> >> > be
> >> > able to load the data in, if the schema of table get changed. Is this
> >> > true?
> >> > I know that I can use DTS export/import to do this task but since
bulk
> >> > insert is the fastest method I would like to try that option if
> > possible.
> >> > Besides, Bulk insert I could also change schema with Alter table
> > command.
> >> > I
> >> > don't know if thats better than unloading/change/reload method that I
> >> > mentioned above.
> >> > I would appreiciate it, if anyone who have export/change
schema/import
> >> > large
> >> > table, give me some direction here.
> >> >
> >> > thanks
> >> >
> >> >
> >>
> >>
> >
> >
>

Bulk Insert Problem

I receive near about 50 text files every month each has near about 1lakh to 3
lakh rows. they come in various formats (few with , delimiters, few with
field qualifiers, few with mix and match of both, and few files with fixed
format string)
so i use bulk insert to copy them into sql server using format files.
Everything works fine so far.
the problem is, in those txt files some times few lines can be either too
long or too short. in that case bulk insert totally rejects the entire file.
That is i dont want the process to stop just because few lines were corrupt.
is there anyway through which i can insert the data by skipping those few
corrupt lines.
Thanks for any help in advance.
This is urgent
Kitty,
Have a look at the -m and -e switches for bcp. This won't solve your problem
though, you need to fix the source data.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Kitty" wrote:

> I receive near about 50 text files every month each has near about 1lakh to 3
> lakh rows. they come in various formats (few with , delimiters, few with
> field qualifiers, few with mix and match of both, and few files with fixed
> format string)
> so i use bulk insert to copy them into sql server using format files.
> Everything works fine so far.
> the problem is, in those txt files some times few lines can be either too
> long or too short. in that case bulk insert totally rejects the entire file.
> That is i dont want the process to stop just because few lines were corrupt.
> is there anyway through which i can insert the data by skipping those few
> corrupt lines.
> Thanks for any help in advance.
> This is urgent
|||Thanks for your time mark.
As you rightly said, this wont solve my problem. and i have no control over
source data, since it comes from various people from various countries all
over the world.
any more suggestions.
Thanks in advance
Kitty
"Mark Allison" wrote:
[vbcol=seagreen]
> Kitty,
> Have a look at the -m and -e switches for bcp. This won't solve your problem
> though, you need to fix the source data.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "Kitty" wrote:

Tuesday, March 27, 2012

Bulk load multiple rows

Bulk load multiple rows

Bulk load doesn't preserve element order?

bulk insertion

Sunday, March 25, 2012

BULK INSERT: high disk queues (SQL Server 2000 SP4)

BULK INSERT: high disk queues (SQL Server 2000 SP4)

Bulk insert, skip rows with duplicate key error?

Bulk Insert ZERO rows returned...

BULK INSERT with leading/trailing quotes

BULK INSERT with leading/trailing quotes

BULK INSERT with leading/trailing quotes

BULK INSERT with leading/trailing quotes

Thursday, March 22, 2012

BULK INSERT stop inserting rows

Bulk insert skips rows

bulk insert question? Another way?

Bulk Insert Question

Tuesday, March 20, 2012

bulk insert question

bulk insert question

bulk insert question

Bulk Insert Problem

build sql script

Blog Archive

About Me