Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

BCP fails on new host/version

4 posts in General Discussion Last posting was on 2011-02-12 07:26:15.0Z
Jay Williams Posted on 2011-02-09 16:45:42.0Z
From: "Jay Williams" <jwillia@cvps.com>
Newsgroups: sybase.public.ase.general
Subject: BCP fails on new host/version
Lines: 79
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d52c4b6$1@forums-1-dub>
Date: 9 Feb 2011 08:45:42 -0800
X-Trace: forums-1-dub 1297269942 10.22.241.152 (9 Feb 2011 08:45:42 -0800)
X-Original-Trace: 9 Feb 2011 08:45:42 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29946
Article PK: 79179

Hi, we are in the middle of a Sybase upgrade moving from version 12.5.4
(AIX) to version 15.0.3 (Linux). During testing one of our BCP scripts is
failing on the new host/version and I cannot figure out why. I have read
through the BCP manual but I can't find a solution. I am not a BCP expert
and was not the original author of this script or format file. The same
file loads fine in the AIX/12.5.4 environment. This file is FTP'ed to the
Sybase server from mainframe. Thanks for any help! Here are all the
details (sorry there are lots of them):

Old version: Adaptive Server Enterprise/12.5.4/EBF 14651 ESD#6/P/RS6000/AIX
4.3.3/ase1254/2079/32-bit/FBO/Wed Jul 11 06:20:44 2007
New version: Adaptive Server Enterprise/15.0.3/EBF 17157
ESD#3/P/x86_64/Enterprise Linux/ase1503/2726/64-bit/FBO/Fri Feb 5 01:42:20
2010

Script:
bcp elecserv.dbo.socket in
socket1.txt -S<server> -U<user> -P<pass> -fsocket.fmt -e
err.txt -A16384 -b1 -m100000 -Y
(I had to add the -Y flags to all of my BCP in scripts...this is the only
change made)

Format File socket.fmt:
10.0
11
1 SYBCHAR 0 2 "\t" 1 DIST_NUM
2 SYBCHAR 0 6 "\t" 2 PREMISE_NUM
3 SYBCHAR 0 5 "\t" 3 SOCKET_SEQ
4 SYBCHAR 0 1 "\t" 4 SOCKET_STATUS
5 SYBCHAR 0 1 "\t" 5 PULSE_ONLY_IND
6 SYBCHAR 0 10 "\t" 6 INACTIVE_DATE8
7 SYBCHAR 0 2 "\t" 7 METER_LOCATION_CD
8 SYBCHAR 0 10 "\t" 8 FAC_ID
9 SYBCHAR 0 10 "\t" 9 PROTECTIVE_FAC_ID
10 SYBCHAR 0 10 "\t" 10 PROTECTIVE_PROCESS_DATE
11 SYBCHAR 0 10 "\n" 11 METER_READ_SEQ_NUM
12 SYBCHAR 0 10 "" 0 METERS_TO_PROTECT
13 SYBCHAR 0 10 "" 0 PROTECT_UPD_IND
14 SYBCHAR 0 10 "" 0 FID
15 SYBCHAR 0 10 "" 0 PROTECTIVE_FID

Table structure:
CREATE TABLE SOCKET (
DIST_NUM char(2) NOT NULL,
PREMISE_NUM char(6) NOT NULL,
SOCKET_SEQ smallint NOT NULL,
SOCKET_STATUS char(1) NOT NULL,
PULSE_ONLY_IND char(1) NOT NULL,
INACTIVE_DATE8 datetime NOT NULL,
METER_LOCATION_CD char(2) NOT NULL,
FAC_ID int NOT NULL,
PROTECTIVE_FAC_ID int NOT NULL,
PROTECTIVE_PROCESS_DATE datetime NULL,
METER_READ_SEQ_NUM int NOT NULL,
METERS_TO_PROTECT int DEFAULT CREATE DEFAULT D_0 AS 0 NOT NULL,
PROTECT_UPD_IND char(10) DEFAULT create default DD_NONE as 'NONE' NOT
NULL,
FID numeric(10,0) DEFAULT CREATE DEFAULT D_0 AS 0 NOT NULL,
PROTECTIVE_FID numeric(10,0) DEFAULT CREATE DEFAULT D_0 AS 0NOT NULL
)

File data socket1.txt:
01 000001 02 21 0000000
0000000 000010

Here is the file data shown in Vi's :set list mode:
01^I000001^I02^I ^I ^I ^I21^I0000000^I0000000^I ^I000010$

The error in err.txt is:
#@ Row 1, Column 11: CSLIB Message: - L0/O0/S0/N24/1/0:
cs_convert: cslib user api layer: common library error: The
conversion/operation was stopped due to a syntax error in the source field.
@#
#@ Row 1: Not transferred @#
01 000001 02 21 0000000
0000000 000010


"Mark A. Parsons" <iron_horse Posted on 2011-02-09 21:35:14.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: BCP fails on new host/version
References: <4d52c4b6$1@forums-1-dub>
In-Reply-To: <4d52c4b6$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d530892$1@forums-1-dub>
Date: 9 Feb 2011 13:35:14 -0800
X-Trace: forums-1-dub 1297287314 10.22.241.152 (9 Feb 2011 13:35:14 -0800)
X-Original-Trace: 9 Feb 2011 13:35:14 -0800, vip152.sybase.com
Lines: 109
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29950
Article PK: 79177

I created the table in a 15.0.3 dataserver, created a socket1.txt file to match yours (for the most part - only used
single spaces), and got the bcp to work without a problem. [of course, eh?]

I'm leaning towards a problem with the data file. Perhaps a different row delimiter than what your format file was
designed for.

Is the file ftp'd in binary mode from the mainframe down to the linux box?

Is the file ftp'd directly from the mainframe to the linux box, or does it make a stop along the way?

Can you post the results of running both data files (AIX and linux) through 'od -c'? Here's what mine looks like:

=============================
$ od -c socket1.txt
0000000 0 1 \t 0 0 0 0 0 1 \t 0 2 \t \t
0000020 \t \t 2 1 \t 0 0 0 0 0 0 0 \t 0 0
0000040 0 0 0 0 0 \t \t 0 0 0 0 1 0 \n
0000057
=============================

NOTE: I created the data file, and ran my bcp tests, from a cygwin session on a Windows box ... hence the unix
formatting of my data file.

Another test that might be of interest ... use vi to create a small ascii text file (eg, tt.txt) on both machines, with
a couple lines of tab-separated values on each line. Then run both files through 'od -c'. Objective is to see if
you're getting different characters in the end-of-line position.

On 02/09/2011 11:45, Jay Williams wrote:
> Hi, we are in the middle of a Sybase upgrade moving from version 12.5.4
> (AIX) to version 15.0.3 (Linux). During testing one of our BCP scripts is
> failing on the new host/version and I cannot figure out why. I have read
> through the BCP manual but I can't find a solution. I am not a BCP expert
> and was not the original author of this script or format file. The same
> file loads fine in the AIX/12.5.4 environment. This file is FTP'ed to the
> Sybase server from mainframe. Thanks for any help! Here are all the
> details (sorry there are lots of them):
>
> Old version: Adaptive Server Enterprise/12.5.4/EBF 14651 ESD#6/P/RS6000/AIX
> 4.3.3/ase1254/2079/32-bit/FBO/Wed Jul 11 06:20:44 2007
> New version: Adaptive Server Enterprise/15.0.3/EBF 17157
> ESD#3/P/x86_64/Enterprise Linux/ase1503/2726/64-bit/FBO/Fri Feb 5 01:42:20
> 2010
>
> Script:
> bcp elecserv.dbo.socket in
> socket1.txt -S<server> -U<user> -P<pass> -fsocket.fmt -e
> err.txt -A16384 -b1 -m100000 -Y
> (I had to add the -Y flags to all of my BCP in scripts...this is the only
> change made)
>
> Format File socket.fmt:
> 10.0
> 11
> 1 SYBCHAR 0 2 "\t" 1 DIST_NUM
> 2 SYBCHAR 0 6 "\t" 2 PREMISE_NUM
> 3 SYBCHAR 0 5 "\t" 3 SOCKET_SEQ
> 4 SYBCHAR 0 1 "\t" 4 SOCKET_STATUS
> 5 SYBCHAR 0 1 "\t" 5 PULSE_ONLY_IND
> 6 SYBCHAR 0 10 "\t" 6 INACTIVE_DATE8
> 7 SYBCHAR 0 2 "\t" 7 METER_LOCATION_CD
> 8 SYBCHAR 0 10 "\t" 8 FAC_ID
> 9 SYBCHAR 0 10 "\t" 9 PROTECTIVE_FAC_ID
> 10 SYBCHAR 0 10 "\t" 10 PROTECTIVE_PROCESS_DATE
> 11 SYBCHAR 0 10 "\n" 11 METER_READ_SEQ_NUM
> 12 SYBCHAR 0 10 "" 0 METERS_TO_PROTECT
> 13 SYBCHAR 0 10 "" 0 PROTECT_UPD_IND
> 14 SYBCHAR 0 10 "" 0 FID
> 15 SYBCHAR 0 10 "" 0 PROTECTIVE_FID
>
> Table structure:
> CREATE TABLE SOCKET (
> DIST_NUM char(2) NOT NULL,
> PREMISE_NUM char(6) NOT NULL,
> SOCKET_SEQ smallint NOT NULL,
> SOCKET_STATUS char(1) NOT NULL,
> PULSE_ONLY_IND char(1) NOT NULL,
> INACTIVE_DATE8 datetime NOT NULL,
> METER_LOCATION_CD char(2) NOT NULL,
> FAC_ID int NOT NULL,
> PROTECTIVE_FAC_ID int NOT NULL,
> PROTECTIVE_PROCESS_DATE datetime NULL,
> METER_READ_SEQ_NUM int NOT NULL,
> METERS_TO_PROTECT int DEFAULT CREATE DEFAULT D_0 AS 0 NOT NULL,
> PROTECT_UPD_IND char(10) DEFAULT create default DD_NONE as 'NONE' NOT
> NULL,
> FID numeric(10,0) DEFAULT CREATE DEFAULT D_0 AS 0 NOT NULL,
> PROTECTIVE_FID numeric(10,0) DEFAULT CREATE DEFAULT D_0 AS 0NOT NULL
> )
>
> File data socket1.txt:
> 01 000001 02 21 0000000
> 0000000 000010
>
> Here is the file data shown in Vi's :set list mode:
> 01^I000001^I02^I ^I ^I ^I21^I0000000^I0000000^I ^I000010$
>
> The error in err.txt is:
> #@ Row 1, Column 11: CSLIB Message: - L0/O0/S0/N24/1/0:
> cs_convert: cslib user api layer: common library error: The
> conversion/operation was stopped due to a syntax error in the source field.
> @#
> #@ Row 1: Not transferred @#
> 01 000001 02 21 0000000
> 0000000 000010
>
>
>


Jay Williams Posted on 2011-02-10 13:21:47.0Z
From: "Jay Williams" <jwillia@cvps.com>
Newsgroups: sybase.public.ase.general
References: <4d52c4b6$1@forums-1-dub> <4d530892$1@forums-1-dub>
Subject: Re: BCP fails on new host/version
Lines: 142
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d53e66b@forums-1-dub>
Date: 10 Feb 2011 05:21:47 -0800
X-Trace: forums-1-dub 1297344107 10.22.241.152 (10 Feb 2011 05:21:47 -0800)
X-Original-Trace: 10 Feb 2011 05:21:47 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29952
Article PK: 79181

Mark, thank you! I truly appreciate you taking the time to do all of that
testing.

The od -c command was a lifesaver, what I found is that the file coming from
the mainframe actually has a carriage return and a newline as the line
delimeter (\r\n). I verified that the file did not pass through Windows,
that it was straight from MF to to Linux. Seems odd that the carriage
return would be there, but that is besides the point I guess. Must be that
the old version of BCP we were using (12.5.1 according to "bcp -v") ignored
the carriage return but the new version (15.0) choked on it, probably
because the last field is of type int.

I changed the format file to have a delimiter of \r\n and the file loaded
fine.

Thanks again!

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4d530892$1@forums-1-dub...
>I created the table in a 15.0.3 dataserver, created a socket1.txt file to
>match yours (for the most part - only used single spaces), and got the bcp
>to work without a problem. [of course, eh?]
>
> I'm leaning towards a problem with the data file. Perhaps a different row
> delimiter than what your format file was designed for.
>
> Is the file ftp'd in binary mode from the mainframe down to the linux box?
>
> Is the file ftp'd directly from the mainframe to the linux box, or does it
> make a stop along the way?
>
> Can you post the results of running both data files (AIX and linux)
> through 'od -c'? Here's what mine looks like:
>
> =============================
> $ od -c socket1.txt
> 0000000 0 1 \t 0 0 0 0 0 1 \t 0 2 \t \t
> 0000020 \t \t 2 1 \t 0 0 0 0 0 0 0 \t 0 0
> 0000040 0 0 0 0 0 \t \t 0 0 0 0 1 0 \n
> 0000057
> =============================
>
> NOTE: I created the data file, and ran my bcp tests, from a cygwin
> session on a Windows box ... hence the unix formatting of my data file.
>
> Another test that might be of interest ... use vi to create a small ascii
> text file (eg, tt.txt) on both machines, with a couple lines of
> tab-separated values on each line. Then run both files through 'od -c'.
> Objective is to see if you're getting different characters in the
> end-of-line position.
>
>
>
> On 02/09/2011 11:45, Jay Williams wrote:
>> Hi, we are in the middle of a Sybase upgrade moving from version 12.5.4
>> (AIX) to version 15.0.3 (Linux). During testing one of our BCP scripts
>> is
>> failing on the new host/version and I cannot figure out why. I have read
>> through the BCP manual but I can't find a solution. I am not a BCP
>> expert
>> and was not the original author of this script or format file. The same
>> file loads fine in the AIX/12.5.4 environment. This file is FTP'ed to
>> the
>> Sybase server from mainframe. Thanks for any help! Here are all the
>> details (sorry there are lots of them):
>>
>> Old version: Adaptive Server Enterprise/12.5.4/EBF 14651
>> ESD#6/P/RS6000/AIX
>> 4.3.3/ase1254/2079/32-bit/FBO/Wed Jul 11 06:20:44 2007
>> New version: Adaptive Server Enterprise/15.0.3/EBF 17157
>> ESD#3/P/x86_64/Enterprise Linux/ase1503/2726/64-bit/FBO/Fri Feb 5
>> 01:42:20
>> 2010
>>
>> Script:
>> bcp elecserv.dbo.socket in
>> socket1.txt -S<server> -U<user> -P<pass> -fsocket.fmt -e
>> err.txt -A16384 -b1 -m100000 -Y
>> (I had to add the -Y flags to all of my BCP in scripts...this is the only
>> change made)
>>
>> Format File socket.fmt:
>> 10.0
>> 11
>> 1 SYBCHAR 0 2 "\t" 1 DIST_NUM
>> 2 SYBCHAR 0 6 "\t" 2 PREMISE_NUM
>> 3 SYBCHAR 0 5 "\t" 3 SOCKET_SEQ
>> 4 SYBCHAR 0 1 "\t" 4 SOCKET_STATUS
>> 5 SYBCHAR 0 1 "\t" 5 PULSE_ONLY_IND
>> 6 SYBCHAR 0 10 "\t" 6 INACTIVE_DATE8
>> 7 SYBCHAR 0 2 "\t" 7 METER_LOCATION_CD
>> 8 SYBCHAR 0 10 "\t" 8 FAC_ID
>> 9 SYBCHAR 0 10 "\t" 9 PROTECTIVE_FAC_ID
>> 10 SYBCHAR 0 10 "\t" 10 PROTECTIVE_PROCESS_DATE
>> 11 SYBCHAR 0 10 "\n" 11 METER_READ_SEQ_NUM
>> 12 SYBCHAR 0 10 "" 0 METERS_TO_PROTECT
>> 13 SYBCHAR 0 10 "" 0 PROTECT_UPD_IND
>> 14 SYBCHAR 0 10 "" 0 FID
>> 15 SYBCHAR 0 10 "" 0 PROTECTIVE_FID
>>
>> Table structure:
>> CREATE TABLE SOCKET (
>> DIST_NUM char(2) NOT NULL,
>> PREMISE_NUM char(6) NOT NULL,
>> SOCKET_SEQ smallint NOT NULL,
>> SOCKET_STATUS char(1) NOT NULL,
>> PULSE_ONLY_IND char(1) NOT NULL,
>> INACTIVE_DATE8 datetime NOT NULL,
>> METER_LOCATION_CD char(2) NOT NULL,
>> FAC_ID int NOT NULL,
>> PROTECTIVE_FAC_ID int NOT NULL,
>> PROTECTIVE_PROCESS_DATE datetime NULL,
>> METER_READ_SEQ_NUM int NOT NULL,
>> METERS_TO_PROTECT int DEFAULT CREATE DEFAULT D_0 AS 0 NOT NULL,
>> PROTECT_UPD_IND char(10) DEFAULT create default DD_NONE as 'NONE' NOT
>> NULL,
>> FID numeric(10,0) DEFAULT CREATE DEFAULT D_0 AS 0 NOT NULL,
>> PROTECTIVE_FID numeric(10,0) DEFAULT CREATE DEFAULT D_0 AS 0NOT NULL
>> )
>>
>> File data socket1.txt:
>> 01 000001 02 21 0000000
>> 0000000 000010
>>
>> Here is the file data shown in Vi's :set list mode:
>> 01^I000001^I02^I ^I ^I ^I21^I0000000^I0000000^I
>> ^I000010$
>>
>> The error in err.txt is:
>> #@ Row 1, Column 11: CSLIB Message: - L0/O0/S0/N24/1/0:
>> cs_convert: cslib user api layer: common library error: The
>> conversion/operation was stopped due to a syntax error in the source
>> field.
>> @#
>> #@ Row 1: Not transferred @#
>> 01 000001 02 21 0000000
>> 0000000 000010
>>
>>
>>


Michael Peppler [Team Sybase] Posted on 2011-02-12 07:26:15.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: BCP fails on new host/version
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2011.02.12.07.26.12.640606@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4d52c4b6$1@forums-1-dub> <4d530892$1@forums-1-dub> <4d53e66b@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 11 Feb 2011 23:26:15 -0800
X-Trace: forums-1-dub 1297495575 10.22.241.152 (11 Feb 2011 23:26:15 -0800)
X-Original-Trace: 11 Feb 2011 23:26:15 -0800, vip152.sybase.com
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29962
Article PK: 79190

Was the FTP done in binary mode?

On Thu, 10 Feb 2011 05:21:47 -0800, Jay Williams wrote:

> Mark, thank you! I truly appreciate you taking the time to do all of that
> testing.
>
> The od -c command was a lifesaver, what I found is that the file coming
> from the mainframe actually has a carriage return and a newline as the
> line delimeter (\r\n). I verified that the file did not pass through
> Windows, that it was straight from MF to to Linux. Seems odd that the
> carriage return would be there, but that is besides the point I guess.
> Must be that the old version of BCP we were using (12.5.1 according to
> "bcp -v") ignored the carriage return but the new version (15.0) choked on
> it, probably because the last field is of type int.