Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 28th, 2003, 02:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default BCP Error while importing data from a textfile

Hi All,

I do have a query here. Please help.

When I used BCP to import data from datafile to a table on sqlserver, I do get this error. Please let me know what could be wrong with it.

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

Network packet size (bytes): 4096
Clock Time (ms.): total 78
_____________________

Command I used is...
bcp DBNAME.OWNER.TABLENAME in sys.log.2003-11-01.csv -c -SSERVERNAME -Uxxxx -PXXXX

I have a file generated everyday (tab-column delimiter and newline-Row Delimiter) with the filename in the format(sys.log.yyyy-mm-dd.csv where dd is the previous day) given above in the command.

What I am trying to do is to automate the process of importing all the files to the sql server as a daily scheduled process/job and at the end of the month another job would analyse the data populated in the table and generate a report.

Please advise. New ideas are most welcome.

Thanks for help in advance.

Cheers,

-Vijay G
__________________
- Vijay G
Reply With Quote
  #2 (permalink)  
Old November 29th, 2003, 01:39 PM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

-Check if table definition matches generally with bcp datafile
-Check if all the field separators are placed write.

Work with single (first) record only in a bcp data file, you would know if definitions are ok or not, if not there would be a corruption in the file somewhere else //;)

Reply With Quote
  #3 (permalink)  
Old December 1st, 2003, 01:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I agree with you. I checked all that, and seems to be no issues with it.

But to my wonder the same works fine when I tried from EM using Import Wizard, as I was doing it for the last 2 months importing the data that way manually.

Cheers,

-Vijay G
Reply With Quote
  #4 (permalink)  
Old December 1st, 2003, 02:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

It worked now.

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000
1000 rows sent to SQL Server. Total sent: 13000
1000 rows sent to SQL Server. Total sent: 14000
1000 rows sent to SQL Server. Total sent: 15000
1000 rows sent to SQL Server. Total sent: 16000
1000 rows sent to SQL Server. Total sent: 17000
1000 rows sent to SQL Server. Total sent: 18000
1000 rows sent to SQL Server. Total sent: 19000
1000 rows sent to SQL Server. Total sent: 20000
1000 rows sent to SQL Server. Total sent: 21000
1000 rows sent to SQL Server. Total sent: 22000
1000 rows sent to SQL Server. Total sent: 23000
1000 rows sent to SQL Server. Total sent: 24000
1000 rows sent to SQL Server. Total sent: 25000
1000 rows sent to SQL Server. Total sent: 26000
1000 rows sent to SQL Server. Total sent: 27000
1000 rows sent to SQL Server. Total sent: 28000
1000 rows sent to SQL Server. Total sent: 29000
1000 rows sent to SQL Server. Total sent: 30000

30966 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 3234 Avg 0 (9575.14 rows per sec.)

Let me explain what I did to make it work.

I suspected if the filename could be the problem, so I renamed it to test.csv which didnot help.

Then I copied just a line in to test.csv and tried bcp, it worked. That way I copied 25%, 50% and 90% of the data to test.csv, and tried BCP, it worked.

Lastly, I tried with the entire data file, and failed to work. Then identified that FORMATTING was the problem.

All these I did were, Opened the original data file in WORDPAD, copied the same to notepad, and tried BCP with the file saved in NOTEPAD. This way I didnot know that formatting could be the problem. Only when I tried opening the original file in notepad I noticed that the records were not in different lines. Same was not seen when opened in WORDPAD. While copying the data from WORDPAD to NOTEPAD, I was unknowingly formatting it properly.

In EM I choose {LF} as the row separator.

So Is there any way I could get this formatting problem solved. Let me know what I should use for the the -r parameter to overcome this formatting problem.

Thanks for your time and effort in advance.

Cheers,

-Vijay G
Reply With Quote
  #5 (permalink)  
Old December 7th, 2004, 11:21 PM
Registered User
 
Join Date: Dec 2004
Location: Toronto, Ontario, Canada.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to YHAVR
Default

I wonder if you have been able to resolve this issue.
I have the same problem. However, loading the same file on a different machine doesn't produce this error. The problem even occur if the file has only one record.

Your advise would be highly appreciated.

Regards,
Yuriy
Quote:
quote:Originally posted by happygv
 It worked now.

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000
1000 rows sent to SQL Server. Total sent: 13000
1000 rows sent to SQL Server. Total sent: 14000
1000 rows sent to SQL Server. Total sent: 15000
1000 rows sent to SQL Server. Total sent: 16000
1000 rows sent to SQL Server. Total sent: 17000
1000 rows sent to SQL Server. Total sent: 18000
1000 rows sent to SQL Server. Total sent: 19000
1000 rows sent to SQL Server. Total sent: 20000
1000 rows sent to SQL Server. Total sent: 21000
1000 rows sent to SQL Server. Total sent: 22000
1000 rows sent to SQL Server. Total sent: 23000
1000 rows sent to SQL Server. Total sent: 24000
1000 rows sent to SQL Server. Total sent: 25000
1000 rows sent to SQL Server. Total sent: 26000
1000 rows sent to SQL Server. Total sent: 27000
1000 rows sent to SQL Server. Total sent: 28000
1000 rows sent to SQL Server. Total sent: 29000
1000 rows sent to SQL Server. Total sent: 30000

30966 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 3234 Avg 0 (9575.14 rows per sec.)

Let me explain what I did to make it work.

I suspected if the filename could be the problem, so I renamed it to test.csv which didnot help.

Then I copied just a line in to test.csv and tried bcp, it worked. That way I copied 25%, 50% and 90% of the data to test.csv, and tried BCP, it worked.

Lastly, I tried with the entire data file, and failed to work. Then identified that FORMATTING was the problem.

All these I did were, Opened the original data file in WORDPAD, copied the same to notepad, and tried BCP with the file saved in NOTEPAD. This way I didnot know that formatting could be the problem. Only when I tried opening the original file in notepad I noticed that the records were not in different lines. Same was not seen when opened in WORDPAD. While copying the data from WORDPAD to NOTEPAD, I was unknowingly formatting it properly.

In EM I choose {LF} as the row separator.

So Is there any way I could get this formatting problem solved. Let me know what I should use for the the -r parameter to overcome this formatting problem.

Thanks for your time and effort in advance.

Cheers,

-Vijay G
Reply With Quote
  #6 (permalink)  
Old October 6th, 2006, 02:07 AM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi All,

I've written a tool to transfer the records of one table of one dbserver to another table of another dbserver using bcp.

To write to a dat file i use the following bcp parameter

" \"select * from KoileiSource.dbo.[addressdetails] where status='D'\" QueryOut \"addressdetails.dat\" -e \"addressdetails.err\" -t\"\t\" -r\"\t\n\" -S\"KoileiSource\" -U \"sa\" -P\"sa\" -N"

To transfer the data to the target table i use the following bcp parameter

" \"KoileiTarget.dbo.addressdetails\" IN \"addressdetails.dat\" -e \"addressdetails.err\" -t\"\t\" -r\"\t\n\" -S\"KoileiTarget\" -U \"sa\" -P\"sa\" -N"


I get the following error

-----------------------
Started at 06.10.2006 10:42:59
Total Records Selected in AddressDetails : 40395

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000
1000 rows sent to SQL Server. Total sent: 13000
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
1000 rows sent to SQL Server. Total sent: 14000
1000 rows sent to SQL Server. Total sent: 15000
1000 rows sent to SQL Server. Total sent: 16000
1000 rows sent to SQL Server. Total sent: 17000
1000 rows sent to SQL Server. Total sent: 18000
1000 rows sent to SQL Server. Total sent: 19000
1000 rows sent to SQL Server. Total sent: 20000
1000 rows sent to SQL Server. Total sent: 21000
1000 rows sent to SQL Server. Total sent: 22000
1000 rows sent to SQL Server. Total sent: 23000
1000 rows sent to SQL Server. Total sent: 24000
1000 rows sent to SQL Server. Total sent: 25000
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
1000 rows sent to SQL Server. Total sent: 26000
1000 rows sent to SQL Server. Total sent: 27000
1000 rows sent to SQL Server. Total sent: 28000
1000 rows sent to SQL Server. Total sent: 29000
1000 rows sent to SQL Server. Total sent: 30000
1000 rows sent to SQL Server. Total sent: 31000
1000 rows sent to SQL Server. Total sent: 32000
1000 rows sent to SQL Server. Total sent: 33000
1000 rows sent to SQL Server. Total sent: 34000
1000 rows sent to SQL Server. Total sent: 35000
1000 rows sent to SQL Server. Total sent: 36000
1000 rows sent to SQL Server. Total sent: 37000
1000 rows sent to SQL Server. Total sent: 38000
1000 rows sent to SQL Server. Total sent: 39000
1000 rows sent to SQL Server. Total sent: 40000

40391 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 9654

Error in transfer
Ended at 06.10.2006 10:43:13
-----------------------


can anybody help me. This is the first time i'm using BCP.

Thanks in advance

Raheema









Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
BCP Error lisa30 SQL Server 2000 5 October 11th, 2007 07:06 PM
bcp error SpringSummer SQL Server 2005 1 July 12th, 2007 02:18 AM
Error exporting using bcp Pallen SQL Server 2000 0 July 29th, 2006 04:56 AM
BCP error while importing data from a text file happygv SQL Server ASP 1 December 15th, 2003 09:16 AM
Importing a TextFile, best way ioates SQL Server 2000 1 June 27th, 2003 07:24 AM



All times are GMT -4. The time now is 02:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.