Diconnected from DB - ERROR: CLI prepare error: Unable to retrieve error message.

[L5ive]

Hi Guys,

im new with SAS Enterprise so my terminologies might be a little different from yours. i have an ODBC connection to oracle data base. and when i start a new session the library is connected and i can execute any code. however if i left a session open for few hours and try to use it again i get the following Error:

ERROR: CLI prepare error: Unable to retrieve error message.

i will provide you with the necessary info however please guide me how to to get since im not the administrator of the server.

Thank you

[SASKiwi]

The database connection is likely timing out because of the lack of activity. The timeout setting could in the Oracle database, or it could be the consequence of a SAS server firewall port rule setting. There is no default timeout setting at the SAS end.

I suggest you talk to your SAS administrator about getting this behaviour changed. There is no setting at your end that you can apply to fix this. I'm assuming here that the EG Oracle data connection is not created by you with a LIBNAME statement.   

[L5ive]

Hi SASKiwi,

thank you for your reply. i checked with the administrator and they said they have removed the time limit (not sure if they doing it right though).

however the issue persists. in another operation connected to the same DB they use the below code to reconnect to the library

libname KSACT oracle user=WORK pass=TEST path=GMACT schema=work;

however when i only know the libname and i don't know what to write in the rest of the fields (User, Pass,Path & Schema)

could you guide me how to acquire these info from SAS or if their a similar approach to this code to reconnect to the libname.

Thank you,

[Sajid01]

"however when i only know the libname and i don't know what to write in the rest of the fields (User, Pass,Path & Schema) could you guide me how to acquire these info from SAS or if their a similar approach to this code to reconnect to the libname."
The details for user, password and schema will be provided by your team. You should ask your SAS Admin ? Manager to provide them

[SASKiwi]

@L5ive - Please refer to @Sajid01 's response to answer your questions. Most likely your SAS administrator would have tested database connections in LIBNAMEs before defining them in SAS metadata. You should also pass on the connection timeout problem to your administrator as it is really their responsibility to sort out. I also recommend adding the DEFER = YES option to all external database connections. This option delays connecting to the database until it is actually used so it will help reduce timeouts.