Problems with BCP Command Line

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Problems with BCP Command Line

Author

Topic

sanctos
Starting Member

19 Posts

Posted - 2004-11-10 : 10:31:19

I thought I would make a fresh topic here.

I am trying to use the bcp utility command to create my bcp Format file. In case no one read the other post, here is my problem.

Setup Info:

Server Name: IDXDC
Database Name: MBLOGS_DEV
Table Name: AuditJournal_DEV
- AuditID numeric 9 (auto-number, not in CSV import file, this is important!!)
- AccntNo varchar 50 (was numeric)
- EditDate varchar 50 (was datetime, couldn't convert from import file.)
- EditTime varchar 50
- Users varchar 50 (had to change from 'user' cause it's a keyword and bcp doesn't like it.)
- AccntName varchar 50
- Field varchar 50
- OldValue varchar 100
- NewValue varchar 100
- ClientName varchar 100

Audit.CSV (comma delimited file, no AuditID auto-number, quoted values in quotes.)
- AccntNo
- EditDate
- "EditTime"
- "User"
- "AccntName"
- "Field"
- "OldValue"
- "NewValue"
- "ClientName"

Heres the bcp command I have so far:

bcp MBLOGS_DEV.dbo.AuditJournal_DEV in "\\'SERVERNAME'\InetPub\wwwroot\Intranet\medbill\test\Audit.CSV" -F2 -c -t, -SIDXDC -Umyname -Pmyname

When I run this I get this error:

----------------------------------------------------------------
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

8 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 15
---------------------------------------------------------------

I am a bit sketchy on some of the syntax:
-F2 (the first row is 2, since there are headers on line 1)
-c
-t, (field terminator is a comma, will -t, work?)

switches I did not include:
-r (row terminator is a new line, do I need to put -r\n ?

Any help would be excellent, I have been working on this for a really long time.

Sanctos

UPDATE: The CSV file has thousands of records in it. The bcp command is actually copying 8 records only into it. They are all shifted over 1 to the left due to the AuditID field....this is not good.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-10 : 13:35:14

see
http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html

It includes a bit on format files for quote delimitted strings.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources