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 with newid() generation

4 posts in General Discussion Last posting was on 2011-06-08 08:59:47.0Z
hy Posted on 2011-06-07 03:24:16.0Z
From: "hy" <nospam_harrylhy@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: bcp with newid() generation
Lines: 8
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ded99e0$1@forums-1-dub>
Date: 6 Jun 2011 20:24:16 -0700
X-Trace: forums-1-dub 1307417056 10.22.241.152 (6 Jun 2011 20:24:16 -0700)
X-Original-Trace: 6 Jun 2011 20:24:16 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30222
Article PK: 72402

ASE: Adaptive Server Enterprise/15.0/EBF 13448 ESD#2/P/NT (IX86)/Windows 2000/ase150/2193/32-bit/OPT/Wed May 17 16:43:17 2006



Hi all

I need to import some data back to our ase that comes from the text files (export from foxpro), the primary key of the table in ase
using newid() for column default and will generate different unique keys automatically with general insert sql, I import the text
file without any data for primary key and let ase generate by itself, but if I use bcp to import the text files, it only generate
one newid() key for whole column data, then prompt the duplicate error message and reject this import (I deleted the primary key and
check this out). Is this a normal case in ase with bcp? or this is a bug or any human error?




Thanks for anyone help


Rob V [ Sybase ] Posted on 2011-06-07 10:44:02.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: bcp with newid() generation
References: <4ded99e0$1@forums-1-dub>
In-Reply-To: <4ded99e0$1@forums-1-dub>
Content-Type: text/plain; charset=Big5
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dee00f2$1@forums-1-dub>
Date: 7 Jun 2011 03:44:02 -0700
X-Trace: forums-1-dub 1307443442 10.22.241.152 (7 Jun 2011 03:44:02 -0700)
X-Original-Trace: 7 Jun 2011 03:44:02 -0700, vip152.sybase.com
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30223
Article PK: 72401


On 07-Jun-2011 05:24, hy wrote:
> ASE: Adaptive Server Enterprise/15.0/EBF 13448 ESD#2/P/NT
> (IX86)/Windows 2000/ase150/2193/32-bit/OPT/Wed May 17 16:43:17 2006
>
>
>
> Hi all
>
> I need to import some data back to our ase that comes from the text
> files (export from foxpro), the primary key of the table in ase using
> newid() for column default and will generate different unique keys
> automatically with general insert sql, I import the text file without
> any data for primary key and let ase generate by itself, but if I use
> bcp to import the text files, it only generate one newid() key for
> whole column data, then prompt the duplicate error message and reject
> this import (I deleted the primary key and check this out). Is this a
> normal case in ase with bcp? or this is a bug or any human error?
>
>
>
>
> Thanks for anyone help

How are you bcp'ing into the table? I guess you're BCP'ing without a value for the key.
When I do that, I see the same behaviour as what you describe still in 15.5.
I think this may be a scenario that may just never have been covered, and you're stumbling into this. I'm not saying it is a bug though.

I'll raise an internal change request for this though, but it would be helpful if you could send me some specifics about your use case (type of application, volume of data imported, your reason for using newid() here) -- please send to me directly at rob--at--sypron--dot--nl.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


hy Posted on 2011-06-08 03:37:39.0Z
From: "hy" <nospam_harrylhy@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4ded99e0$1@forums-1-dub> <4dee00f2$1@forums-1-dub>
In-Reply-To: <4dee00f2$1@forums-1-dub>
Subject: Re: bcp with newid() generation
Lines: 1
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4deeee83$1@forums-1-dub>
Date: 7 Jun 2011 20:37:39 -0700
X-Trace: forums-1-dub 1307504259 10.22.241.152 (7 Jun 2011 20:37:39 -0700)
X-Original-Trace: 7 Jun 2011 20:37:39 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30229
Article PK: 72408

Thanks Rob, One of the case I process my BCP data as following steps:

1. In Foxpro 2.6 for DOS, I wrote a small scripts (or in foxpro command prompt) to export data with command "copy to txtfile1 type
sdf" (SDF output data to text file without any seperator between columns), and export the final text file contents like following ;

dos_importJOYCE 20050510OT-THATHAILAND
dos_importJOYCE 20050510CHINA CHINA
dos_importJOYCE 20080519EU-EU EUROPE

(10 char: dos_import = create user,
10 char: JOYCE = last update user,
8 char: 20050510 = last update date,
6 char: OT-THA = location,
40 char: THAILAND = description of location)

2. In dos command, I use the following bcp command (typed in .bat file) to import the above data back to ASE;

BCP database01..loc_data IN txtfile1.txt -Usa -Ppassword -f txtfile1.fmt -e txtfile1.err

Contents in txtfile.fmt:
10.0
5
1 SYBCHAR 0 10 "" 2 cruser
2 SYBCHAR 0 10 "" 4 luuser
3 SYBCHAR 0 8 "" 5 ludate
4 SYBCHAR 0 6 "" 10 locat
5 SYBCHAR 0 40 "\n" 11 descn


In ASE, the structure of loc_data like following:

create table loc_data (
mtype char(1) DEFAULT 'I' not null , <-- Maintenance type ('I','U','D'), modify in table trigger
cruser varchar(30) DEFAULT suser_name() not null , <-- create user
crdate datetime DEFAULT getdate() not null , <-- create date
luuser varchar(30) DEFAULT suser_name() not null , <-- last update user, modify in table trigger
ludate datetime DEFAULT getdate() not null , <-- last update date, modify in table trigger
rid udd_rid not null , <-- ** Unique row ID, column of PRIMARY KEY
locat char(6) not null , <-- location id
descn char(40) DEFAULT '' not null ) <-- description

* udd_rid: User defined data type char(40), and auto fill with default: convert(varchar(30), getutcdate(), 112)+newid()


As you can see, the table structure of dos .dbf not same as ase table, first 6 columns is default to system generate, so some
columns may not fill at import time, include unique primary key (rid), and now my temporary solution is generate this unique key in
foxpro with date + recno() and insert to a temp table first (e.g.: select
dtos(date())+strtran(time(),":","")+padl(alltrim(str(recno(),26)),26,'0') as rid, * from loc_dos into cursor c1), then use this
cursor for text file export (as following sample data), the key format is different but better than nothing can do :)


2011060316273500000000000000000000000018dos_importJOYCE 20050510OT-THATHIALAND
2011060316273500000000000000000000000019dos_importJOYCE 20050510CHINA CHINA
2011060316273500000000000000000000000020dos_importJOYCE 20080519EU-EU EUROPE




"Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> 礎b繞l瞼籀簣i繞K瞻繙簧e瞼D礎簧 4dee00f2$1@forums-1-dub 瞻瞻翹繞翹g...

> On 07-Jun-2011 05:24, hy wrote:
>> ASE: Adaptive Server Enterprise/15.0/EBF 13448 ESD#2/P/NT
>> (IX86)/Windows 2000/ase150/2193/32-bit/OPT/Wed May 17 16:43:17 2006
>>
>>
>>
>> Hi all
>>
>> I need to import some data back to our ase that comes from the text
>> files (export from foxpro), the primary key of the table in ase using
>> newid() for column default and will generate different unique keys
>> automatically with general insert sql, I import the text file without
>> any data for primary key and let ase generate by itself, but if I use
>> bcp to import the text files, it only generate one newid() key for
>> whole column data, then prompt the duplicate error message and reject
>> this import (I deleted the primary key and check this out). Is this a
>> normal case in ase with bcp? or this is a bug or any human error?
>>
>>
>>
>>
>> Thanks for anyone help
>
>
> How are you bcp'ing into the table? I guess you're BCP'ing without a value for the key.
> When I do that, I see the same behaviour as what you describe still in 15.5.
> I think this may be a scenario that may just never have been covered, and you're stumbling into this. I'm not saying it is a bug
> though.
>
> I'll raise an internal change request for this though, but it would be helpful if you could send me some specifics about your use
> case (type of application, volume of data imported, your reason for using newid() here) -- please send to me directly at
> rob--at--sypron--dot--nl.
>
> HTH,
>
> Rob V.
> -----------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
> and Replication Server 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at www.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
> Sypron B.V., The Netherlands | Chamber of Commerce 27138666
> -----------------------------------------------------------------
>


Rob V [ Sybase ] Posted on 2011-06-08 08:59:47.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: bcp with newid() generation
References: <4ded99e0$1@forums-1-dub> <4dee00f2$1@forums-1-dub> <4deeee83$1@forums-1-dub>
In-Reply-To: <4deeee83$1@forums-1-dub>
Content-Type: text/plain; charset=Big5
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4def3a03@forums-1-dub>
Date: 8 Jun 2011 01:59:47 -0700
X-Trace: forums-1-dub 1307523587 10.22.241.152 (8 Jun 2011 01:59:47 -0700)
X-Original-Trace: 8 Jun 2011 01:59:47 -0700, vip152.sybase.com
Lines: 151
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30230
Article PK: 72409


On 08-Jun-2011 05:37, hy wrote:
> Thanks Rob, One of the case I process my BCP data as following steps:
>
> 1. In Foxpro 2.6 for DOS, I wrote a small scripts (or in foxpro
> command prompt) to export data with command "copy to txtfile1 type
> sdf" (SDF output data to text file without any seperator between
> columns), and export the final text file contents like following ;
>
> dos_importJOYCE 20050510OT-THATHAILAND
> dos_importJOYCE 20050510CHINA CHINA
> dos_importJOYCE 20080519EU-EU EUROPE
>
> (10 char: dos_import = create user,
> 10 char: JOYCE = last update user,
> 8 char: 20050510 = last update date,
> 6 char: OT-THA = location,
> 40 char: THAILAND = description of location)
>
> 2. In dos command, I use the following bcp command (typed in .bat
> file) to import the above data back to ASE;
>
> BCP database01..loc_data IN txtfile1.txt -Usa -Ppassword -f
> txtfile1.fmt -e txtfile1.err
>
> Contents in txtfile.fmt:
> 10.0
> 5
> 1 SYBCHAR 0 10 "" 2 cruser
> 2 SYBCHAR 0 10 "" 4 luuser
> 3 SYBCHAR 0 8 "" 5 ludate
> 4 SYBCHAR 0 6 "" 10 locat
> 5 SYBCHAR 0 40 "\n" 11 descn
>
>
> In ASE, the structure of loc_data like following:
>
> create table loc_data (
> mtype char(1) DEFAULT 'I' not null , <-- Maintenance type
> ('I','U','D'), modify in table trigger
> cruser varchar(30) DEFAULT suser_name() not null , <-- create user
> crdate datetime DEFAULT getdate() not null , <-- create date
> luuser varchar(30) DEFAULT suser_name() not null , <-- last update
> user, modify in table trigger
> ludate datetime DEFAULT getdate() not null , <-- last update date,
> modify in table trigger
> rid udd_rid not null , <-- ** Unique row ID, column of PRIMARY KEY
> locat char(6) not null , <-- location id
> descn char(40) DEFAULT '' not null ) <-- description
>
> * udd_rid: User defined data type char(40), and auto fill with
> default: convert(varchar(30), getutcdate(), 112)+newid()
>
>
> As you can see, the table structure of dos .dbf not same as ase table,
> first 6 columns is default to system generate, so some columns may not
> fill at import time, include unique primary key (rid), and now my
> temporary solution is generate this unique key in foxpro with date +
> recno() and insert to a temp table first (e.g.: select
> dtos(date())+strtran(time(),":","")+padl(alltrim(str(recno(),26)),26,'0')
> as rid, * from loc_dos into cursor c1), then use this cursor for text
> file export (as following sample data), the key format is different
> but better than nothing can do :)
>
>
> 2011060316273500000000000000000000000018dos_importJOYCE
> 20050510OT-THATHIALAND
> 2011060316273500000000000000000000000019dos_importJOYCE 20050510CHINA
> CHINA
> 2011060316273500000000000000000000000020dos_importJOYCE 20080519EU-EU
> EUROPE
>
>
>
>
> "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> 礎b繞l
> 瞼籀簣i繞K瞻繙簧e瞼D礎簧 4dee00f2$1@forums-1-dub 瞻瞻翹繞翹g...
>> On 07-Jun-2011 05:24, hy wrote:
>>> ASE: Adaptive Server Enterprise/15.0/EBF 13448 ESD#2/P/NT
>>> (IX86)/Windows 2000/ase150/2193/32-bit/OPT/Wed May 17 16:43:17 2006
>>>
>>>
>>>
>>> Hi all
>>>
>>> I need to import some data back to our ase that comes from the text
>>> files (export from foxpro), the primary key of the table in ase using
>>> newid() for column default and will generate different unique keys
>>> automatically with general insert sql, I import the text file without
>>> any data for primary key and let ase generate by itself, but if I use
>>> bcp to import the text files, it only generate one newid() key for
>>> whole column data, then prompt the duplicate error message and reject
>>> this import (I deleted the primary key and check this out). Is this a
>>> normal case in ase with bcp? or this is a bug or any human error?
>>>
>>>
>>>
>>>
>>> Thanks for anyone help
>>
>>
>> How are you bcp'ing into the table? I guess you're BCP'ing without a
>> value for the key.
>> When I do that, I see the same behaviour as what you describe still
>> in 15.5.
>> I think this may be a scenario that may just never have been covered,
>> and you're stumbling into this. I'm not saying it is a bug though.
>>
>> I'll raise an internal change request for this though, but it would
>> be helpful if you could send me some specifics about your use case
>> (type of application, volume of data imported, your reason for using
>> newid() here) -- please send to me directly at rob--at--sypron--dot--nl.
>>
>> HTH,
>>
>> Rob V.
>> -----------------------------------------------------------------
>> Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
>> and Replication Server 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at www.sypron.nl/shop):
>> "Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
>> Sypron B.V., The Netherlands | Chamber of Commerce 27138666
>> -----------------------------------------------------------------
>>

Thanks, this is helpful.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------