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

x
?
Solved

row terminator in Bulk Insert SQL Server 2005

Posted on 2011-03-10
7
Medium Priority
?
830 Views
Last Modified: 2012-05-11
i get an extract daily via ftp that was a text file that is now compressed and having a .gz extension and is now binary. my bulk insert was working without a ROWTERMINATOR when it  was a .txt file, but since its been .gz I get
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 33. Verify that the field terminator and row terminator are specified correctly.

I added a ROWTERMINATOR = '\r\n' to no avail. The file is an extract from Oracle. I can import the file into access no problem. Any help is appreciated.

Thanks,

Jim
0
Comment
Question by:jmoss111
[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
  • 4
  • 3
7 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35098558
.gz is a compressed file -- you need to un-compress it first using the gzip command like this:

  gzip.exe  -d  myfile.gz
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35098568
Run the above from a command prompt in the same folder where your file resides.  If you don't have gzip.exe you can download it from several places - just google.
0
 
LVL 18

Author Comment

by:jmoss111
ID: 35098587
yes i do uncompress the file and i have the problem with the extracted .txt file and not by trying to load the .gz file

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 33

Expert Comment

by:knightEknight
ID: 35099629
I'm guessing then that the file comes from a unix/linux environment.  Have you tried setting ROWTERMINATOR='\n'   or  ROWTERMINATOR='\r'  ?
0
 
LVL 18

Author Comment

by:jmoss111
ID: 35103934
the file did come from unix/Oracle and i tried ROWTERMINATOR = '\r\n'  and ROWTERMINATOR = '\n' and ROWTERMINATOR = '\r'
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 0 total points
ID: 35109677
i downloaded todos.exe and ran that against my files... problem solved
0
 
LVL 18

Author Closing Comment

by:jmoss111
ID: 35145516
i found a workable solution
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

618 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