Solved

bcp "0 rows copied"

Posted on 2006-07-13
7
3,298 Views
Last Modified: 2008-02-20
i am trying to load a text file into a table.
no errors are reported but nothing loads.

the SQL engine is running on the same machine from
which i am trying to bcp.

--- START ---
C:\Temp> bcp trading.dbo.tradeEvents in C:\Temp\data.txt -f C:\Temp\bcp.fmt -T
Starting copy...

0 rows copied.
Network packet size (bytes): 4096
ClockTime (ms.) Total        : 1
C:\Temp>
--- END ---

this is a sample row in "data.txt":
--- START ---
IBM;2006-06-21 00:00:01.0000;77.98;3689400;0.0024;0.0028;0.0102;
--- END ---

this is "bcp.fmt":
--- START ---
9.0
7
1    SQLCHAR           2     50     ";"     1     symb               SQL_Latin1_General_CP1_CI_AS
2    SQLDATETIME    0     8       ";"     2     day                 ""
3    SQLMONEY4       0     4       ";"     3     adjCls             ""
4    SQLINT              0     4       ";"     4     volume            ""
5    SQLFLT8            0     8       ";"     5     intradayPPC     ""
6    SQLFLT8            0     8       ";"     6     dailyPPC          ""
7    SQLFLT8            0     8       ";"     7     nightlyPPC       ""
--- END --- END ---

i also tried "bcp .... -U dwilkins -P roppongi" instread of "bcp .... -T"
i think/know "dwilkins" has write permission on "tradeEvents".
it made no difference.
0
Comment
Question by:dwilkins123
  • 4
  • 3
7 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
What if you add

-e C:\Err.TXT


to that command line (to create an error file)


Then check if there is any more info in Err.TXT
0
 

Author Comment

by:dwilkins123
Comment Utility
note the "-e C:\Temp\err.text" addition
--- START ---
C:\Temp> bcp trading.dbo.tradeEvents in C:\Temp\data.txt -f C:\Temp\bcp.fmt -T -e C:\Temp\err.txt
Starting copy...

0 rows copied.
Network packet size (bytes): 4096
ClockTime (ms.) Total        : 1
C:\Temp>
--- END ---

and C:\Temp\err.txt is empty.
0
 

Author Comment

by:dwilkins123
Comment Utility
i really need to get this data loaded asap.

i've spend hours and messed around with "BULK INSERT"

the most common errors i get:
(1) "unexpected end of file"
(2) "the column is too long in the data file"

(3) i can correctly load data, however
it will only load the first row in "data.txt".
and all the columns must be typed as "text".


what are so things to look at to solve any one
of those problems?

of course, the ideal solution is to use "bcp".

thnx.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
Those error messages imply that there are not a fixed number of columns in the file.

I suggest as an initial troubleshooting step, that you try loading just the first line in.

Then keep adding ten lines till it stops working.

Then you can identify any lies with issues.



That sample line that you provided has an extra ; at the end Is that a typo? If not, your format file should look something like this:



9.0
7
1    SQLCHAR           2     50     ";"     1     symb               SQL_Latin1_General_CP1_CI_AS
2    SQLDATETIME    0     8       ";"     2     day                 ""
3    SQLMONEY4       0     4       ";"     3     adjCls             ""
4    SQLINT              0     4       ";"     4     volume            ""
5    SQLFLT8            0     8       ";"     5     intradayPPC     ""
6    SQLFLT8            0     8       ";"     6     dailyPPC          ""
7    SQLFLT8            0     8       ";\r\n"     7     nightlyPPC       ""



Note that field sevens terminator is a ; followed by carriage return/linefeed in this example.

0
 

Author Comment

by:dwilkins123
Comment Utility
";\r\n\" as the final column terminator produces the same result of:
---> no columns inserted; no errors <---

i experimented with how i can intentionally generate errors.
all of these actions generate errors:

(1) deleting a column out of "bcp.fmt" fails, as well as changing their type
generates errors:
bcp knows about the column structure of the table. so, this proves that if
there was a table/"bcp.fmt" formatting error, i'd get an error.

is there an ENV variable i need set in the shell?

the version of SQL Server i use is: [SQL-Server 2005 [Developer Edition] v. 9.00]
0
 

Author Comment

by:dwilkins123
Comment Utility
I DID IT!!! YES!!! IT WORKS :::: case closed

stating with this simplest file format and working
fwd was the beginning of finding the solution.

the trick is.....
when bcp-ing a text fmt file,  you gotta enter, no matter the true format:

file storage type [int]: ---> char
prefix length [2]: ---> 0
length of field [4]: ---> 50
field terminator [none]: ---> ;

bcp wants to view everything as a fixed-width CHAR with a delimitor.
even if its really an INT, MONEY, DATETIME, FLOAT ........

(1) "type" the columns as you really want then in Serv. Mngr.
(2) load everything as [CHAR(50)] in bcp.

and i think why? what is the meaning? this makes no sense.
in MySQL, i loaded my tables in 5-minutes.
as i learn more, SQL Server better be worth this insanity.
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 200 total points
Comment Utility
Yes I've always found format files very painful.

Usually you can get away without them though. Using BULK INSERT, this should also have worked (if the number of fields in destination table match the number of fields in the source):



BULK INSERT YourTable
FROM 'YourFile.TXT'
WITH (
FIELDTERMINATOR = ';'
)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now