ID #1026

Sybase outer joins and ANSI joins cannot be mixed in the same query

Running a report based on a sybase database can result in the following error:

Exception: DBD, [Server SYB_FNET_PROD39] [Procedure CTDynamic] Adaptive Server cannot perform the requested action. Sybase outer joins and ANSI joins cannot be mixed in the same query. Rewrite the command as specified in the error message. State: ZZZZZ

Exception: DBD, ct_dynamic(DEALLOC): user api layer: external error: The specified id does not exist on this connection.State: ZZZZZ


This is caused by wrong SQL generation as you will get JOINS in the FROM clause!

The default sybase.prm file located at <drive>\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\dataAccess\connectionServer\sybase\sybase.prm needs to be changed to get the SQL generated correctly.

  • Remove the following line <Parameter Name="OUTERJOINS_GENERATION">ANSI_92</Parameter>
  • Change the following line <Parameter Name="EXT_JOIN_INVERT">YES</Parameter> to <Parameter Name="EXT_JOIN_INVERT">NO</Parameter>
  • Add the following lines <Parameter Name="LEFT_OUTER">$*</Parameter> and <Parameter Name="RIGHT_OUTER">*$</Parameter>

Close and restart Deski after changing the sybase.prm file, the SQL should be generated correctly now.

Mind the following. For using DESKI in ZABO mode make sure you edit all the servers in the @cluster where you are connecting to and RESTART the connection servers!!! Otherwise the old (original) sybase.prm config is still cached and can result in random results! Be careful with setting the universe parameter "ANSI92 to Yes", it seems that it is bypassing the sybase.prm file making you go round and round in trouble shooting.

Sybase middleware used is Sybase Open Client 12.0 and Sybase IQ 12.43.

Tags: Adaptive Server, ANSI, cannot, joins, mixed, outer, same query, Server SYB_FNET_PROD39, State: ZZZZZ, Sybase

Related entries:

You can comment this FAQ