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

-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 //;)

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

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

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

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