topic SAS DI Fails with a message when updating Sybase table in SAS Data Management

SAS DI Fails with a message when updating Sybase table

etl_tool — Wed, 06 May 2015 21:26:46 GMT

Hi,

I'm trying to update a timestamp column in sybase. It just throws an error saying

AN EXCEPTION WAS THROWN WHILE SENDING A PACKET TO THE PEER.

iT'S JUST A SIMPLE UPDATE

SAS DATA SET --> TABLE LOADER WITH INSERT/UPDATE AND SKIP NEW ROWS OPTION --> SYBASE TARGET.

Re: SAS DI Fails with a message when updating Sybase table

LinusH — Thu, 07 May 2015 13:35:33 GMT

You need to leave more details, such as the log.

Add these options, maybe Sybase can give feedback before the exception:

options sastrace=',,,d' sastraceloc=saslog;

Perhaps your datatypes doesn't match, giving you an overflow?

Re: SAS DI Fails with a message when updating Sybase table

etl_tool — Thu, 07 May 2015 14:15:17 GMT

Linus,

Thanks, I'm new to SAS. Do you want me to add the options to precode?

Here's the data example.

SAS DATA SET

COLUMN --> TYPE --> INFORMAT --> FORMAT-->DATAEXAMPLE

ID --> CHAR 16 --> $16 --> $16 --> 0534232

LAST_TMSP --> NUMERIC --> DATETIME27.6 -->DATETIME27.6 --> 06may2015:18:22:02.000000

SYBASE DATA

COLUMN --> TYPE -->DATAEXAMPLE

ID --> CHAR 16--> 0534232

LAST_TMSP --> DATETIME --> 7/19/2009 3:36 PM

Re: SAS DI Fails with a message when updating Sybase table

LinusH — Fri, 08 May 2015 06:19:12 GMT

Anywhere just before the insert to Sybase. In your example it seems you have different precision in the datetime column. Check SAS on line doc for data type mapping between SAS and Sybase (ODBC?)

Re: SAS DI Fails with a message when updating Sybase table

SASKiwi — Fri, 08 May 2015 07:49:54 GMT

You say you are updating a TIMESTAMP column but then later say it is DATETIME: LAST_TMSP --> DATETIME --> 7/19/2009 3:36 PM

So which is it? Check the Sybase schema to confirm, not how it appears in SAS.

Re: SAS DI Fails with a message when updating Sybase table

etl_tool — Fri, 08 May 2015 14:33:09 GMT

Hi SASKiwi,

This is the datatype and data. No confusion or mistake in it. I've submitted a SAS Support ticket too

SAS DATA SET

COLUMN --> TYPE --> INFORMAT --> FORMAT-->DATAEXAMPLE

ID --> CHAR 16 --> $16 --> $16 --> 0534232

LAST_TMSP --> NUMERIC --> DATETIME27.6 -->DATETIME27.6 --> 06may2015:18:22:02.000000

SYBASE DATA

COLUMN --> TYPE -->DATAEXAMPLE

ID --> CHAR 16--> 0534232

LAST_TMSP --> DATETIME --> 7/19/2009 3:36 PM

Re: SAS DI Fails with a message when updating Sybase table

mebass — Fri, 08 May 2015 14:41:20 GMT

If it helps, here is the list of supported data types and SAS conversions for Sybase (see LIBNAME Statement Data Conversions on this page):

http://support.sas.com/documentation/cdl/en/acreldb/67589/HTML/default/viewer.htm#n1n0n59fz7nfs2n1syfy8mfyqi9x.htm

Re: SAS DI Fails with a message when updating Sybase table

etl_tool — Fri, 08 May 2015 15:23:18 GMT

I took the whole generated code and execute in SAS Base.

Ok, I think the problem is it is a view in Sybase that I'm trying to update to. It works when you execute the update command in a sybase interactive sql or aqua data studio but does not work from SAS. I will let you know if the problem gets solved when they grant the permission.