Bulk Insert Fails !

Hello

I am trying to execute a BULK INSERT ... this is my code
BULK INSERT myTableSQL.dbo.[Daily_ss] FROM 'c:\daily_ss.txt'
WITH (
DATAFILETYPE='native',
FIELDTERMINATOR = 'char(9)',
ROWTERMINATOR = '\n'
)

But I get an Error

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 1. Make sure the field terminator and row terminator are
specified correctly.
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.
The statement has been terminated.

1. The FIELDTERMINATOR in my file is the tab ...is it correct that i
declare it as char(9)?

2. the date format in the text file are dd/mm/yyyy will i have a
problem with this? Because SQL uses mm/dd/yyyy

3. I used this with DATAFILETYPE='char' and i get the same error

Please HELP :)
ThanksHi savvaschr,

I guess you are right at you 1st doubt as you have specified 'char(9)' and
not actual tab. So I guess you need to paste actual tab charecter inthe
quotes, also make sure that you have dropped indexes. I am a bit doubtful
about the 2nd # as it shouldn't be a problem.

Please let us know the solution.

Best regards,
Tushar.|||Will the data fit in the column?
Is your column varchar(50) and the data is greater than 50 characters?

http://sqlservercode.blogspot.com/|||I copy the 'tab' character from the file and i put it as FIELDSEPARETOR
but it still dont work and i 've tested it with another file and table
with no date fields.

So maybe is the tab character OR is the indexes of the table . Shall i
remove the indexes?
And if i remove them in order for the BULK INSERT to work and I have
to add them again whats the purpose of BULK INSERTing them instead of
adding row by row ?|||<savvaschr@.nodalsoft.com.cy> wrote:

> Hello
> I am trying to execute a BULK INSERT ... this is my code
> BULK INSERT myTableSQL.dbo.[Daily_ss] FROM 'c:\daily_ss.txt'
> WITH (
> DATAFILETYPE='native',
> FIELDTERMINATOR = 'char(9)',
> ROWTERMINATOR = '\n'
> )
> But I get an Error
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 1. Make sure the field terminator and row terminator are
> specified correctly.
> 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.
> The statement has been terminated.
> 1. The FIELDTERMINATOR in my file is the tab ...is it correct that i
> declare it as char(9)?
> 2. the date format in the text file are dd/mm/yyyy will i have a
> problem with this? Because SQL uses mm/dd/yyyy
> 3. I used this with DATAFILETYPE='char' and i get the same error
> Please HELP :)
> Thanks

I believe the command you want is...

BULK INSERT myTableSQL.dbo.[Daily_ss] FROM 'c:\daily_ss.txt'
WITH (
DATAFILETYPE='char',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

Craig|||i Use the above and i got an error

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 22. Make sure the field terminator and row terminator are
specified correctly.
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.
The statement has been terminated.

I am openign a unix file and read from it ...is there a chance that the
'\n' is not the RowTerminator ? What posible character except '\n'
might be?

Thanks a lot
Savvas|||(savvaschr@.nodalsoft.com.cy) writes:
> i Use the above and i got an error
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 22. Make sure the field terminator and row terminator are
> specified correctly.
> 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.
> The statement has been terminated.
>
> I am openign a unix file and read from it ...is there a chance that the
> '\n' is not the RowTerminator ? What posible character except '\n'
> might be?

\n as terminator has always been problematic, because \n in the format
specification is interpreted as \r\n. I can't recall that I have ever
been able to get it to work.

A work around is to open the file from Windows with an editor, and make
sure that when you save again that lines are terminated with \r\n.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||<savvaschr@.nodalsoft.com.cy> wrote:

>i Use the above and i got an error
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 22. Make sure the field terminator and row terminator are
> specified correctly.
> 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.
> The statement has been terminated.
>
> I am openign a unix file and read from it ...is there a chance that the
> '\n' is not the RowTerminator ? What posible character except '\n'
> might be?
> Thanks a lot
> Savvas

As Erland already pointed out, the most likely culprit is the fact that your
file is from a Unix server. This is definitely the problem if the records
in your file have 22 fields. As far as I can tell, you have 2 options:

1. Do as Erland suggested and "fix" the file before you import it... either
with a text editior or little utility program you write (please see below).

2. See if the code running on the Unix host can be changed to use \r\n
instead of \n.

Craig|||Ok Guys
I forced unix to use chr(13) at the ena of line and now its ok
but in date fields i am geting an error saying:

code page 737 doesnt exist

I have tried both dd/mm/yyyy and mm/dd/yyyy formats and i still take
the same error|||(savvaschr@.nodalsoft.com.cy) writes:
> Ok Guys
> I forced unix to use chr(13) at the ena of line and now its ok
> but in date fields i am geting an error saying:
> code page 737 doesnt exist
> I have tried both dd/mm/yyyy and mm/dd/yyyy formats and i still take
> the same error

For date formats, you are best of using YYYY-MM-DD (or YYYYMMDD). However
that message, which I have never seen, is something different, and not
related to date format.

Going back to your first post, I see that you have specified
DATAFILETYPE='native'. Native here means that the data is the binary
form of the SQL Server data types. So a datetime value, should be an
8-bit binary value. I don't think your Unix system produces that.

Try DATAFILETYPE='character' instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||<savvaschr@.nodalsoft.com.cy> wrote:

> Ok Guys
> I forced unix to use chr(13) at the ena of line and now its ok
> but in date fields i am geting an error saying:
> code page 737 doesnt exist
> I have tried both dd/mm/yyyy and mm/dd/yyyy formats and i still take
> the same error

It's terrible to admit this, but I've been in a shop that only does American
English for years, so my experience with code pages is limited. However, I
would suggest using the format yyyymmdd. I believe this is guaranteed to be
interpreted correctly by SQL Server regardless of locale or codepage.

Good luck,

Craig|||OK

I forced put ^m as line terminator at unix files and i put the date
field format as
yyyy-mm-dd and its working ok

Thanks a lot to everybody
Savvas

Bulk Insert error file does not exist

Hello

I am trying to bulk insert a text file into SQL 2005 table. When I execute the bulk insert I get the error

"Msg 4860, Level 16, State 1, Line 1. Cannot bulk load. The file "\\ENDUSER-SQL\EnduserText\B1020063.txt" does not exist."

The text file that it is saying does not exist I recently created thru my code. I can open the file but only when I rename the file will the Bulk Insert work. After creating the text file I am moving it to the server that SQL server is running on. Also if I run sp_FileExists it also says the file does not exist unless again I rename the file then this stored procedure recognizes the file. I dont' know if I have a permission issue or what is the problem. Any help would be appreiated.

Thanks

Chris

What is your bcp command? What is the original and new name of the file?|||This is always a rights or login issue.

How are you "running" the bulk insert, is it a job or a dts or a query or what? Is it "Bulk Insert" or "BCP.EXE".

Assuming it is Bulk Insert, like you said, it is running on the SERVER as the logged in user. Can the server access \\ENDUSER-SQL...?

What are you "renaming" the file too? Are you actually moving the file to another location or just renaming it? I don't see anything wrong with the name.|||

I am running the bulk insert from a query. The original file name is B120049.txt, I then rename the file to say B120049BLK.txt save it then the bulk insert runs fine.

Thanks

Chris

|||

I am running the bulk insert in a query

BULK INSERT B1020063 FROM '\\ENDUSER-SQL\EnduserText\B1020063.txt' WITH (FIELDTERMINATOR = '|')

ENDUSER-SQL is the server that MS SQL 2005 is running on. I am running the query thru my Delphi 2005 code using ADO components. What I am doing is running my Delphi code to create the text file from a paradox table (don't laugh) then trying to use the bulk insert to take the text file into SQL table. I am running the code on my desktop and creating the text file on a shared folder on the SQL Server (\\ENDUSER-SQL\EnduserText\..)

When I rename the file I am taking B1020063.txt renaming to B1020063BLK.txt and it works. Also if after saving the file once I save it back to orginal name it also works. I am not moving the file from the orginal location just changing the name.

Thanks for the help.

Chris

|||BULK INSERT will be running from the server, so it doesn't matter where you ran it from.

Try changing the file name to the actual physical directory "C:\EndUser...." or whatever. See if that works.

It has to be a rights issue to the file. When you rename it, it is getting your rights, then running as you so it works.|||Do an xp_cmdshell 'dir \\ENDUSER-SQL\EnduserText' to see if SQL Server can access the folder.|||

I tried renaming the file to C:EnduserText\B1020049.txt and go the same results. Any ideas on how I can change the rights on the file so I can use it without having to renam it?

Thanks

Chis

|||

I ran this stored procedure and here are the results :

Volume in drive \\ENDUSER-SQL\EnduserText has no label.
Volume Serial Number is 547D-E293
NULL
Directory of \\ENDUSER-SQL\EnduserText
NULL
10/29/2006 01:32 PM <DIR> .
10/29/2006 01:32 PM <DIR> ..
10/27/2006 03:27 PM 77,087 AcctRpts.txt
10/27/2006 03:27 PM 826,363 AcctSql.txt
10/27/2006 03:27 PM 217 AcctTypes.txt
10/27/2006 03:27 PM 121 AcctTypes2.txt
10/27/2006 03:27 PM 1,411,007 Address.txt
10/27/2006 03:27 PM 7,350 Addrlink.txt
10/27/2006 03:27 PM 185,409 AllFeat.txt
10/27/2006 03:27 PM 574 Allocation.txt
10/27/2006 03:27 PM 13,954 ANPI.txt
10/27/2006 03:27 PM 617 AREACODE.txt
10/27/2006 03:27 PM 1,882 ARSETUP.txt
10/27/2006 03:27 PM 3,613 B1020049.txt
10/27/2006 03:27 PM 7,243 B1020063.txt
10/27/2006 03:30 PM 50,497,983 B1120022.txt
10/27/2006 03:32 PM 46,926,455 B1120023.txt
10/27/2006 03:35 PM 54,188,893 B1120024.txt
10/27/2006 03:37 PM 50,094,853 B1120025.txt
10/27/2006 03:39 PM 54,233,552 B1120026.txt
10/27/2006 03:41 PM 54,900,297 B1120027.txt
10/27/2006 03:41 PM 72 BILCYCLE.txt
10/27/2006 03:41 PM 4,549 Billdate.txt
10/27/2006 03:41 PM 0 BILLERR.txt
10/27/2006 03:41 PM 3,765,367 BILLMAIN.txt
10/27/2006 03:41 PM 10,293 Billplan.txt
10/27/2006 03:44 PM 35,721,280 Billsum.txt
10/27/2006 03:41 PM 261,183 BILLTOBK.txt
10/27/2006 03:44 PM 152,994 Bilmesg.txt
10/27/2006 03:44 PM 2,574 BundledService.txt
10/27/2006 03:44 PM 1,759 BusCalendar.txt
10/29/2006 11:27 AM 49,507,562 C200110.txt
10/29/2006 11:29 AM 45,172,115 C200111.txt
10/29/2006 11:31 AM 47,562,230 C20017.txt
10/29/2006 11:33 AM 50,695,011 C20018.txt
10/29/2006 11:36 AM 56,648,282 C20019.txt
10/29/2006 11:38 AM 50,773,887 C20021.txt
10/29/2006 11:40 AM 46,550,340 C200210.txt
10/29/2006 11:42 AM 43,977,305 C200211.txt
10/29/2006 11:44 AM 47,850,286 C200212.txt
10/29/2006 11:46 AM 45,525,197 C20022.txt
10/29/2006 11:48 AM 41,312,655 C20023.txt
10/29/2006 11:50 AM 48,118,979 C20024.txt
10/29/2006 11:52 AM 45,851,116 C20025.txt
10/29/2006 11:54 AM 48,136,512 C20026.txt
10/29/2006 11:57 AM 47,693,599 C20027.txt
10/29/2006 11:59 AM 48,643,674 C20028.txt
10/29/2006 12:01 PM 49,560,178 C20029.txt
10/29/2006 12:03 PM 47,320,372 C20031.txt
10/29/2006 12:05 PM 44,242,516 C200310.txt
10/29/2006 12:07 PM 43,453,596 C200311.txt
10/29/2006 12:09 PM 48,399,934 C200312.txt
10/29/2006 12:11 PM 42,166,984 C20032.txt
10/29/2006 12:13 PM 42,449,679 C20033.txt
10/29/2006 12:15 PM 42,856,103 C20034.txt
10/29/2006 12:17 PM 44,666,989 C20035.txt
10/29/2006 12:19 PM 45,443,402 C20036.txt
10/29/2006 12:21 PM 47,432,232 C20037.txt
10/29/2006 12:23 PM 46,027,801 C20038.txt
10/29/2006 12:25 PM 49,730,365 C20039.txt
10/29/2006 12:27 PM 45,443,543 C20041.txt
10/29/2006 12:29 PM 44,342,955 C200410.txt
10/29/2006 12:31 PM 42,923,015 C200411.txt
10/29/2006 12:33 PM 43,391,572 C200412.txt
10/29/2006 12:35 PM 41,510,324 C20042.txt
10/29/2006 12:37 PM 45,968,110 C20043.txt
10/29/2006 12:38 PM 42,195,260 C20044.txt
10/29/2006 12:41 PM 47,489,289 C20045.txt
10/29/2006 12:43 PM 44,718,583 C20046.txt
10/29/2006 12:45 PM 47,564,502 C20047.txt
10/29/2006 12:47 PM 46,161,036 C20048.txt
10/29/2006 12:49 PM 47,575,248 C20049.txt
10/29/2006 12:51 PM 45,312,859 C20051.txt
10/29/2006 12:53 PM 44,072,464 C200510.txt
10/29/2006 12:55 PM 43,128,828 C200511.txt
10/29/2006 12:57 PM 42,414,577 C200512.txt
10/29/2006 01:34 PM 48,520,020 c20052.txt
10/29/2006 12:59 PM 42,046,826 C20053.txt
10/29/2006 01:00 PM 40,829,562 C20054.txt
10/29/2006 01:02 PM 46,261,908 C20055.txt
10/29/2006 01:04 PM 44,259,999 C20056.txt
10/29/2006 01:06 PM 44,248,580 C20057.txt
10/29/2006 01:08 PM 44,001,686 C20058.txt
10/29/2006 01:10 PM 44,345,187 C20059.txt
10/29/2006 01:12 PM 44,186,657 C20061.txt
10/29/2006 01:14 PM 30,604,652 C200610.txt
10/29/2006 01:16 PM 42,462,188 C20062.txt
10/29/2006 01:18 PM 44,203,999 C20063.txt
10/29/2006 01:20 PM 42,164,513 C20064.txt
10/29/2006 01:22 PM 51,782,537 C20065.txt
10/29/2006 01:24 PM 46,269,771 C20066.txt
10/29/2006 01:27 PM 51,892,690 C20067.txt
10/29/2006 01:29 PM 48,158,790 C20068.txt
10/29/2006 01:32 PM 50,942,048 C20069.txt
08/22/2006 04:10 AM 3,927,036 CASS060817done.txt
08/24/2006 12:32 PM 3,485 vod082406.txt
10/19/2006 12:13 PM 3,522 vod101306.txt
10/19/2006 12:13 PM 8,695 vod101606.txt
96 File(s) 3,236,402,958 bytes
2 Dir(s) 130,462,183,424 bytes free
NULL

It looks like it sees all the text files I have created.

Thanks for the help.

Chris