Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how do i fix format of export using bcp in sql server

Posted on 2013-11-07
3
Medium Priority
?
366 Views
Last Modified: 2013-11-25
When I issue the following export command

bcp " select fname, lname, dept, REPLACE(REPLACE(contract_no, CHAR(13), ''), CHAR(10), ''), audit_data from DEV.dbo.contracts" queryout "C:\exports\coontracts.txt"  -c -t"|" -r"\IAC-EOR" -T -S 127.0.0.1

the data successfully exports 1810 records

when I try to import the data into oracle using an external table

I execute the following script:

CREATE TABLE contracts_load (fname CHAR(15), lname CHAR(20), dept CHAR(20), contract_no CHAR(20), audit_data CHAR(2000))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY contracts_dir ACCESS PARAMETERS (RECORDS DELIMITED BY "IAC-EOR" FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' LRTRIM
(fname CHAR(15), lname CHAR(20), dept CHAR(20), contract_no CHAR(20), audit_dataCHAR(2000))
LOCATION ('contracts.txt'));

the table creates successfully. However, when I do a select count(*) from contracts_load only 139 records are there. I check the log file and see the following error:

KUP-04021 field formatting error for field audit_data
KUP-04023 field start is after end of record

When I check the contracts.txt file it appears the records continue to a point and cut off. for example

lark|andrea|finance|F33760C-5006|0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*429*429**429*429**429*429**429*429**429*429**429*429**429*429**429*429**429*429**429*429**429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*IAC-EORnelson|roy|A22476-C-5007|0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*IAC-EORdawkins|jenny|D226703-K-3772|0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*521*521**521*521**521*521**521*521**521*521**521*521**521*521**521*521*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*429*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*0*
0*0*0*0*0*0*IAC-EOR

I added the IAC-EOR record delimiter because i was only getting 1615 records when my record delimiter was NEWLINE. There are 1810 records. Can someone help me figure out how to fix this? I am not sure if it can be done during the bcp export or fixed during the external table creation in Oracle.
0
Comment
Question by:sikyala
[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
3 Comments
 
LVL 9

Expert Comment

by:guswebb
ID: 39631217
You can only stream a certain amount of text using BCP so it looks like you are hitting that ceiling and your export truncates.
0
 

Accepted Solution

by:
sikyala earned 0 total points
ID: 39631507
I added the following lines to my access parameters list in the external table creation script:

MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS

and successfully loaded 1810 records
0
 

Author Closing Comment

by:sikyala
ID: 39674124
yay me
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

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 …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Viewers will learn how the fundamental information of how to create a table.

715 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