Solved

bcp "0 rows copied"

Posted on 2006-07-13
7
3,572 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
[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 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Output of Select Statement 2 38
SQL Server sp_executesql / Change Tracking Version() results into variable 4 30
sql trace 4 28
Search Text in Views 2 28
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 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