bcp copy in failed
Eugene
Our client started to getting that message at the end of bcp on QA on
any file, though actually it is successful, the same configuration on
production does not produce that error.
ASE Server:
> select @@version
> Adaptive Server Enterprise/15.0.3/EBF 16548 ESD#1/P/Sun_svr4/OS 5.8/ase1503/2680/64-bit/FBO/Thu Mar 5 09:39:28 2009
bcp version:
> bcp -v
> Sybase CTBCP Utility/12.5.1/P/DRV.12.5.1.0/SPARC/Solaris 2.8/BUILD1251-004/OPT/Mon Nov 10 12:02:24 2003
Any ideas ?
Regards,
Eugene
Mark A. Parsons
- bcp version string (bcp -v) used for each bcp operation ?
- actual bcp commands used against the 2 dataservers ?
- actual/complete bcp errors ?
- confirmation that *all* data is bcp'd into the table (and in the correct columns) ?
- any differences in the table schemas in the 2 dataservers ?
Eugene
>> - bcp version string (bcp -v) used for each bcp operation ?
See in prev.email
>> actual bcp commands used against the 2 dataservers ?
> bcp margins_yest..fxrates in tmp1.txt -c -U<user> -P<user_pwd> -S<server_name>
I do not think it is related to the file or/and table.
> Starting copy...
> bcp copy in failed
> bcp margins_yest..fxrates in tmp1.txt -c -b1 -U<user> -P<user_pwd> -S<server_name>
> Starting copy...
> Batch successfully bulk-copied to SQL Server.
> bcp copy in failed
Table (for example):
> CREATE TABLE dbo.fxrates
> (
> ccy_code char(3) NOT NULL,
> mult_div_ind char(1) NOT NULL,
> fx_rate float NOT NULL,
> market char(2) NOT NULL,
> last_update_time char(12) NULL
> )
> LOCK ALLPAGES
> go
tmp1.txt - has just 1 row
> od -c tmp1.txt
> 0000000 T R Y \t M \t 0 . 6 7 2 2 7 0 0 0
> 0000020 \t \t 0 \n
> 0000025
>> - actual/complete bcp errors ?
This is most tricky- no actual errors, everything went OK.
(not in stdout, not in err.file)
>> - confirmation that *all* data is bcp'd into the table (and in the
>> correct columns) ?
> select * from fxrates where ccy_code="TRY"
> go
> TRY M 0.67227 0
>> - any differences in the table schemas in the 2 dataservers ?
No
HTH,
Eugene
Neal Stack [Sybase]
What is the server's character set (sp_helpsort)?
What is the client's character set (select @@client_csname)?
What is the DDL for the table?
If I have the above information, I will give it a try with the sample data you listed in your email.
Regards,
Neal
Eugene
1.
> Sort Order Description
>
> ------------------------------------------------------------------
> Character Set = 1, iso_1
> ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 50, bin_iso_1
> Binary ordering, for the ISO 8859/1 or Latin-1 character set (
> iso_1).
> Characters, in Order
>
> ------------------------------------------------------------------
> ! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
> @ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _
> ` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~
> � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
> � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
> � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
>
> (return status = 0)
2.
> 1> select @@client_csname
> 2> go
>
> ------------------------------
> iso_1
>
> (1 row affected)
3. See prev.email
HTH,
Eugene
J
Does a more recent version of bcp give you the same problem? Are you
positive that the qa and production versions are the same?
J
Neal Stack [Sybase]
It seems to work for me using the exact same version of bcp and nearly the same version of ASE.
My version of ASE only has one additional fix related to CIS so that should not make a difference.
Does your "sp_help fxrates" output show any differences from mine such as float scale/precision or indexes?
% od -c fxrates.dat
0000000 T R Y \t M \t 0 . 6 7 2 2 7 0 0 0
0000020 \t \t 0 \n
0000025
% ./bcp -v
Sybase CTBCP Utility/12.5.1/P/DRV.12.5.1.0/SPARC/Solaris 2.8/BUILD1251-004/OPT/Mon Nov 10 12:02:24 2003
% ./bcp tempdb..fxrates in fxrates.dat -Usa -P -c
Starting copy...
1 rows copied.
Clock Time (ms.): total = 1 Avg = 1 (1000.00 rows per sec.)
% isql -Usa -P -D tempdb -w 132
1> select @@version
2> select * from fxrates
3> go
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Adaptive Server Enterprise/15.0.3/EBF 16919 ESD#1 ONE-OFF/P/Sun_svr4/OS 5.8/ase1503/2681/64-bit/FBO/Tue May 12 18:08:30 2009
(1 row affected)
ccy_code mult_div_ind fx_rate market last_update_time
-------- ------------ -------------------- ------ ----------------
TRY M 0.672270 0
(1 row affected)
1> sp_help fxrates
2> go
Name Owner Object_type Create_date
------- ----- ----------- -------------------
fxrates dbo user table Dec 1 2009 9:22AM
(1 row affected)
Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
---------------- ----- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
ccy_code char 3 NULL NULL 0 NULL NULL NULL NULL 0
mult_div_ind char 1 NULL NULL 0 NULL NULL NULL NULL 0
fx_rate float 8 NULL NULL 0 NULL NULL NULL NULL 0
market char 2 NULL NULL 0 NULL NULL NULL NULL 0
last_update_time char 12 NULL NULL 1 NULL NULL NULL NULL 0
Object does not have any indexes.
No defined keys for this object.
Regards,
Neal
Eugene
It works for me either, it works on client production,
it worked fine on their QA till Oct.13 (!:-)) when they
reloaded schema/data from their production
(it was 12.5.3 at that moment) to QA (was 15.0.3 already)
and then it is started producing that during SOD (start of day) load
data to our system.
It even actually works fine on their QA now, except
producing msg 'bcp copy in failed' (without any details)
at the end of bcp in.
So far my advice to client was to drop/recreate indexes or/and dbcc
Example table has 2 add.indexes (see below), but thing is not related
to particular table/file input, it is happened on any loaded
during start of day file (8 files/tables)
HTH,
Eugene
> CREATE UNIQUE CLUSTERED INDEX index1
> ON dbo.fxrates(ccy_code)
> WITH IGNORE_DUP_KEY
> ON ds01_sgmt
> go
> IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.fxrates') AND name='index1')
> PRINT '<<< CREATED INDEX dbo.fxrates.index1 >>>'
> ELSE
> PRINT '<<< FAILED CREATING INDEX dbo.fxrates.index1 >>>'
> go
> CREATE INDEX index2
> ON dbo.fxrates(market,ccy_code)
> ON is01_sgmt
> go
> IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.fxrates') AND name='index2')
> PRINT '<<< CREATED INDEX dbo.fxrates.index2 >>>'
> ELSE
> PRINT '<<< FAILED CREATING INDEX dbo.fxrates.index2 >>>'
> go
Eugene
I have asked client to upgrade to more recent version,
but it is the same on their production and moreover
worked fine in the past (like 1,5 months).
Looks like the same, but definitely something changed,
I am suspecting that problem arose after client reloaded
data from production (at that time 12.5.3/4) to qa (15.0.3)
HTH,
Eugene
Neal Stack [Sybase]
Do all 8 tables have a nullable final column?
Are they using the "-e errfile.txt" parameter when copying in (I didn't see that parameter in your email)?
Regards,
Neal
Eugene
1. No
2. No
3. Does not matter, there are no errors anyway
There no "problematic" tables, it is just only 8 of them getting loaded,
but I can take any table and I just did with small "dictionary/constant
" table 'account_type' (2 columns, 3 recs, all chars) did bcp out (OK),
then deleted all 3 recs, did bcp in (OK, but again msg "bcp copy in
failed" at the end).
I think something wrong with database. I am asking hoping that somebody
had the same or knows something, cause I already researched solved
cases, newsgroups, EBFS bug fixes, did open case, so far nothing.
I am not expecting too much from newsgroup or/and TS, just maybe
somebody had that puzzle in the past ?
Again I think it is started when they loaded data and for some reason
copied over using defncopy views, sps from prod (12.5.3/4 at that
moment) to qa (15.0.3, though it seems to me that they had something
like this long time ago (without any 15.0.3)
I suggested to to do dbcc, or/and drop/recreate indexes ( I cannot do it
on their site, only bcp in/out, insert/delete/select/update, even
truncate or/and getting ASE log not possible, cause I am not the owner
or sa_role).
HTH,
Eugene