Bulk insert and fixed fields problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk insert and fixed fields problem

Author

Topic

Konstruktorn
Starting Member

3 Posts

Posted - 2006-12-30 : 07:04:39

Hi!

I have a problem with a textfile (fixed fields) I want to import with BULK INSERT. It works fine with bcp but with BULK INSERT it simply doesn't work. Here's the code:

create table #temp_20061229
( [col1] char(1) NULL,
[col2] char(2) NULL,
[col2] char(8) NULL,
[col3] char(4) NULL,
[col4] char(3) NULL,
[col5] char(1) NULL,
[col6] char(30) NULL,
[col7] char(12) NULL,
[col8] char(9) NULL,
)

BULK INSERT #temp_20061229 FROM '\\dmclust1\Common\test.TXT'

and here's the error message:
"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.
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."

Please help!!

Forgot to mention:
- bcp works fine with global temporary table (not local) and that's why I want to use BULK INSERT
- BULK INSERT works with a bigger char, like char(100) (but then I get just one long row)

monty
Posting Yak Master

130 Posts

Posted - 2006-12-30 : 07:39:36

As it has error message 7399 its a bug which was reported to MS...

now either u have to use BCP utility or DTS to get rid if this issue..

for more details gothru this link:

http://support.microsoft.com/kb/324122

its me monty

Konstruktorn
Starting Member

3 Posts

Posted - 2006-12-30 : 14:06:11

Thanks monty, but then it wouldn't work with char(100) either...would it (?)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-30 : 15:09:08

Tell BULK INSERT explicit which separators/terminators to use.

Or, if you are running both commands in one run, put a GO between the two commands.

Peter Larsson
Helsingborg, Sweden

Konstruktorn
Starting Member

3 Posts

Posted - 2007-01-02 : 02:41:41

It's a fixed fields table so it has no separators (or is there a way to tell it's a fixed field??)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources