topic Re: Sybase terminate connections in SAS Enterprise Guide

Sybase terminate connections

AbutiThabiso — Wed, 24 Jul 2019 07:43:20 GMT

When I run a proc in SAS EG, I keep getting the errors

ERROR: CLI prepare error: [Sybase][ODBC Driver][Sybase IQ]Connection was terminated

SQL statement: SELECT "FLG_CUST_TYP", "TXT_CUST_TYP" FROM MIGFCR_SNAPSHOT.CI_CUST_TYPES .

ERROR: PROC SQL runtime error for operation=sqxsrc.

ERROR: An error has occurred.

3 The SAS System 08:39 Wednesday, July 24, 2019

ERROR: CLI close cursor error: [Sybase][ODBC Driver][Sybase IQ]Connection was terminated : [Sybase][ODBC Driver][Sybase

IQ]Connection was terminated

ERROR: CLI close cursor error: [Sybase][ODBC Driver][Sybase IQ]Connection was terminated

If I execute the proc again it runs with no issue. Anyone who has experienced in the past please assist.

Re: Sybase terminate connections

SASKiwi — Wed, 24 Jul 2019 07:53:39 GMT

Ask your Sybase DBA to check his database logs for evidence of this problem.

Re: Sybase terminate connections

ballardw — Wed, 24 Jul 2019 16:22:40 GMT

By any chance is there any macro code involved?

I ask because there are a number of timing things that can happen with macro code, such as setting a macro variable in the wrong order, that can cause an error the first time (something not defined for example) but runs the second time because it now has the value set (after needed).

A flat out guess might be a macro variable in a connection string.

It usually helps to show the entire Proc or data step that generates an error from the log along with the error messages, not just the error itself. Paste the log or editor entries into a code box opened on the forum with the {I} or "running man" icons to preserve formatting of text as the message windows here will remove white space and sometimes introduce html that makes code not runnable.

Re: Sybase terminate connections

TomKari — Wed, 24 Jul 2019 17:13:48 GMT

This is the general format that SAS uses to pass back an error message thrown by Sybase IQ, so you need to figure out what's going wrong in the Sybase IQ environment.

In addition to the other comments, which are spot-on, I notice that you appear to be using the ODBC interface to Sybase IQ. SAS has released a native connection engine for IQ, which in my experience worked much better. If possible, I suggest that you acquire that and use it. You may see performance and reliability benefits, as IQ works significantly differently from regular ODBC-type databases.

One problem that I recall was that SAS by default was asking for way too few records to be returned at a time, which was hammering performance.

Note that all of these comments apply to when I was last working with SAS and IQ, circa 2012, so things may have changed.

Tom