Solved

bcp "0 rows copied"

Posted on 2006-07-13
7
3,357 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
ID: 17106675
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
ID: 17107591
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
ID: 17112717
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17116887
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
ID: 17117833
";\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
ID: 17118193
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
ID: 17118568
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

932 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

9 Experts available now in Live!

Get 1:1 Help Now