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 behaves differently on different tables.

4 posts in General Discussion Last posting was on 2010-10-01 03:47:56.0Z
George Brink Posted on 2009-10-07 18:24:51.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: bcp behaves differently on different tables.
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: <4accdcf3$1@forums-1-dub>
Date: 7 Oct 2009 11:24:51 -0700
X-Trace: forums-1-dub 1254939891 10.22.241.152 (7 Oct 2009 11:24:51 -0700)
X-Original-Trace: 7 Oct 2009 11:24:51 -0700, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28424
Article PK: 77668

Server ASE 15.0.3#1
I need to load many tables into database. I have a script which runs bcp
for each table one by one, something like:
bcp.exe mydb.dbo.t1 in data\t1.dat -S.. -U.. -P.. -X -c
bcp.exe mydb.dbo.t2 in data\t2.dat -S.. -U.. -P.. -X -c
bcp.exe mydb.dbo.t3 in data\t3.dat -S.. -U.. -P.. -X -c
... etc for three hundred tables.
The problem is that some of the tables are loaded perfectly, but some
are not.

Few tables gives me a warning:
-------------------
Starting copy...
Server Message: tst - Msg 4852, Level 10, State 1:
Performing slow bcp on table 'aaaa'. To enable fast bcp please turn on
'select into/bulkcopy' option on the database 'mydb'.


0 rows copied.
Clock Time (ms.): total = 16
-------------------

Some tables fail with error:
-------------------
Starting copy...
Server Message: tst - Msg 4806, Level 16, State 1:
You cannot run the non-logged version of bulk copy in this database.
Please check with the DBO.

Server Message: tst - Msg 3621, Level 10, State 0:
Command has been aborted.

CTLIB Message: - L1/O3/S0/N14/0/0:
blk_init(): blk layer: CT library error: Failed when CT_Lib routine
ct_results() called.
blk_init failed.
bcp copy in failed
-------------------

My question right now is why there is a difference? Why some tables are
loaded without any warnings and other fail with the error? All tables
are in the same database. What can be different between these tables?


Neal Stack [Sybase] Posted on 2009-10-07 19:33:28.0Z
Message-ID: <4ACCED07.5030504@nospam.com>
From: "Neal Stack [Sybase]" <nstack@nospam.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
To: George Brink <siberianowl@yahoo.com>
Subject: Re: bcp behaves differently on different tables.
References: <4accdcf3$1@forums-1-dub>
In-Reply-To: <4accdcf3$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
Date: 7 Oct 2009 12:33:28 -0700
X-Trace: forums-1-dub 1254944008 10.22.241.152 (7 Oct 2009 12:33:28 -0700)
X-Original-Trace: 7 Oct 2009 12:33:28 -0700, vip152.sybase.com
Lines: 7
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28426
Article PK: 77669

Hello,

The tables that generate the message about "slow" bcp likely have an index or trigger defined for them:
http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.utility/html/utility/utility62.htm

Regards,
Neal


Bret Halford [Sybase] Posted on 2009-10-07 19:51:37.0Z
From: "Bret Halford [Sybase]" <bret@sybase.com>
Organization: Sybase, Inc.
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: bcp behaves differently on different tables.
References: <4accdcf3$1@forums-1-dub>
In-Reply-To: <4accdcf3$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: <4accf149$1@forums-1-dub>
Date: 7 Oct 2009 12:51:37 -0700
X-Trace: forums-1-dub 1254945097 10.22.241.152 (7 Oct 2009 12:51:37 -0700)
X-Original-Trace: 7 Oct 2009 12:51:37 -0700, vip152.sybase.com
Lines: 63
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28427
Article PK: 77670


George Brink wrote:
> Server ASE 15.0.3#1
> I need to load many tables into database. I have a script which runs bcp
> for each table one by one, something like:
> bcp.exe mydb.dbo.t1 in data\t1.dat -S.. -U.. -P.. -X -c
> bcp.exe mydb.dbo.t2 in data\t2.dat -S.. -U.. -P.. -X -c
> bcp.exe mydb.dbo.t3 in data\t3.dat -S.. -U.. -P.. -X -c
> ... etc for three hundred tables.
> The problem is that some of the tables are loaded perfectly, but some
> are not.
>
> Few tables gives me a warning:
> -------------------
> Starting copy...
> Server Message: tst - Msg 4852, Level 10, State 1:
> Performing slow bcp on table 'aaaa'. To enable fast bcp please turn on
> 'select into/bulkcopy' option on the database 'mydb'.
>
>
> 0 rows copied.
> Clock Time (ms.): total = 16
> -------------------
>
> Some tables fail with error:
> -------------------
> Starting copy...
> Server Message: tst - Msg 4806, Level 16, State 1:
> You cannot run the non-logged version of bulk copy in this database.
> Please check with the DBO.
>
> Server Message: tst - Msg 3621, Level 10, State 0:
> Command has been aborted.
>
> CTLIB Message: - L1/O3/S0/N14/0/0:
> blk_init(): blk layer: CT library error: Failed when CT_Lib routine
> ct_results() called.
> blk_init failed.
> bcp copy in failed
> -------------------
>
> My question right now is why there is a difference? Why some tables are
> loaded without any warnings and other fail with the error? All tables
> are in the same database. What can be different between these tables?

I believe you will find the difference is that the tables with errors do
not have a clustered index, while the tables that work do.

Historically, bcp would always want to use fully-logged ("slow bcp")
if the table had any indexes or triggers. 15.0.2 relaxed this and
now allows "fast bcp" into tables that have nonclustered indexes or
triggers (the triggers aren't fired). It is raising the warning to
alert you that it could run in fast mode if you turned sp_dboption
"select into" on.

If the table does not have a clustered index, bcp wants to use "fast
bcp" mode. See the 15.0.2 New Features guide for more information.
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00641.1502/html/nfg1502/nfg1502131.htm


Cheers,
-bret


arkadiyk Posted on 2010-10-01 03:47:56.0Z
From: "arkadiyk" <arkadiyk@gmail.com>
Newsgroups: sybase.public.ase.general
Organization: 133.250.249.71
References: <4accdcf3$1@forums-1-dub>
X-Newsreader: AspNNTP 1.50 (JodoHost)
Subject: Re: bcp behaves differently on different tables.
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ca559ec$1@forums-1-dub>
Date: 30 Sep 2010 20:47:56 -0700
X-Trace: forums-1-dub 1285904876 10.22.241.152 (30 Sep 2010 20:47:56 -0700)
X-Original-Trace: 30 Sep 2010 20:47:56 -0700, vip152.sybase.com
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29621
Article PK: 78854

sorry, the answer is probably late, but for anyone who is like me googling for
the error message, here it is:

you need to either run the following command if you are dbo
sp_dboption database_name, "select into/bulkcopy", true

or create an index for this table


the full answer is here:

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/@Generic__BookTex
tView/40676;pt=15380