f



Error when BCP date field from SQL Server to Sybase

Hello,

I am trying to BCP data out of Sql Server and insert it in to Sybase.
I am having a persistent issues when I come across a DATETIME field.
I have tried using a format file as well as just using the "-c" option
when exporting the data.  This is my BCP format file when I export the
data from SQL (windows)

10.0
7
1	SQLCHAR	0	8	"|"	1	col1
2	SQLCHAR	0	4	"|"	2	col2
3	SQLCHAR	0	4	"|"	3	col3
4	SQLCHAR	0	80	"|"	4	col4
5	SQLCHAR	0	6	"|"	5	col5
6	SQLCHAR	0	26	"|"	6	date
7	SQLCHAR	0	6	"\n"	7	col7

This creates an output file like this -

5090802|0|1|AAA|1.00|Dec 29 1994 12:00:00:000AM|System
5090803|0|1|AAA|1.00|Dec 29 1994 12:00:00:000AM|System
5090804|0|1|AAA|1.00|Jul 22 1993 12:00:00:000AM|System

The input format file on the sybase side looks like this -

10.0
7
1	SYBCHAR	0	8	"|"	1	col1
2	SYBCHAR	0	4	"|"	2	col2
3	SYBCHAR	0	4	"|"	3	col3
4	SYBCHAR	0	80	"|"	4	co4
5	SYBCHAR	0	4	"|"	5	co5
6	SYBCHAR 0	26	"|"	6	date
7	SYBCHAR	0	6	"\n"	7	col7

The error I am getting when I BCP file in to Sybase (unix) looks
something like this -

cs_convert: cslib user api layer: common library error: The conversion/
operation was stopped due to a syntax error in the source field.
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.
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.
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.
bcp copy in failed
Error: BCP Error occurred.

Can anyone please help here??

Thanks,
Jaimin

0
Jaimin
4/16/2007 3:24:31 PM
comp.databases.sybase 3279 articles. 0 followers. Post Follow

4 Replies
667 Views

Similar Articles

[PageSpeed] 14

On Apr 16, 10:24 am, "Jaimin" <jaimins...@gmail.com> wrote:
> Hello,
>
> I am trying to BCP data out of Sql Server and insert it in to Sybase.
> I am having a persistent issues when I come across a DATETIME field.
> I have tried using a format file as well as just using the "-c" option
> when exporting the data.  This is my BCP format file when I export the
> data from SQL (windows)
>
> 10.0
> 7
> 1       SQLCHAR 0       8       "|"   1       col1
> 2       SQLCHAR 0       4       "|"   2       col2
> 3       SQLCHAR 0       4       "|"   3       col3
> 4       SQLCHAR 0       80      "|"   4       col4
> 5       SQLCHAR 0       6       "|"   5       col5
> 6       SQLCHAR 0       26      "|"   6       date
> 7       SQLCHAR 0       6       "\n"  7       col7
>
> This creates an output file like this -
>
> 5090802|0|1|AAA|1.00|Dec 29 1994 12:00:00:000AM|System
> 5090803|0|1|AAA|1.00|Dec 29 1994 12:00:00:000AM|System
> 5090804|0|1|AAA|1.00|Jul 22 1993 12:00:00:000AM|System
>
> The input format file on the sybase side looks like this -
>
> 10.0
> 7
> 1       SYBCHAR 0       8       "|"   1       col1
> 2       SYBCHAR 0       4       "|"   2       col2
> 3       SYBCHAR 0       4       "|"   3       col3
> 4       SYBCHAR 0       80      "|"   4       co4
> 5       SYBCHAR 0       4       "|"   5       co5
> 6       SYBCHAR 0       26      "|"   6       date
> 7       SYBCHAR 0       6       "\n"  7       col7
>
> The error I am getting when I BCP file in to Sybase (unix) looks
> something like this -
>
> cs_convert: cslib user api layer: common library error: The conversion/
> operation was stopped due to a syntax error in the source field.
> 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.
> 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.
> 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.
> bcp copy in failed
> Error: BCP Error occurred.
>
> Can anyone please help here??
>
> Thanks,
> Jaimin

Few options to try here. First, try using the -e option of the BCP
when inserting the data into Sybase table with the batch size of 1 (-b
option) and see if you can get any rows imported at all. If not, try
using the options for the field and row delimiters and then try to
import the data to Sybase.

If none of these work, try writing a DTS package on the SQL Server
side to send the data to Sybase.

0
BS
4/16/2007 5:43:21 PM
Jaimin <jaiminsoni@gmail.com> wrote:

....
> 
> The error I am getting when I BCP file in to Sybase (unix) looks
> something like this -
> 
> cs_convert: cslib user api layer: common library error: The conversion/
> operation was stopped due to a syntax error in the source field.
> CSLIB Message:  - L0/O0/S0/N24/1/0:
> 
> Can anyone please help here??

You should post the bcp commands you are excuting and the layout
of the tables involved.

Luc.

0
Luc
4/17/2007 6:51:56 AM
On Apr 16, 10:24 am, "Jaimin" <jaimins...@gmail.com> wrote:
> Hello,
>
> I am trying to BCP data out of Sql Server and insert it in to Sybase.
> I am having a persistent issues when I come across a DATETIME field.
> I have tried using a format file as well as just using the "-c" option
> when exporting the data.  This is my BCP format file when I export the
> data from SQL (windows)
>
> 10.0
> 7
> 1       SQLCHAR 0       8       "|"   1       col1
> 2       SQLCHAR 0       4       "|"   2       col2
> 3       SQLCHAR 0       4       "|"   3       col3
> 4       SQLCHAR 0       80      "|"   4       col4
> 5       SQLCHAR 0       6       "|"   5       col5
> 6       SQLCHAR 0       26      "|"   6       date
> 7       SQLCHAR 0       6       "\n"  7       col7
>
> This creates an output file like this -
>
> 5090802|0|1|AAA|1.00|Dec 29 1994 12:00:00:000AM|System
> 5090803|0|1|AAA|1.00|Dec 29 1994 12:00:00:000AM|System
> 5090804|0|1|AAA|1.00|Jul 22 1993 12:00:00:000AM|System
>
> The input format file on the sybase side looks like this -
>
> 10.0
> 7
> 1       SYBCHAR 0       8       "|"   1       col1
> 2       SYBCHAR 0       4       "|"   2       col2
> 3       SYBCHAR 0       4       "|"   3       col3
> 4       SYBCHAR 0       80      "|"   4       co4
> 5       SYBCHAR 0       4       "|"   5       co5
> 6       SYBCHAR 0       26      "|"   6       date
> 7       SYBCHAR 0       6       "\n"  7       col7
>
> The error I am getting when I BCP file in to Sybase (unix) looks
> something like this -
>
> cs_convert: cslib user api layer: common library error: The conversion/
> operation was stopped due to a syntax error in the source field.
> 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.
> 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.
> 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.
> bcp copy in failed
> Error: BCP Error occurred.
>
> Can anyone please help here??
>
> Thanks,
> Jaimin

I've had similar issues bcping datetime data into Sybase, from other
sources as well.

I have a small Perl reformatter somewhere, I could share if you are
interested.

Keith

0
Keith
4/17/2007 11:46:28 AM
The problem might not be actually with the datetime field. It is most
probably due to compatibility issues in datatype conversions. Try not
to use a format file. just use -c option in the bcp command line.

Thyagarajan Delli,
http://thyagarajan.blogspot.com

0
dellit
4/18/2007 4:14:31 PM
Reply: