Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS SQL Bulk Insert problem

Posted on 2011-02-16
5
Medium Priority
?
395 Views
Last Modified: 2012-08-13
Hi All

Time to page the FOAK once again.

I'm having trouble with a bulk insert statement - hope someone can help me out here!

The sql is as follows:-

BULK INSERT  dbo.gbinfo FROM  'I:/pages/gb/pass/monthly.txt' WITH(FIELDTERMINATOR = '|',ROWTERMINATOR = '\n')

I have some 750k rows all looking like:-

1|2002-12-01|12|2.71|0.00|271.00|C010002|OXF|Central|1
3|2002-12-01|12|30.51|0.00|3051.00|C010005|*N|*N|1
4|2002-12-01|12|1.67|0.00|167.00|C010007|UN |Scotland|1
5|2002-12-01|12|7.09|0.00|709.00|C010008|OXF|Central|1

and so on

Until I can get an import I've set all the columns in the table to nvarchar and there is no index column.

It's chucking up this error:-

Auto-attach to process '[2340] [SQL] GRANTB' on machine 'GRANTB' succeeded.
The thread 'GRANTB\SQLEXPRESS [53]' (0x17a8) has exited with code 0 (0x0).
The thread 'GRANTB\SQLEXPRESS [53]' (0x17a8) has exited with code 0 (0x0).
The program '[2340] [SQL] GRANTB: GRANTB\SQLEXPRESS' has exited with code 0 (0x0).

Many thanks in advance!

Best regards

Grant
0
Comment
Question by:Buzby58
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34910632
could it be that your 750K rows result in 2G (sql express 2000/2005) or 4GB (sql express 2008) of data?
that would explain it, eventually ...
0
 

Author Comment

by:Buzby58
ID: 34910756
Thanks for uber prompt response!

Sorry I'm newsish to MS SQL

The txt file is 28mb and am using SQL express 2008 - load works like a dream in MySql but that options not open to me!

I've just discovered this:-

Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 10. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Not sure about the field terminator and there's no nulls in there as far as can see - nulls are allowed in the receiving table.

Thanks

Grant

0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 34915634
Since the error is that the last value in the first row is too long, my guess is that the row terminator is at fault and the import has interpreted the entire remainded of the file as the contents of row1, column 10.

0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 34915668
Try
ROWTERMINATOR = '/r'

More Info on row terminators here:

http://msdn.microsoft.com/en-us/library/ms191485.aspx
0
 

Author Comment

by:Buzby58
ID: 34916528
Tried a good combination of row terminators - still no go - just can't see why it won't work :-/
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question