build sql script: errors

Showing posts with label errors. Show all posts

Sunday, March 25, 2012

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 UTF8 File ERROR 4864 on first row first column

we try to import a UTF8 File (at first with SSIS BULK INSERT but now with SQL to track down the errors) and always face the same issue - the first row does not import with error "Msg 4864, Level 16, State 1, Line 10 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (PSGT_ID)."

The Statements see below. We need to use utf8 because thets the only we can get - with ansi oder unicode it works - so why not utf8? (Codepage 65001?)

--CREATE TABLE [BULKTEST](
-- [PSGT_ID] [numeric](38, 0) NOT NULL primary key,
-- [PSGT_PSG_ID] [numeric](38, 0) NOT NULL,
-- [PSGT_PSP_ID] [numeric](38, 0) NOT NULL,
-- [PSGT_KURZTEXTM] [nvarchar](5) COLLATE Latin1_General_CI_AS NULL,
-- [PSGT_TEXT] [nvarchar](40) COLLATE Latin1_General_CI_AS NULL, -
- [PSGT_AENDDATUM] [datetime] NOT NULL)

truncate table [BULKTEST]
BULK INSERT [BULKTEST] FROM 'C:\test.txt' WITH( CHECK_CONSTRAINTS, CODEPAGE='65001', DATAFILETYPE='char', FIELDTERMINATOR='|', ROWTERMINATOR='\n')
select * from [BULKTEST]

As test data you could use something like this

1|2|3|4|5|2006-01-01
2|2|3|4|5|2006-01-01
3|2|3|4|5|2006-01-01
4|2|3|4|5|2006-01-01

in notepad in a text file stored as UTF8!

We really need help on this!

Best regards, HANNES

Try using the DATAFILETYPE = 'widechar'. You can also try using a format file.|||

I have tried widechar and also a format file - but it stays the same.

Maybe we are a little bit further - could it by that notepad incorrectly adds some characters at the beginning of the file?

Like ∩╗┐ - if i display them on the comandline with type <filename>?

LG HANNES

|||Check this Blog http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx in this case.

Thursday, March 22, 2012

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
>
>
>

Thursday, March 8, 2012

Bulk insert errors

Hi.

I am trying following procedure:

I have table:

CREATE TABLE [dbo].[organiz] (
[cislo_subjektu] [int] NULL ,
[reference_subjektu] [varchar] (30) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_subjektu] [varchar] (100) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[nazev_zkraceny] [varchar] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ulice] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[psc] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[misto] [char] (40) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[ico] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[dic] [char] (15) COLLATE SQL_Czech_CP1250_CI_AS NULL ,
[uverovy_limit] [money] NULL ,
[stav_limitu] [money] NULL
) ON [PRIMARY]
GO

Format File:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="40" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="15" COLLATION="SQL_Czech_CP1250_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="cislo_subjektu" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="reference_subjektu" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="nazev_subjektu" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="nazev_zkraceny" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="ulice" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="6" NAME="psc" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="7" NAME="misto" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="8" NAME="ico" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="9" NAME="dic" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="10" NAME="uverovy_limit" xsi:type="SQLMONEY"/>
<COLUMN SOURCE="11" NAME="stav_limitu" xsi:type="SQLMONEY"/>
</ROW>
</BCPFORMAT>

And XML file located on drive.

When i try bulk insert:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\organizpok.xml' /* my format file */)

I get error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row ....

This error occurs with format file created by bcp. When i try to mess a little with format file, i can get to this error:
Bulk load data conversion error (truncation) for row ...

Anyone has experience with this?
SEe this http://www.thescripts.com/forum/thread520822.html is any help, good explanation by Erland.|||Hm, i did not find solution for my problem, or i am blind.
|||It has to look this way:

DECLARE @.X XML
SELECT @.X = X.C

FROM OPENROWSET(BULK

'D:\organizace.xml',

SINGLE_BLOB) AS X(C)
INSERT INTO pokus.dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('* Devil ', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('* Music ', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

Bulk insert errors

Hi.

I am trying following procedure:

I have table:

Format File:

And XML file located on drive.

When i try bulk insert:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\organizpok.xml' /* my format file */)

I get error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row ....

This error occurs with format file created by bcp. When i try to mess a little with format file, i can get to this error:
Bulk load data conversion error (truncation) for row ...

DECLARE @.X XML
SELECT @.X = X.C

FROM OPENROWSET(BULK

'D:\organizace.xml',

SINGLE_BLOB) AS X(C)
INSERT INTO pokus.dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('* Devil ', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('* Music ', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

BULK INSERT errors

I'm writing a .NET component that will bulk insert a file into a table.
I also have a string array inside the class whose purpose is to
capture the errors during the bulk insert process. Can this be done?If you are using the SqlBulkCopy class, an error will rollback the batch
containing the problem row and the WriteToServer operation will terminate.
In order to trap insert errors on individual rows yet still save good data,
you could use a hybrid technique in which you write a batch at a time using
WriteToServer and fall back on individual insert commands if an error is
encountered.
1) Read a batch of records from file (e.g. 1000)
2) Write records as a single batch via SqlBulkCopy.WriteToServer
3) If a SqlException is thrown, insert rows individually and trap errors
Note that you'll also need to address file parsing errors, such as an
unexpected number of fields in a CSV record.
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149281083.751510.288450@.y43g2000cwc.googlegroups.com...
> I'm writing a .NET component that will bulk insert a file into a table.
> I also have a string array inside the class whose purpose is to
> capture the errors during the bulk insert process. Can this be done?
>|||That's my biggest hurdle. I'm tied to using 1.1 Framework that doesn't
have the SqlBulkCopy class :(
I do plan on sending a batch parameter to control for that. Maybe
there's a property for the OleDbCommand that captures errors.
Dan Guzman wrote:
> If you are using the SqlBulkCopy class, an error will rollback the batch
> containing the problem row and the WriteToServer operation will terminate.
> In order to trap insert errors on individual rows yet still save good data
,
> you could use a hybrid technique in which you write a batch at a time usin
g
> WriteToServer and fall back on individual insert commands if an error is
> encountered.
> 1) Read a batch of records from file (e.g. 1000)
> 2) Write records as a single batch via SqlBulkCopy.WriteToServer
> 3) If a SqlException is thrown, insert rows individually and trap errors
> Note that you'll also need to address file parsing errors, such as an
> unexpected number of fields in a CSV record.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <tobinlim@.gmail.com> wrote in message
> news:1149281083.751510.288450@.y43g2000cwc.googlegroups.com...|||With OleDbCommand (and SqlCommand) about all you can do is try/catch. If
you send multiple statements in a batch, it's hard to identify which insert
caused the error.
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149365527.051997.242230@.u72g2000cwu.googlegroups.com...
> That's my biggest hurdle. I'm tied to using 1.1 Framework that doesn't
> have the SqlBulkCopy class :(
> I do plan on sending a batch parameter to control for that. Maybe
> there's a property for the OleDbCommand that captures errors.
> Dan Guzman wrote:
>|||What I meant is my batchsize is something that gets passed on to a BULK
INSERT statement: "BATCHSIZE = " + intBatchSize + ", ..."
Anyway, if what you're saying is true, I might have to force the
batchsize to 1 and bulk insert one row at a time and try to catch
errors.
Is this what you're saying?
Dan Guzman wrote:
> With OleDbCommand (and SqlCommand) about all you can do is try/catch. If
> you send multiple statements in a batch, it's hard to identify which inser
t
> caused the error.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <tobinlim@.gmail.com> wrote in message
> news:1149365527.051997.242230@.u72g2000cwu.googlegroups.com...|||Let me try to clarify. Some errors, like a primary key violation, will
abort the entire BULK INSERT batch. You'll need to resort to individual
inserts to identify the problem data and persist the good records. I
wouldn't recommend BULK INSERT with batch size of 1 because BULK INSERT is
optimized for large volumes of data. A batch size 1 will be much slower
than a regular inserts and defeat the purpose of BULK INSERT.
Other errors, such as a data type conversion error, will err the problem
record but not the batch. This will still throw a SqlException and you can
iterate over the Errors collection to get the details of the individual
errors. Unfortunately, it's not easy to programmatically distinguish
between errors that abort the BULK INSERT batch and those that err
individual records.
If your data is usually clean and you want the performance of BULK INSERT,
you might consider a modified version of the approach I suggested earlier:
1) BEGIN TRAN
2) BULK INSERT entire file
3) If no errors, COMMIT
4) If errors, ROLLBACK and use individual inserts to identify problem data
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149512589.648528.145350@.g10g2000cwb.googlegroups.com...
> What I meant is my batchsize is something that gets passed on to a BULK
> INSERT statement: "BATCHSIZE = " + intBatchSize + ", ..."
> Anyway, if what you're saying is true, I might have to force the
> batchsize to 1 and bulk insert one row at a time and try to catch
> errors.
> Is this what you're saying?
> Dan Guzman wrote:
>|||Thanks. I discovered the SqlException object in .NET. Very useful.
It's pretty much what I've been looking for. Now I noticed that if
multiple errors occur within a row insert, SqlException captures the
first error and moves on. I don't necessarily need to have all the
errors for each row. Just want to understand the nature of BULK INSERT
errors.
Dan Guzman wrote:
> Let me try to clarify. Some errors, like a primary key violation, will
> abort the entire BULK INSERT batch. You'll need to resort to individual
> inserts to identify the problem data and persist the good records. I
> wouldn't recommend BULK INSERT with batch size of 1 because BULK INSERT is
> optimized for large volumes of data. A batch size 1 will be much slower
> than a regular inserts and defeat the purpose of BULK INSERT.
> Other errors, such as a data type conversion error, will err the problem
> record but not the batch. This will still throw a SqlException and you ca
n
> iterate over the Errors collection to get the details of the individual
> errors. Unfortunately, it's not easy to programmatically distinguish
> between errors that abort the BULK INSERT batch and those that err
> individual records.
> If your data is usually clean and you want the performance of BULK INSERT,
> you might consider a modified version of the approach I suggested earlier:
> 1) BEGIN TRAN
> 2) BULK INSERT entire file
> 3) If no errors, COMMIT
> 4) If errors, ROLLBACK and use individual inserts to identify problem data
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <tobinlim@.gmail.com> wrote in message
> news:1149512589.648528.145350@.g10g2000cwb.googlegroups.com...|||> Thanks. I discovered the SqlException object in .NET. Very useful.
> It's pretty much what I've been looking for. Now I noticed that if
> multiple errors occur within a row insert, SqlException captures the
> first error and moves on.
Have you looked at SqlException.Errors? This contains a collection of
SqlError objects and contains details of row-level errors in my test.
However, as I mentioned earlier, you might find it problematic to
distinguish between errors that roll back the entire batch and errors that
err individual records.
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149605426.090781.127220@.j55g2000cwa.googlegroups.com...
> Thanks. I discovered the SqlException object in .NET. Very useful.
> It's pretty much what I've been looking for. Now I noticed that if
> multiple errors occur within a row insert, SqlException captures the
> first error and moves on. I don't necessarily need to have all the
> errors for each row. Just want to understand the nature of BULK INSERT
> errors.
>
> Dan Guzman wrote:
>

BULK Insert Errors

I'm having a little trouble with a Bulk Insert. This is my first time
working with this, so I'm not sure what I'm missing here. I've read
everything I can find about Bulk Insert in BOL and from querying the
archive, but I'm not finding the answer.
This is the error I'm getting when attempting to Bulk Insert the data into a
table in a local MSDE DB named GACT:
Server: Msg 4865, Level 16, State 1, Line 1
Could not bulk insert because the maximum number of errors (10) was
exceeded.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows return
ed
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
This is the Table def:
CREATE TABLE [GACT2] (
[id] [uniqueidentifier] NOT NULL ,
[Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
) ON [PRIMARY]
GO
These are a couple of lines from the Import file:
14|3J6|897000|117236710|B|||20070103|200
70103|20070104|000000000080000000|+|
000000000044800000|+|CITIGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
15|3J6|897000|117236710|B|||20070103|200
70103|20070104|000000000080000000|+|
000000000044800000|+|CITIGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
And this is my Bulk Insert Query:
Delete from GACT2
GO
BULK INSERT GACT.dbo.GACT2
FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
KEEPNULLS,
KEEPIDENTITY
)
TIA
MattYour problem is with the first field - you are trying to push what appears
to be an int value (14) into a unique identifier column. So your data is
incompatible with the table. You have to get valid data or change your
schema or pipe it to a holding table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OlnRNS0MHHA.3268@.TK2MSFTNGP04.phx.gbl...
> I'm having a little trouble with a Bulk Insert. This is my first time
> working with this, so I'm not sure what I'm missing here. I've read
> everything I can find about Bulk Insert in BOL and from querying the
> archive, but I'm not finding the answer.
> This is the error I'm getting when attempting to Bulk Insert the data into
> a table in a local MSDE DB named GACT:
> Server: Msg 4865, Level 16, State 1, Line 1
> Could not bulk insert because the maximum number of errors (10) was
> exceeded.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows retu
rned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
> This is the Table def:
> CREATE TABLE [GACT2] (
> [id] [uniqueidentifier] NOT NULL ,
> [Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ) ON [PRIMARY]
> GO
> These are a couple of lines from the Import file:
> 14|3J6|897000|117236710|B|||20070103|200
70103|20070104|000000000080000000|
+|000000000044800000|+|CITIGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> 15|3J6|897000|117236710|B|||20070103|200
70103|20070104|000000000080000000|
+|000000000044800000|+|CITIGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> And this is my Bulk Insert Query:
> Delete from GACT2
> GO
> BULK INSERT GACT.dbo.GACT2
> FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> KEEPIDENTITY
> )
> TIA
> Matt
>|||> Your problem is with the first field - you are trying to push what appears
> to be an int value (14) into a unique identifier column. So your data is
> incompatible with the table. You have to get valid data or change your
> schema or pipe it to a holding table.
That's not an issue. I'm creating the output file myself, so I can remove
that field if I need to. What would be the best way to have a unique ID
column? Do I need to leave it null in my import file and let SQL generate
it? Or should I just change the table def for that column to INT(14)
instead?
TIA
Matt|||>> Your problem is with the first field - you are trying to push what
> That's not an issue. I'm creating the output file myself, so I can remove
> that field if I need to. What would be the best way to have a unique ID
> column? Do I need to leave it null in my import file and let SQL generate
> it? Or should I just change the table def for that column to INT(14)
> instead?
I did some testing and ended up changing that field in my table to INT and
now I get past that error. Now I'm getting a new error that says
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 15
(Description).
I have the description field defined as [Description] [varchar] (240
)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
The description field in my file is always 240 chars. It CAN'T be any bigger
because that's the number of bytes I'm extracting for that field and writing
to the file. I don't get it. Changing it to [TEXT] helps, but it only
imports up to that field for one row and stops.|||I found it odd that you're using \n alone as a rowterminator. It *looks* to
me like your data may naturally contain carriage returns and/or line feeds,
unless that was a bad copy & paste, so I'd make sure that you use a proper
delimiter that can't appear in your data.
A
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OY3ovA2MHHA.992@.TK2MSFTNGP04.phx.gbl...
> I did some testing and ended up changing that field in my table to INT and
> now I get past that error. Now I'm getting a new error that says
> Server: Msg 4863, Level 16, State 1, Line 1
> Bulk insert data conversion error (truncation) for row 1, column 15
> (Description).
> I have the description field defined as [Description] [varchar] (2
40)
> COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> The description field in my file is always 240 chars. It CAN'T be any
> bigger because that's the number of bytes I'm extracting for that field
> and writing to the file. I don't get it. Changing it to [TEXT] helps,
but
> it only imports up to that field for one row and stops.
>|||>I found it odd that you're using \n alone as a rowterminator. It *looks*
>to me like your data may naturally contain carriage returns and/or line
>feeds, unless that was a bad copy & paste, so I'd make sure that you use a
>proper delimiter that can't appear in your data.
The lines are pretty long, so the copy of it wrapped. Each row of data is on
one line in my file and is terminated with 0D 0A Hex. Is that \n or \r\n?
I've tried both. I also tried adding just a ! at the end of the row and
using "|!" as the terminator, but it didn't change anything either.|||> >I found it odd that you're using \n alone as a rowterminator. It *looks*
> The lines are pretty long, so the copy of it wrapped. Each row of data is
> on one line in my file and is terminated with 0D 0A Hex. Is that \n or
> \r\n? I've tried both. I also tried adding just a ! at the end of the row
> and using "|!" as the terminator, but it didn't change anything either.
Nevermind. It was a standard 1d10t error. I changed the path to where I was
generating the output file in my code, but forgot to change the Bulk Insert
query in QA to reflect that. It works perfectly now.
Thanks for anyone that posted.

BULK Insert Errors

I'm having a little trouble with a Bulk Insert. This is my first time
working with this, so I'm not sure what I'm missing here. I've read
everything I can find about Bulk Insert in BOL and from querying the
archive, but I'm not finding the answer.
This is the error I'm getting when attempting to Bulk Insert the data into a
table in a local MSDE DB named GACT:
Server: Msg 4865, Level 16, State 1, Line 1
Could not bulk insert because the maximum number of errors (10) was
exceeded.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
This is the Table def:
CREATE TABLE [GACT2] (
[id] [uniqueidentifier] NOT NULL ,
[Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
These are a couple of lines from the Import file:
14|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
15|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
And this is my Bulk Insert Query:
Delete from GACT2
GO
BULK INSERT GACT.dbo.GACT2
FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
KEEPNULLS,
KEEPIDENTITY
)
TIA
MattYour problem is with the first field - you are trying to push what appears
to be an int value (14) into a unique identifier column. So your data is
incompatible with the table. You have to get valid data or change your
schema or pipe it to a holding table.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OlnRNS0MHHA.3268@.TK2MSFTNGP04.phx.gbl...
> I'm having a little trouble with a Bulk Insert. This is my first time
> working with this, so I'm not sure what I'm missing here. I've read
> everything I can find about Bulk Insert in BOL and from querying the
> archive, but I'm not finding the answer.
> This is the error I'm getting when attempting to Bulk Insert the data into
> a table in a local MSDE DB named GACT:
> Server: Msg 4865, Level 16, State 1, Line 1
> Could not bulk insert because the maximum number of errors (10) was
> exceeded.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
> This is the Table def:
> CREATE TABLE [GACT2] (
> [id] [uniqueidentifier] NOT NULL ,
> [Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> These are a couple of lines from the Import file:
> 14|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> 15|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> And this is my Bulk Insert Query:
> Delete from GACT2
> GO
> BULK INSERT GACT.dbo.GACT2
> FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> KEEPIDENTITY
> )
> TIA
> Matt
>|||> Your problem is with the first field - you are trying to push what appears
> to be an int value (14) into a unique identifier column. So your data is
> incompatible with the table. You have to get valid data or change your
> schema or pipe it to a holding table.
That's not an issue. I'm creating the output file myself, so I can remove
that field if I need to. What would be the best way to have a unique ID
column? Do I need to leave it null in my import file and let SQL generate
it? Or should I just change the table def for that column to INT(14)
instead?
TIA
Matt|||>> Your problem is with the first field - you are trying to push what
>> appears to be an int value (14) into a unique identifier column. So your
>> data is incompatible with the table. You have to get valid data or change
>> your schema or pipe it to a holding table.
> That's not an issue. I'm creating the output file myself, so I can remove
> that field if I need to. What would be the best way to have a unique ID
> column? Do I need to leave it null in my import file and let SQL generate
> it? Or should I just change the table def for that column to INT(14)
> instead?
I did some testing and ended up changing that field in my table to INT and
now I get past that error. Now I'm getting a new error that says
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 15
(Description).
I have the description field defined as [Description] [varchar] (240)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
The description field in my file is always 240 chars. It CAN'T be any bigger
because that's the number of bytes I'm extracting for that field and writing
to the file. I don't get it. Changing it to [TEXT] helps, but it only
imports up to that field for one row and stops.|||I found it odd that you're using \n alone as a rowterminator. It *looks* to
me like your data may naturally contain carriage returns and/or line feeds,
unless that was a bad copy & paste, so I'd make sure that you use a proper
delimiter that can't appear in your data.
A
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OY3ovA2MHHA.992@.TK2MSFTNGP04.phx.gbl...
>> Your problem is with the first field - you are trying to push what
>> appears to be an int value (14) into a unique identifier column. So your
>> data is incompatible with the table. You have to get valid data or
>> change your schema or pipe it to a holding table.
>> That's not an issue. I'm creating the output file myself, so I can remove
>> that field if I need to. What would be the best way to have a unique ID
>> column? Do I need to leave it null in my import file and let SQL generate
>> it? Or should I just change the table def for that column to INT(14)
>> instead?
> I did some testing and ended up changing that field in my table to INT and
> now I get past that error. Now I'm getting a new error that says
> Server: Msg 4863, Level 16, State 1, Line 1
> Bulk insert data conversion error (truncation) for row 1, column 15
> (Description).
> I have the description field defined as [Description] [varchar] (240)
> COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> The description field in my file is always 240 chars. It CAN'T be any
> bigger because that's the number of bytes I'm extracting for that field
> and writing to the file. I don't get it. Changing it to [TEXT] helps, but
> it only imports up to that field for one row and stops.
>|||>I found it odd that you're using \n alone as a rowterminator. It *looks*
>to me like your data may naturally contain carriage returns and/or line
>feeds, unless that was a bad copy & paste, so I'd make sure that you use a
>proper delimiter that can't appear in your data.
The lines are pretty long, so the copy of it wrapped. Each row of data is on
one line in my file and is terminated with 0D 0A Hex. Is that \n or \r\n?
I've tried both. I also tried adding just a ! at the end of the row and
using "|!" as the terminator, but it didn't change anything either.|||> >I found it odd that you're using \n alone as a rowterminator. It *looks*
> >to me like your data may naturally contain carriage returns and/or line
> >feeds, unless that was a bad copy & paste, so I'd make sure that you use
> >a proper delimiter that can't appear in your data.
> The lines are pretty long, so the copy of it wrapped. Each row of data is
> on one line in my file and is terminated with 0D 0A Hex. Is that \n or
> \r\n? I've tried both. I also tried adding just a ! at the end of the row
> and using "|!" as the terminator, but it didn't change anything either.
Nevermind. It was a standard 1d10t error. I changed the path to where I was
generating the output file in my code, but forgot to change the Bulk Insert
query in QA to reflect that. It works perfectly now.
Thanks for anyone that posted.

Bulk insert errors

Hi.

I am trying following procedure:

I have table:

Format File:

And XML file located on drive.

When i try bulk insert:

BULK INSERT pokus.dbo.organiz
FROM 'D:\organizace.xml' /* my file */
WITH (FORMATFILE = 'D:\organizpok.xml' /* my format file */)

I get error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row ....

This error occurs with format file created by bcp. When i try to mess a little with format file, i can get to this error:
Bulk load data conversion error (truncation) for row ...

DECLARE @.X XML
SELECT @.X = X.C

FROM OPENROWSET(BULK

'D:\organizace.xml',

SINGLE_BLOB) AS X(C)
INSERT INTO pokus.dbo.organiz

SELECT

C.value('(./cislo_subjektu/text())[1]', 'int') AS 'cislo_subjektu'

,C.value('(./reference_subjektu/text())[1]', 'varchar(30)') AS 'reference_subjektu'

,C.value('(./nazev_subjektu/text())[1]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('(./nazev_zkraceny/text())[1]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('(./ulice/text())[1]', 'char(40)') AS 'ulice'

,C.value('(./psc/text())[1]', 'char(15)') AS 'psc'

,C.value('(./misto/text())[1]', 'char(40)') AS 'misto'

,C.value('(./ico/text())[1]', 'char(15)') AS 'ico'

,C.value('(./dic/text())[1]', 'char(15)') AS 'dic'

,C.value('(./uverovy_limit/text())[1]', 'money') AS 'uverovy_limit'

,C.value('(./stav_limitu/text())[1]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

SELECT

C.value('*[1]', 'int') AS 'cislo_subjektu'

,C.value('*[2]', 'varchar(30)') AS 'reference_subjektu'

,C.value('*[3]', 'varchar(100)') AS 'nazev_subjektu'

,C.value('*[4]', 'varchar(40)') AS 'nazev_zkraceny'

,C.value('*[5]', 'char(40)') AS 'ulice'

,C.value('* Devil ', 'char(15)') AS 'psc'

,C.value('*[7]', 'char(40)') AS 'misto'

,C.value('* Music ', 'char(15)') AS 'ico'

,C.value('*[9]', 'char(15)') AS 'dic'

,C.value('*[10]', 'money') AS 'uverovy_limit'

,C.value('*[11]', 'money') AS 'stav_limitu'

FROM @.X.nodes('/root/organizace') T(C)

BULK Insert Errors

I'm having a little trouble with a Bulk Insert. This is my first time
working with this, so I'm not sure what I'm missing here. I've read
everything I can find about Bulk Insert in BOL and from querying the
archive, but I'm not finding the answer.
This is the error I'm getting when attempting to Bulk Insert the data into a
table in a local MSDE DB named GACT:
Server: Msg 4865, Level 16, State 1, Line 1
Could not bulk insert because the maximum number of errors (10) was
exceeded.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
This is the Table def:
CREATE TABLE [GACT2] (
[id] [uniqueidentifier] NOT NULL ,
[Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
These are a couple of lines from the Import file:
14|3J6|897000|117236710|B|||20070103|20070103|2007 0104|000000000080000000|+|000000000044800000|+|CIT IGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
15|3J6|897000|117236710|B|||20070103|20070103|2007 0104|000000000080000000|+|000000000044800000|+|CIT IGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
And this is my Bulk Insert Query:
Delete from GACT2
GO
BULK INSERT GACT.dbo.GACT2
FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
KEEPNULLS,
KEEPIDENTITY
)
TIA
Matt
Your problem is with the first field - you are trying to push what appears
to be an int value (14) into a unique identifier column. So your data is
incompatible with the table. You have to get valid data or change your
schema or pipe it to a holding table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OlnRNS0MHHA.3268@.TK2MSFTNGP04.phx.gbl...
> I'm having a little trouble with a Bulk Insert. This is my first time
> working with this, so I'm not sure what I'm missing here. I've read
> everything I can find about Bulk Insert in BOL and from querying the
> archive, but I'm not finding the answer.
> This is the error I'm getting when attempting to Bulk Insert the data into
> a table in a local MSDE DB named GACT:
> Server: Msg 4865, Level 16, State 1, Line 1
> Could not bulk insert because the maximum number of errors (10) was
> exceeded.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
> This is the Table def:
> CREATE TABLE [GACT2] (
> [id] [uniqueidentifier] NOT NULL ,
> [Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> These are a couple of lines from the Import file:
> 14|3J6|897000|117236710|B|||20070103|20070103|2007 0104|000000000080000000|+|000000000044800000|+|CIT IGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> 15|3J6|897000|117236710|B|||20070103|20070103|2007 0104|000000000080000000|+|000000000044800000|+|CIT IGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> And this is my Bulk Insert Query:
> Delete from GACT2
> GO
> BULK INSERT GACT.dbo.GACT2
> FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> KEEPIDENTITY
> )
> TIA
> Matt
>
|||> Your problem is with the first field - you are trying to push what appears
> to be an int value (14) into a unique identifier column. So your data is
> incompatible with the table. You have to get valid data or change your
> schema or pipe it to a holding table.
That's not an issue. I'm creating the output file myself, so I can remove
that field if I need to. What would be the best way to have a unique ID
column? Do I need to leave it null in my import file and let SQL generate
it? Or should I just change the table def for that column to INT(14)
instead?
TIA
Matt
|||>> Your problem is with the first field - you are trying to push what
> That's not an issue. I'm creating the output file myself, so I can remove
> that field if I need to. What would be the best way to have a unique ID
> column? Do I need to leave it null in my import file and let SQL generate
> it? Or should I just change the table def for that column to INT(14)
> instead?
I did some testing and ended up changing that field in my table to INT and
now I get past that error. Now I'm getting a new error that says
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 15
(Description).
I have the description field defined as [Description] [varchar] (240)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
The description field in my file is always 240 chars. It CAN'T be any bigger
because that's the number of bytes I'm extracting for that field and writing
to the file. I don't get it. Changing it to [TEXT] helps, but it only
imports up to that field for one row and stops.
|||I found it odd that you're using \n alone as a rowterminator. It *looks* to
me like your data may naturally contain carriage returns and/or line feeds,
unless that was a bad copy & paste, so I'd make sure that you use a proper
delimiter that can't appear in your data.
A
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OY3ovA2MHHA.992@.TK2MSFTNGP04.phx.gbl...
> I did some testing and ended up changing that field in my table to INT and
> now I get past that error. Now I'm getting a new error that says
> Server: Msg 4863, Level 16, State 1, Line 1
> Bulk insert data conversion error (truncation) for row 1, column 15
> (Description).
> I have the description field defined as [Description] [varchar] (240)
> COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> The description field in my file is always 240 chars. It CAN'T be any
> bigger because that's the number of bytes I'm extracting for that field
> and writing to the file. I don't get it. Changing it to [TEXT] helps, but
> it only imports up to that field for one row and stops.
>
|||>I found it odd that you're using \n alone as a rowterminator. It *looks*
>to me like your data may naturally contain carriage returns and/or line
>feeds, unless that was a bad copy & paste, so I'd make sure that you use a
>proper delimiter that can't appear in your data.
The lines are pretty long, so the copy of it wrapped. Each row of data is on
one line in my file and is terminated with 0D 0A Hex. Is that \n or \r\n?
I've tried both. I also tried adding just a ! at the end of the row and
using "|!" as the terminator, but it didn't change anything either.
|||> >I found it odd that you're using \n alone as a rowterminator. It *looks*
> The lines are pretty long, so the copy of it wrapped. Each row of data is
> on one line in my file and is terminated with 0D 0A Hex. Is that \n or
> \r\n? I've tried both. I also tried adding just a ! at the end of the row
> and using "|!" as the terminator, but it didn't change anything either.
Nevermind. It was a standard 1d10t error. I changed the path to where I was
generating the output file in my code, but forgot to change the Bulk Insert
query in QA to reflect that. It works perfectly now.
Thanks for anyone that posted.

BULK Insert Errors

I'm having a little trouble with a Bulk Insert. This is my first time
working with this, so I'm not sure what I'm missing here. I've read
everything I can find about Bulk Insert in BOL and from querying the
archive, but I'm not finding the answer.
This is the error I'm getting when attempting to Bulk Insert the data into a
table in a local MSDE DB named GACT:
Server: Msg 4865, Level 16, State 1, Line 1
Could not bulk insert because the maximum number of errors (10) was
exceeded.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
This is the Table def:
CREATE TABLE [GACT2] (
[id] [uniqueidentifier] NOT NULL ,
[Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
These are a couple of lines from the Import file:
14|3J6|897000|117236710|B|||20070103|20070103|2007 0104|000000000080000000|+|000000000044800000|+|CIT IGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
15|3J6|897000|117236710|B|||20070103|20070103|2007 0104|000000000080000000|+|000000000044800000|+|CIT IGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
And this is my Bulk Insert Query:
Delete from GACT2
GO
BULK INSERT GACT.dbo.GACT2
FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
KEEPNULLS,
KEEPIDENTITY
)
TIA
Matt
Your problem is with the first field - you are trying to push what appears
to be an int value (14) into a unique identifier column. So your data is
incompatible with the table. You have to get valid data or change your
schema or pipe it to a holding table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OlnRNS0MHHA.3268@.TK2MSFTNGP04.phx.gbl...
> I'm having a little trouble with a Bulk Insert. This is my first time
> working with this, so I'm not sure what I'm missing here. I've read
> everything I can find about Bulk Insert in BOL and from querying the
> archive, but I'm not finding the answer.
> This is the error I'm getting when attempting to Bulk Insert the data into
> a table in a local MSDE DB named GACT:
> Server: Msg 4865, Level 16, State 1, Line 1
> Could not bulk insert because the maximum number of errors (10) was
> exceeded.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
> This is the Table def:
> CREATE TABLE [GACT2] (
> [id] [uniqueidentifier] NOT NULL ,
> [Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> These are a couple of lines from the Import file:
> 14|3J6|897000|117236710|B|||20070103|20070103|2007 0104|000000000080000000|+|000000000044800000|+|CIT IGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> 15|3J6|897000|117236710|B|||20070103|20070103|2007 0104|000000000080000000|+|000000000044800000|+|CIT IGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> And this is my Bulk Insert Query:
> Delete from GACT2
> GO
> BULK INSERT GACT.dbo.GACT2
> FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> KEEPIDENTITY
> )
> TIA
> Matt
>
|||> Your problem is with the first field - you are trying to push what appears
> to be an int value (14) into a unique identifier column. So your data is
> incompatible with the table. You have to get valid data or change your
> schema or pipe it to a holding table.
That's not an issue. I'm creating the output file myself, so I can remove
that field if I need to. What would be the best way to have a unique ID
column? Do I need to leave it null in my import file and let SQL generate
it? Or should I just change the table def for that column to INT(14)
instead?
TIA
Matt
|||>> Your problem is with the first field - you are trying to push what
> That's not an issue. I'm creating the output file myself, so I can remove
> that field if I need to. What would be the best way to have a unique ID
> column? Do I need to leave it null in my import file and let SQL generate
> it? Or should I just change the table def for that column to INT(14)
> instead?
I did some testing and ended up changing that field in my table to INT and
now I get past that error. Now I'm getting a new error that says
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 15
(Description).
I have the description field defined as [Description] [varchar] (240)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
The description field in my file is always 240 chars. It CAN'T be any bigger
because that's the number of bytes I'm extracting for that field and writing
to the file. I don't get it. Changing it to [TEXT] helps, but it only
imports up to that field for one row and stops.
|||I found it odd that you're using \n alone as a rowterminator. It *looks* to
me like your data may naturally contain carriage returns and/or line feeds,
unless that was a bad copy & paste, so I'd make sure that you use a proper
delimiter that can't appear in your data.
A
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OY3ovA2MHHA.992@.TK2MSFTNGP04.phx.gbl...
> I did some testing and ended up changing that field in my table to INT and
> now I get past that error. Now I'm getting a new error that says
> Server: Msg 4863, Level 16, State 1, Line 1
> Bulk insert data conversion error (truncation) for row 1, column 15
> (Description).
> I have the description field defined as [Description] [varchar] (240)
> COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> The description field in my file is always 240 chars. It CAN'T be any
> bigger because that's the number of bytes I'm extracting for that field
> and writing to the file. I don't get it. Changing it to [TEXT] helps, but
> it only imports up to that field for one row and stops.
>
|||>I found it odd that you're using \n alone as a rowterminator. It *looks*
>to me like your data may naturally contain carriage returns and/or line
>feeds, unless that was a bad copy & paste, so I'd make sure that you use a
>proper delimiter that can't appear in your data.
The lines are pretty long, so the copy of it wrapped. Each row of data is on
one line in my file and is terminated with 0D 0A Hex. Is that \n or \r\n?
I've tried both. I also tried adding just a ! at the end of the row and
using "|!" as the terminator, but it didn't change anything either.
|||> >I found it odd that you're using \n alone as a rowterminator. It *looks*
> The lines are pretty long, so the copy of it wrapped. Each row of data is
> on one line in my file and is terminated with 0D 0A Hex. Is that \n or
> \r\n? I've tried both. I also tried adding just a ! at the end of the row
> and using "|!" as the terminator, but it didn't change anything either.
Nevermind. It was a standard 1d10t error. I changed the path to where I was
generating the output file in my code, but forgot to change the Bulk Insert
query in QA to reflect that. It works perfectly now.
Thanks for anyone that posted.

Thursday, February 16, 2012

Bulk Copy Errors

I am getting a "The process could not bulk copy into table '[dbo].[ ... "
error when attempting set up transactional replication between two SQL Server
2000 servers. The error occurs when copying the snapshot. The funny thing is
that the specific error states there is a primary key violation. However the
target table is empty and the source table does not contain any primary key
violations. Any ideas on what's causing this and how to address it?
Don't have a clue. I have never heard of this error before.
I'd try logging as per this kb article in hopes it can shed more light on
the matter.
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:6073D5C2-709B-441C-A62D-EB2182A3ADCF@.microsoft.com...
>I am getting a "The process could not bulk copy into table '[dbo].[ ... "
> error when attempting set up transactional replication between two SQL
> Server
> 2000 servers. The error occurs when copying the snapshot. The funny thing
> is
> that the specific error states there is a primary key violation. However
> the
> target table is empty and the source table does not contain any primary
> key
> violations. Any ideas on what's causing this and how to address it?

Friday, February 10, 2012

Build fails but no errors are returned

hi,

I moved my ssis solution from on dev machine to another.

When building the solution, visual studio keeps saying that the build failed but no errors are returned which is not really helpful...

any clues would be appreciated.

thanks

I don't believe you need to "build" anything.|||

well, the solution is built before starting the debugger process...

anyway, I've sorted my problem... errors where not displayed in the error list but I've found one returned in the output window.

It was complaining that it could not load the project files (note that I had no errors while loading the solution in VS).

I've deleted the .suo and other user files from the solution and it now works... did not realise that project file paths informations could be in the user files... I was expecting this information to be specific to solution and project files ...

|||I set my projects to never build under Tools-Options. Never had a problem.

Build Errors with the code!!

Hi,

I'm new to SQL Server RS.

I'm getting build errors after writing the following code:

List<ReportParameter> reportParam = new List<ReportParameter>();
reportParam.Add(new ReportParameter("Database", connectionString));
reportParam.Add(new ReportParameter("master_dept", hidMasterDept.Value.ToString().Trim()));
reportParam.Add(new ReportParameter("station_id", hidStationId.Value.ToString().Trim()));

reportViewer.ServerReport.SetParameters(reportParam)

The error message is as follows:
No overload for method 'ReportParameter' takes '2' arguments

But when I build the page using "Build Page" command from the menu, it builds successfully and then the project build succeeds.

When I modify any other page and build the solution, the same build error for this page occurs again.

Any idea how to resolve this? Been spending a lot of time figuring this out.

Thanks in Advance,

Manjunath HK

First make sure that all your parameters are single-valued, not multi-valued.

The best practice is always to create the report parameter just with a name and then adding values to it using Values collection like this:

reportParam.Add((new ReportParameter("Database")).Values.Add(connectionString))

Shyam

|||Hi Shyam,

Thanks for the reply.
I tried the same code suggestion given by you. But still nothing the same problem exists.
|||

Go to the following folder on your machine:

Program Files\Microsoft Visual Studio 8\ReportViewer

right click on Microsoft.ReportViewer.WinForms.dll and go to Version tab and make sure that the version is 8.0.50727.42

Also check out this article:

http://support.microsoft.com/default.aspx/kb/933137

Shyam

|||The problem got solved.
I used the following code:

List<Microsoft.Reporting.WebForms.ReportParameter> reportParam = new List<Microsoft.Reporting.WebForms.ReportParameter>();
reportParam.Add(new Microsoft.Reporting.WebForms.ReportParameter("Database", connectionString));
reportParam.Add(new Microsoft.Reporting.WebForms.ReportParameter("master_dept", hidMasterDept.Value.ToString().Trim()));
reportParam.Add(new Microsoft.Reporting.WebForms.ReportParameter("station_id", hidStationId.Value.ToString().Trim()));

I really dont know how it worked. I had already given "using Microsoft.Reporting.WebForms" in my code. But everything is working fine after the above change.

The build succeeded and till now I haven't found any issues.

Sunday, March 25, 2012

Bulk insert, skip rows with duplicate key error?

BULK INSERT UTF8 File ERROR 4864 on first row first column

Thursday, March 22, 2012

bulk insert question? Another way?

Thursday, March 8, 2012

Bulk insert errors

Bulk insert errors

BULK INSERT errors

BULK Insert Errors

BULK Insert Errors

Bulk insert errors

BULK Insert Errors

BULK Insert Errors

Thursday, February 16, 2012

Bulk Copy Errors

Friday, February 10, 2012

Build fails but no errors are returned

Build Errors with the code!!

build sql script

Blog Archive

About Me