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.

Problem with bcp during transfer data from MS to SYBASE.

4 posts in Windows NT Last posting was on 1998-02-20 06:57:03.0Z
Vitaly Lipovetsky Posted on 1998-02-14 11:17:37.0Z
From: "Vitaly Lipovetsky" <vit@fuib.com>
Subject: Problem with bcp during transfer data from MS to SYBASE.
Date: Sat, 14 Feb 1998 13:17:37 +0200
Lines: 56
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Message-ID: <34e57c56.0@ifuib>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com!ifuib!172.17.3.101
Xref: forums-1-dub sybase.public.sqlserver.nt:5175
Article PK: 1081255

I migrating from MS SQL 6.5 to ASE 11.5.

tp_id is int in MS and numeric(12,0) in syb.

I have a table :

CREATE TABLE tb_accntab2 (
accn_id tp_id NOT NULL ,
cnum decimal(6, 0) NOT NULL ,
ccy char (3) NOT NULL ,
acod decimal(4, 0) NOT NULL ,
acsq decimal(2, 0) NOT NULL ,
brca char (3) NOT NULL ,
acno decimal(10, 0) NOT NULL ,
daco smalldatetime NOT NULL ,
dacc smalldatetime NOT NULL ,
lmvd smalldatetime NOT NULL ,
dldbl decimal(22, 2) NOT NULL ,
cldbl decimal(22, 2) NOT NULL ,
logtime datetime NOT NULL ,
dturn decimal(22, 2) NOT NULL ,
cturn decimal(22, 2) NOT NULL ,
status tinyint NOT NULL ,
lstp decimal(3, 0) NOT NULL ,
lisp decimal(3, 0) NOT NULL ,
namer varchar (50) NULL ,
nbuaccntab_id tp_id NULL ,
mtype tinyint NULL
)

I download data from MS via cmd.
out.cmd
c:\MSSQL\BINN\bcp FUIB_02..%1 out ..\BCP\%1.BCP -SSQL2-SVR -T -c -r \0
>..\LOG_OUT\%1.log

and upload to SYB
via
in.cmd
%SYBASE%\bin\bcp FUIB_TEST..%1 in
..\BCP\%1.bcp -SBACKUP-SVR -c -Ums2syb_user -Pms2syb_use
%3 -J -m1 -b10000 -r \0 %2 > ..\LOG_IN\%1.log

After transfer I find in some rows column nbuaccntab_id < 0 with the same
value -51539607552
But MS table don't have negative values in nbuaccntab_id.

I tried to transfer 1 incorrect row via view,
and nbuaccntab_id loaded correctly. ????

Any suggestions


Reinoud van Leeuwen Posted on 1998-02-19 11:07:56.0Z
Message-ID: <34EC128C.49E1@sybase.com>
Date: Thu, 19 Feb 1998 12:07:56 +0100
From: Reinoud van Leeuwen <reinoud@sybase.com>
Organization: Sybase Inc.
X-Mailer: Mozilla 3.0 (Win95; I)
MIME-Version: 1.0
To: Vitaly Lipovetsky <vit@fuib.com>
Subject: Re: Problem with bcp during transfer data from MS to SYBASE.
References: <34e57c56.0@ifuib>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 48
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5174
Article PK: 1081251


Vitaly Lipovetsky wrote:
>
> I migrating from MS SQL 6.5 to ASE 11.5.
>
> tp_id is int in MS and numeric(12,0) in syb.
>
> I have a table :
>
> CREATE TABLE tb_accntab2 (
> accn_id tp_id NOT NULL ,
> cnum decimal(6, 0) NOT NULL ,
> ccy char (3) NOT NULL ,
> acod decimal(4, 0) NOT NULL ,
> acsq decimal(2, 0) NOT NULL ,
> brca char (3) NOT NULL ,
> acno decimal(10, 0) NOT NULL ,
> daco smalldatetime NOT NULL ,
> dacc smalldatetime NOT NULL ,
> lmvd smalldatetime NOT NULL ,
> dldbl decimal(22, 2) NOT NULL ,
> cldbl decimal(22, 2) NOT NULL ,
> logtime datetime NOT NULL ,
> dturn decimal(22, 2) NOT NULL ,
> cturn decimal(22, 2) NOT NULL ,
> status tinyint NOT NULL ,
> lstp decimal(3, 0) NOT NULL ,
> lisp decimal(3, 0) NOT NULL ,
> namer varchar (50) NULL ,
> nbuaccntab_id tp_id NULL ,
> mtype tinyint NULL
> )
>
> I download data from MS via cmd.
> out.cmd
> c:\MSSQL\BINN\bcp FUIB_02..%1 out ..\BCP\%1.BCP -SSQL2-SVR -T -c -r \0
> >..\LOG_OUT\%1.log
>
> and upload to SYB
> via
> in.cmd
> %SYBASE%\bin\bcp FUIB_TEST..%1 in
> ..\BCP\%1.bcp -SBACKUP-SVR -c -Ums2syb_user -Pms2syb_use
> %3 -J -m1 -b10000 -r \0 %2 > ..\LOG_IN\%1.log
>
> After transfer I find in some rows column nbuaccntab_id < 0 with the same
> value -51539607552
> But MS table don't have negative values in nbuaccntab_id.
>
> I tried to transfer 1 incorrect row via view,
> and nbuaccntab_id loaded correctly. ????

BCP doesn't do much error checking or conversions. It works best when
the two tables are identical (both names and datatypes and order of
collumns). This explains why the bcp out from a view can work correctly.
I assume the view has something like
select (all fields), tp_id = converert (numeric (12,0), tp_id) from
source_table

Reinoud van Leeuwen
Sybase Professional Services Maarssen / The Netherlands


John McVicker Posted on 1998-02-19 15:02:01.0Z
From: "John McVicker" <mcvicker@sybase.com>
Organization: 192.175.209.26
References: <34e57c56.0@ifuib> <34EC128C.49E1@sybase.com>
X-Newsreader: AspNNTP (Advent 2000, Inc.)
Subject: Re: Problem with bcp during transfer data from MS to SYBASE.
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <NgJCVdUP9GA.129@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Date: Thu, 19 Feb 1998 10:02:01 -0500
Lines: 75
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5173
Article PK: 1081252

Vitaly,

>> lisp decimal(3, 0) NOT NULL ,
>> namer varchar (50) NULL ,
>> nbuaccntab_id tp_id NULL ,
>> mtype tinyint NULL

namer may have "tabs" in them. If so, this is messing up your value in
the nbuaccntab_id BCP in. When you BCP out, use a column
delimiter that is in no way possible in the namer column. You can use
multi-character terminators. Also, I wouldn't use \0 for row terminators,
I would use \n and if you need to FTP to another host, just use ASCII
mode of FTP.
Here's something to try:

c:\MSSQL\BINN\bcp FUIB_02..%1 out ..\BCP\%1.BCP -SSQL2-SVR -T -c -t^%^ -r\n
.\LOG_OUT\%1.log

%SYBASE%\bin\bcp FUIB_TEST..%1 in
.\BCP\%1.bcp -SBACKUP-SVR -c -Ums2syb_user -Pms2syb_use
%3 -J -m1 -b10000 -t^%^ -r\n %2 > ..\LOG_IN\%1.log

The string ^%^ will delimit columns - should be quite unique. Choose
as appropriate.

On Thu, 19 Feb 1998 12:07:56 +0100,
in sybase.public.sqlserver.nt
Reinoud van Leeuwen <reinoud@sybase.com> wrote:
>Vitaly Lipovetsky wrote:
>>
>> I migrating from MS SQL 6.5 to ASE 11.5.
>>
>> tp_id is int in MS and numeric(12,0) in syb.
>>
>> I have a table :
>>
>> CREATE TABLE tb_accntab2 (
>> accn_id tp_id NOT NULL ,
>> cnum decimal(6, 0) NOT NULL ,
>> ccy char (3) NOT NULL ,
>> acod decimal(4, 0) NOT NULL ,
>> acsq decimal(2, 0) NOT NULL ,
>> brca char (3) NOT NULL ,
>> acno decimal(10, 0) NOT NULL ,
>> daco smalldatetime NOT NULL ,
>> dacc smalldatetime NOT NULL ,
>> lmvd smalldatetime NOT NULL ,
>> dldbl decimal(22, 2) NOT NULL ,
>> cldbl decimal(22, 2) NOT NULL ,
>> logtime datetime NOT NULL ,
>> dturn decimal(22, 2) NOT NULL ,
>> cturn decimal(22, 2) NOT NULL ,
>> status tinyint NOT NULL ,
>> lstp decimal(3, 0) NOT NULL ,
>> lisp decimal(3, 0) NOT NULL ,
>> namer varchar (50) NULL ,
>> nbuaccntab_id tp_id NULL ,
>> mtype tinyint NULL
>> )
>>
>> I download data from MS via cmd.
>> out.cmd
>> c:\MSSQL\BINN\bcp FUIB_02..%1 out ..\BCP\%1.BCP -SSQL2-SVR -T -c -r \0
>> >..\LOG_OUT\%1.log
>>
>> and upload to SYB
>> via
>> in.cmd
>> %SYBASE%\bin\bcp FUIB_TEST..%1 in
>> ..\BCP\%1.bcp -SBACKUP-SVR -c -Ums2syb_user -Pms2syb_use
>> %3 -J -m1 -b10000 -r \0 %2 > ..\LOG_IN\%1.log
>>
>> After transfer I find in some rows column nbuaccntab_id < 0 with the same
>> value -51539607552
>> But MS table don't have negative values in nbuaccntab_id.
>>
>> I tried to transfer 1 incorrect row via view,
>> and nbuaccntab_id loaded correctly. ????
>
>BCP doesn't do much error checking or conversions. It works best when
>the two tables are identical (both names and datatypes and order of
>collumns). This explains why the bcp out from a view can work correctly.
>I assume the view has something like
>select (all fields), tp_id = converert (numeric (12,0), tp_id) from
>source_table
>
>Reinoud van Leeuwen
>Sybase Professional Services Maarssen / The Netherlands

John McVicker
Sybase Professional Services
Philadelphia, PA


Vitaly Lipovetsky Posted on 1998-02-20 06:57:03.0Z
From: "Vitaly Lipovetsky" <vit@fuib.com>
References: <34e57c56.0@ifuib> <34EC128C.49E1@sybase.com> <NgJCVdUP9GA.129@forums.powersoft.com>
Subject: Re: Problem with bcp during transfer data from MS to SYBASE.
Date: Fri, 20 Feb 1998 08:57:03 +0200
Lines: 112
X-Newsreader: Microsoft Outlook Express 4.71.1712.3
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Message-ID: <34ed283f.0@ifuib>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com!ifuib!172.17.3.101
Xref: forums-1-dub sybase.public.sqlserver.nt:5168
Article PK: 1081249

I have the same bug with this table, but it don't contain columns with tabs.

I explored some methods for bcp and I think problem raises if target column
numeric with zero precision. If I use numeric(12, 1) bcp works correctly.

tp_id numeric(12,0)

CREATE TABLE tb_accn (
accn_id tp_id IDENTITY NOT NULL ,
cnum decimal(6, 0) NOT NULL ,
ccy char (3) NOT NULL ,
acod decimal(4, 0) NOT NULL ,
acsq decimal(2, 0) NOT NULL ,
brca char (3) NOT NULL ,
nbuaccntab_id tp_id NULL
)

John McVicker wrote in message ...
>Vitaly,
>>> lisp decimal(3, 0) NOT NULL ,
>>> namer varchar (50) NULL ,
>>> nbuaccntab_id tp_id NULL ,
>>> mtype tinyint NULL
>
>namer may have "tabs" in them. If so, this is messing up your value in
>the nbuaccntab_id BCP in. When you BCP out, use a column
>delimiter that is in no way possible in the namer column. You can use
>multi-character terminators. Also, I wouldn't use \0 for row terminators,
>I would use \n and if you need to FTP to another host, just use ASCII
>mode of FTP.
>Here's something to try:
>
>c:\MSSQL\BINN\bcp FUIB_02..%1 out ..\BCP\%1.BCP -SSQL2-SVR -T -c -t^%^ -r\n
>.\LOG_OUT\%1.log
>
>%SYBASE%\bin\bcp FUIB_TEST..%1 in
>.\BCP\%1.bcp -SBACKUP-SVR -c -Ums2syb_user -Pms2syb_use
>%3 -J -m1 -b10000 -t^%^ -r\n %2 > ..\LOG_IN\%1.log
>
>The string ^%^ will delimit columns - should be quite unique. Choose
>as appropriate.
>
>On Thu, 19 Feb 1998 12:07:56 +0100,
> in sybase.public.sqlserver.nt
>Reinoud van Leeuwen <reinoud@sybase.com> wrote:
>>Vitaly Lipovetsky wrote:
>>>
>>> I migrating from MS SQL 6.5 to ASE 11.5.
>>>
>>> tp_id is int in MS and numeric(12,0) in syb.
>>>
>>> I have a table :
>>>
>>> CREATE TABLE tb_accntab2 (
>>> accn_id tp_id NOT NULL ,
>>> cnum decimal(6, 0) NOT NULL ,
>>> ccy char (3) NOT NULL ,
>>> acod decimal(4, 0) NOT NULL ,
>>> acsq decimal(2, 0) NOT NULL ,
>>> brca char (3) NOT NULL ,
>>> acno decimal(10, 0) NOT NULL ,
>>> daco smalldatetime NOT NULL ,
>>> dacc smalldatetime NOT NULL ,
>>> lmvd smalldatetime NOT NULL ,
>>> dldbl decimal(22, 2) NOT NULL ,
>>> cldbl decimal(22, 2) NOT NULL ,
>>> logtime datetime NOT NULL ,
>>> dturn decimal(22, 2) NOT NULL ,
>>> cturn decimal(22, 2) NOT NULL ,
>>> status tinyint NOT NULL ,
>>> lstp decimal(3, 0) NOT NULL ,
>>> lisp decimal(3, 0) NOT NULL ,
>>> namer varchar (50) NULL ,
>>> nbuaccntab_id tp_id NULL ,
>>> mtype tinyint NULL
>>> )
>>>
>>> I download data from MS via cmd.
>>> out.cmd
>>> c:\MSSQL\BINN\bcp FUIB_02..%1 out ..\BCP\%1.BCP -SSQL2-SVR -T -c -r \0
>>> >..\LOG_OUT\%1.log
>>>
>>> and upload to SYB
>>> via
>>> in.cmd
>>> %SYBASE%\bin\bcp FUIB_TEST..%1 in
>>> ..\BCP\%1.bcp -SBACKUP-SVR -c -Ums2syb_user -Pms2syb_use
>>> %3 -J -m1 -b10000 -r \0 %2 > ..\LOG_IN\%1.log
>>>
>>> After transfer I find in some rows column nbuaccntab_id < 0 with the
same
>>> value -51539607552
>>> But MS table don't have negative values in nbuaccntab_id.
>>>
>>> I tried to transfer 1 incorrect row via view,
>>> and nbuaccntab_id loaded correctly. ????
>>
>>BCP doesn't do much error checking or conversions. It works best when
>>the two tables are identical (both names and datatypes and order of
>>collumns). This explains why the bcp out from a view can work correctly.
>>I assume the view has something like
>>select (all fields), tp_id = converert (numeric (12,0), tp_id) from
>>source_table
>>
>>Reinoud van Leeuwen
>>Sybase Professional Services Maarssen / The Netherlands
>
>John McVicker
>Sybase Professional Services
>Philadelphia, PA