Solved

bcp "0 rows copied"

Posted on 2006-07-13
7
3,516 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

685 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