SQL Server Performance Forum – Threads Archive

bcp fails

Hi, I have a weird problem with bcp. Below is my bcp command master..xp_cmdshell ‘BCP Mydb.dbo.tb_user in D: est.dat -b10000 -fD: est.fmt -SDevbox -Utest_user -Ptest_user’ This was earlier working on SQL 6.5 box. Now, am doing the same operation on 2000 after migration. But I get the following error Starting copy…
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
NULL
0 rows copied. But I was told that this works fine in the earlier SQL 6.5 setup. I see from the format file, the column delimiter is "" and row delimiter is
. Am I missing something here ? Also, I tried using bcp for another data file and I get the following error SQLState = S1002, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index Do this an issue with the ODBC ? Please let me know if anybody has faced this issues, as its very critical. Thanks in advance.

Did you update ODBC after migration?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Well, am not too sure. Migration was done a new server running windows 2003 and SQL Server 2000. I need to check the version in ODBC Data source Administration (from control panel). The version it says 3.525.1022.0. Please let me know if I need to update the ODBC driver. Thanks so much for the quick reply

Hi, I guess SQL server service pack 3a was installed. Any pointers ?
The same format file and data file works on a different test server running 6.5 Any help is highly appreciated.

At home now, I don’t remember ODBC version, but you can install last version from Microsoft and try. http://www.microsoft.com/downloads/…e3-c795-4b7d-b037-185d0506396c&DisplayLang=en Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks for the link. I will try installing the MDAC tomorrow. But I strongly suspect SQL_CHAR (truncation)behaviour. We migrated this database from 6.5 to 2000 using two computer upgrade. And the table which am loading has columns with only CHAR datatype. Is it that 6.5 truncates the data on the right without warnings if the data is too long to fit into the columns ? I tried setting the ANSI_WARNINGS OFF. But still didnt work. Is there any other work around ? Thanks,

It was reported this error was fixed in SQL 2000 SP3a and try to reapply it. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi, Thanks for the suggestion. I will reapply the SP3a. The serverproperty(‘productlevel’) shows as SP3. When I gave the Native Unicode Data format -n in the bcp command, the loading works fine. But the data in the table doesnt look good. The bcp is inserting the new line character (row delimiter) as a record in the table. So I find empty spaces between records in the table. Please let me know if you get any hints out of this.

Storing information in Unicode native format is useful when information is to be copied from one computer running SQL Server to another. Using native format for noncharacter data saves time, preventing unnecessary conversion of data types to and from character format. Using Unicode character format for all character data prevents loss of any extended characters when bulk loading data between servers using different code pages (character loss is possible if extended characters are copied into non-Unicode columns if the extended character cannot be represented). I believe re-applying SP3a would solve the problem. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya, That was very informative. I reapplied the SP3a, but no luck Infact, I found that the .dat file in not in ANSI format. The rowdelimiter supposed to be
, displays as special characters when I open in text pad. The rowdelimiter is displayed as ||||||| How will I load such type of files to a table. I checked the collation settings, sort order is 52 and code page is 1252.

Copy and paste ||||||| over the current delimiter in your format file then. Have you tried that yet? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Hi, No, didnt work. I loaded in Native format and then wrote a small PL/SQL code to re-format the data and it works. Thanks again for all your suggestions.

Have you tried replacing the MDAC version with latest SP? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>