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 and Date format

9 posts in ,  AdministrationWindows NT General Discussion Last posting was on 1998-01-12 12:48:21.0Z
Richard R. Klemmer Posted on 1998-01-08 14:15:44.0Z
From: "Richard R. Klemmer" <richard.klemmer@NOSPAM.usda.com>
Subject: BCP and Date format
Organization: USDA-FSIS
Message-ID: <01bd1c48$d15566e0$663b80c7@fsis7.fsis.usda.gov>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Date: Thu, 08 Jan 1998 09:15:44 -0500
Lines: 35
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3338 sybase.public.sqlserver.general:793 sybase.public.sqlserver.nt:389
Article PK: 1065497

I am trying to copy some data into a Sybase SQL Sever 11.0x system for
Windows NT. I am receiving the data in ASCII format, with column
seperators. The problem I am having, is that one of the tables I need to
copy the data into has DATETIME fields in it. I was able to use a format
file for everything else, including the integers, but I cannot get the date
format to work.
I have tried BCP out and specified TEXT for the format of the DATETIME
fields to see what it looked like. The format was "Dec 1 1997
12:00:00:000AM". However, when I tried to BCP in this same data, using the
format file that was created when I exported the data, I got and error
stateing "DB-LIBRARY error: Attempt to convert data stopped by syntax
error in source field."
The only reason I can think that this error occurs is the it can't convert
the Date field back into the DATETIME format.
I can't really purchase a seperate product to convert the data for me, and
other than the Date problem, BCP seems to work for everything else I need.
Does anyone have any idea how I can solve this problem? I should be able
to change how the date is displayed in the ASCII file (such as YYYYMMDD or
YY/MM/DD or even month day year), but I won't be able to get it into the
native SYSDATE format that Sybase and BCP use.
Please post a message here or e-mail me at richard.klemmer@usda.gov.


Dinyar Posted on 1998-01-08 16:53:46.0Z
Message-ID: <34B5049A.F789CCAC@rocketmail.com>
Date: Thu, 08 Jan 1998 22:23:46 +0530
From: Dinyar <dinyar@rocketmail.com>
X-Mailer: Mozilla 4.03 [en] (Win95; I)
MIME-Version: 1.0
To: richard.klemmer@usda.gov
Subject: Re: BCP and Date format
References: <01bd1c48$d15566e0$663b80c7@fsis7.fsis.usda.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 48
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3336 sybase.public.sqlserver.general:791 sybase.public.sqlserver.nt:387
Article PK: 1065496

Hi,
what is your bcp / sybase version?

have u tried
set dateformat and change the setting to the format that u want - think u might need
to restart sybase for the effect to take place - please correnct if i am wrong

have to tried
to bcp in into a table with only a date field in it?

can u post a sample row? and the table structure - including the fmt file?
-dinyar

Richard R. Klemmer wrote:

> I am trying to copy some data into a Sybase SQL Sever 11.0x system for
> Windows NT. I am receiving the data in ASCII format, with column
> seperators. The problem I am having, is that one of the tables I need to
> copy the data into has DATETIME fields in it. I was able to use a format
> file for everything else, including the integers, but I cannot get the date
> format to work.
> I have tried BCP out and specified TEXT for the format of the DATETIME
> fields to see what it looked like. The format was "Dec 1 1997
> 12:00:00:000AM". However, when I tried to BCP in this same data, using the
> format file that was created when I exported the data, I got and error
> stateing "DB-LIBRARY error: Attempt to convert data stopped by syntax
> error in source field."
> The only reason I can think that this error occurs is the it can't convert
> the Date field back into the DATETIME format.
> I can't really purchase a seperate product to convert the data for me, and
> other than the Date problem, BCP seems to work for everything else I need.
> Does anyone have any idea how I can solve this problem? I should be able
> to change how the date is displayed in the ASCII file (such as YYYYMMDD or
> YY/MM/DD or even month day year), but I won't be able to get it into the
> native SYSDATE format that Sybase and BCP use.
> Please post a message here or e-mail me at richard.klemmer@usda.gov.


Jason Froebe Posted on 1998-01-09 00:57:55.0Z
Message-ID: <34B57613.2A06@btigate.com>
Date: Thu, 08 Jan 1998 18:57:55 -0600
From: Jason Froebe <jfroebe@btigate.com>
Organization: .
X-Mailer: Mozilla 3.04 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
Subject: Re: BCP and Date format
References: <01bd1c48$d15566e0$663b80c7@fsis7.fsis.usda.gov> <34B5049A.F789CCAC@rocketmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 13
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3332 sybase.public.sqlserver.general:787 sybase.public.sqlserver.nt:383
Article PK: 1065493


Dinyar wrote:
>
> Hi,
> what is your bcp / sybase version?
>
> have u tried
> set dateformat and change the setting to the format that u want - think u might need
> to restart sybase for the effect to take place - please correnct if i am wrong
>
> have to tried
> to bcp in into a table with only a date field in it?
>
> can u post a sample row? and the table structure - including the fmt file?
> -dinyar

Also try the "-b1" bcp option to see if it occurs on a specific row.

J


Bret Halford Posted on 1998-01-08 15:48:18.0Z
Message-ID: <34B4F542.4435@sybase.com>
Date: Thu, 08 Jan 1998 08:48:18 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: "Richard R. Klemmer" <richard.klemmer@NOSPAM.usda.com>
Subject: Re: BCP and Date format
References: <01bd1c48$d15566e0$663b80c7@fsis7.fsis.usda.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 52
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3337 sybase.public.sqlserver.general:792 sybase.public.sqlserver.nt:388
Article PK: 1065498


Richard R. Klemmer wrote:
>
> I am trying to copy some data into a Sybase SQL Sever 11.0x system for
> Windows NT. I am receiving the data in ASCII format, with column
> seperators. The problem I am having, is that one of the tables I need to
> copy the data into has DATETIME fields in it. I was able to use a format
> file for everything else, including the integers, but I cannot get the date
> format to work.
> I have tried BCP out and specified TEXT for the format of the DATETIME
> fields to see what it looked like. The format was "Dec 1 1997
> 12:00:00:000AM". However, when I tried to BCP in this same data, using the
> format file that was created when I exported the data, I got and error
> stateing "DB-LIBRARY error: Attempt to convert data stopped by syntax
> error in source field."
> The only reason I can think that this error occurs is the it can't convert
> the Date field back into the DATETIME format.
> I can't really purchase a seperate product to convert the data for me, and
> other than the Date problem, BCP seems to work for everything else I need.
> Does anyone have any idea how I can solve this problem? I should be able
> to change how the date is displayed in the ASCII file (such as YYYYMMDD or
> YY/MM/DD or even month day year), but I won't be able to get it into the
> native SYSDATE format that Sybase and BCP use.
> Please post a message here or e-mail me at richard.klemmer@usda.gov.

When you are doing the bcp in, are you using "native mode" (-n)
or "ASCII mode" (-c).

I have generally not found format files necessary for ASCII mode.

Try using the ASCII mode, specifying the column seperator, and
see how that works.

If that fails, perhaps you could post the appropriate "create table"
script, and a sample line of data that you want bcped in.

--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303


Richard R. Klemmer Posted on 1998-01-08 15:49:07.0Z
From: "Richard R. Klemmer" <richard.klemmer@NOSPAM.usda.com>
Subject: Re: BCP and Date format
References: <01bd1c48$d15566e0$663b80c7@fsis7.fsis.usda.gov> <34B4F542.4435@sybase.com>
Organization: USDA-FSIS
Message-ID: <01bd1c55$dfb4ae00$663b80c7@fsis7.fsis.usda.gov>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Date: Thu, 08 Jan 1998 10:49:07 -0500
Lines: 23
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3335 sybase.public.sqlserver.general:790 sybase.public.sqlserver.nt:386
Article PK: 1065499


Bret Halford <bret@sybase.com> wrote in article
<34B4F542.4435@sybase.com>...
> Richard R. Klemmer wrote:
> >
>
>
> When you are doing the bcp in, are you using "native mode" (-n)
> or "ASCII mode" (-c).
>
> I have generally not found format files necessary for ASCII mode.
>
> Try using the ASCII mode, specifying the column seperator, and
> see how that works.

I swear to GOD that I tried using the ASCII mode Yesterday and I
continually got Data Conversion errors or if I did get them to BCP in, they
would be strange large numbers for the Interger data types. However, I
just went back and tried it, and the data was copied in correctly for
everything, including the dates.
I know I'm not going crazy, this didn't work Yesterday. I swear I did
everything the same. I really must be losing it.:-)
Thanks for your help. I'll be checking into the funny farm now.


Bret Halford Posted on 1998-01-08 17:23:13.0Z
Message-ID: <34B50B81.34FD@sybase.com>
Date: Thu, 08 Jan 1998 10:23:13 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: "Richard R. Klemmer" <richard.klemmer@usda.com>
Subject: Re: BCP and Date format
References: <01bd1c48$d15566e0$663b80c7@fsis7.fsis.usda.gov> <34B4F542.4435@sybase.com> <01bd1c55$dfb4ae00$663b80c7@fsis7.fsis.usda.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 22
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3334 sybase.public.sqlserver.general:789 sybase.public.sqlserver.nt:385
Article PK: 1065495


Richard R. Klemmer wrote:
>
v
>
> I swear to GOD that I tried using the ASCII mode Yesterday and I
> continually got Data Conversion errors or if I did get them to BCP in, they
> would be strange large numbers for the Interger data types. However, I
> just went back and tried it, and the data was copied in correctly for
> everything, including the dates.
> I know I'm not going crazy, this didn't work Yesterday. I swear I did
> everything the same. I really must be losing it.:-)
> Thanks for your help. I'll be checking into the funny farm now.

Perhaps you were using a combination of format file and ASCII mode?
I've seen strange results from that.

--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303


Richard R. Klemmer Posted on 1998-01-08 16:54:51.0Z
From: "Richard R. Klemmer" <richard.klemmer@NOSPAM.usda.com>
Subject: Re: BCP and Date format
References: <01bd1c48$d15566e0$663b80c7@fsis7.fsis.usda.gov> <34B4F542.4435@sybase.com> <01bd1c55$dfb4ae00$663b80c7@fsis7.fsis.usda.gov> <34B50B81.34FD@sybase.com>
Organization: USDA-FSIS
Message-ID: <01bd1c5f$19b97fa0$663b80c7@fsis7.fsis.usda.gov>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Date: Thu, 08 Jan 1998 11:54:51 -0500
Lines: 44
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3333 sybase.public.sqlserver.general:788 sybase.public.sqlserver.nt:384
Article PK: 1065494


Bret Halford <bret@sybase.com> wrote in article
<34B50B81.34FD@sybase.com>...
> Perhaps you were using a combination of format file and ASCII mode?
> I've seen strange results from that.
>
> --

No, I wasn't doing that. However, I've had to delete and re-create the
tables, and I've changed some of the options in the database, as well as
stopped and started the server, so maybe there was something in there that
made the difference. Perhaps the error was being caused by something else,
and I incorrectly assumed it was because of the charater mode switch and
the integer data types. Anyway, it works now.

Since I have you on the line (so to speak) I may as well as a couple more
questions. On two of the tables that I am BCPing data into, after the
process is done, I am unable to select all the rows in WISQL. I get and
error stateing:
------------------
Open Client Error: ct_cmd_drop(): user api layer: external error: This
routine can be called only if the command structure is idle
------------------

Am I, however, able to run the select statement using the command line
ISQL program. Do you have any idea why this may be happening?

Also, is there some good way of calling BCP from Visual Basic (ver 5.0) or
using the functionality of BCP and other utilities without having to write
a C++ program that using Open Client? Someone earlier mention an ActiveX
control, SQL Sombero/OCX, from Sylvan Faust, Inc. Would this give me the
functionality that I need? Right now I'm using Batch files, but I'd like
to use something else that would give me the ability for greater validation
of the input.

I'm sorry to throw all these questions at you. I really appreciate your
assistance.


Bret Halford Posted on 1998-01-09 21:45:57.0Z
Message-ID: <34B69A95.54A6@sybase.com>
Date: Fri, 09 Jan 1998 14:45:57 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: "Richard R. Klemmer" <richard.klemmer@NOSPAM.usda.com>
Subject: Re: BCP and Date format
References: <01bd1c48$d15566e0$663b80c7@fsis7.fsis.usda.gov> <34B4F542.4435@sybase.com> <01bd1c55$dfb4ae00$663b80c7@fsis7.fsis.usda.gov> <34B50B81.34FD@sybase.com> <01bd1c5f$19b97fa0$663b80c7@fsis7.fsis.usda.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 37
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3331 sybase.public.sqlserver.general:786 sybase.public.sqlserver.nt:382
Article PK: 1065492


Richard R. Klemmer wrote:

>
> Since I have you on the line (so to speak) I may as well as a couple more
> questions. On two of the tables that I am BCPing data into, after the
> process is done, I am unable to select all the rows in WISQL. I get and
> error stateing:
> ------------------
> Open Client Error: ct_cmd_drop(): user api layer: external error: This
> routine can be called only if the command structure is idle
> ------------------
>
> Am I, however, able to run the select statement using the command line
> ISQL program. Do you have any idea why this may be happening?

I'm pretty UNIX centric, so I never get to work with WISQL. But
it appears WISQL has a 64K buffer; you get this message when it
overflows.

> Also, is there some good way of calling BCP from Visual Basic (ver 5.0) or
> using the functionality of BCP and other utilities without having to write
> a C++ program that using Open Client? Someone earlier mention an ActiveX
> control, SQL Sombero/OCX, from Sylvan Faust, Inc. Would this give me the
> functionality that I need? Right now I'm using Batch files, but I'd like
> to use something else that would give me the ability for greater validation
> of the input.

Sorry, no clue on this one.

--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303


Richard R. Klemmer Posted on 1998-01-12 12:48:21.0Z
From: "Richard R. Klemmer" <richard.klemmer@NOSPAM.usda.com>
Subject: Re: BCP and Date format
References: <01bd1c48$d15566e0$663b80c7@fsis7.fsis.usda.gov> <34B4F542.4435@sybase.com> <01bd1c55$dfb4ae00$663b80c7@fsis7.fsis.usda.gov> <34B50B81.34FD@sybase.com> <01bd1c5f$19b97fa0$663b80c7@fsis7.fsis.usda.gov> <34B69A95.54A6@sybase.com>
Organization: USDA-FSIS
Message-ID: <01bd1f61$4f7e2dc0$663b80c7@fsis7.fsis.usda.gov>
X-Newsreader: Microsoft Internet News 4.70.1161
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Date: Mon, 12 Jan 1998 07:48:21 -0500
Lines: 25
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3330 sybase.public.sqlserver.general:785 sybase.public.sqlserver.nt:381
Article PK: 1065491


Bret Halford <bret@sybase.com> wrote in article
<34B69A95.54A6@sybase.com>...
> Richard R. Klemmer wrote:
>
> >
> > ------------------
> > Open Client Error: ct_cmd_drop(): user api layer: external error: This
> > routine can be called only if the command structure is idle
> > ------------------
> >
> > Am I, however, able to run the select statement using the
command line
> > ISQL program. Do you have any idea why this may be happening?
>
>
> I'm pretty UNIX centric, so I never get to work with WISQL. But
> it appears WISQL has a 64K buffer; you get this message when it
> overflows.

Yeah, it looks like this only happens when there are large amounts of data
returned. Plus, someone else suggested this was the problem. As long as
the data is imported properly (which it appears to be), then I am happy.
I wish I was(were?) able to be UNIX centric as well. You have my envy,
and my thanks for all your help.