We have recently upgraded from v7.6.11 to v8.1.03 and have been testing our queries. One of the query outputs the following error

(FOC1400) SQLCODE IS 8632 (HEX: 000021B8) XOPEN: 42000
: Microsoft OLE DB Provider for SQL Server: [42000] Internal error: An exp
: ression services limit has been reached. Please look for potentially com
: plex expressions in your query, and try to simplify them. [] Deferred pr
: epare could not be completed


The query worked fine previously. Is there a work around to ignore the limitations?.

Thank you in advance.

This message has been edited. Last edited by: WF1326,

There were a number of adapter improvements between 7.7 and 8. It could be that the sql translation has changed, now causing the sql error you are getting. I would run a sql trace on your query to see what is being generated. If that doesn't help get you some answers, post the trace with your code so we can help look at it with you.

Assuming it got that far, can you turn on SQL tracing to see what is being generated. May give a hint to where to look.

Thank you for replies Eric and Waz....

I have used the following trace commands

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
SET XRETRIEVAL = OFF

In the SQL statements, i could not see any possible errors; except for complex and huge case statements being generated. No joins involved.

I had to mention that inside the fex, we were first defining several columns and then getting the data from SQL Server. I am able to get the required result, no errors, with one possible workaround i.e) by holding the required data first and then defining the columns later. This improved the performance as well.

Sadly, our previous way of querying had been used in several fex's and it is hard to find each one and make changes. So, i am looking for an alternate way of looking into the issue.

Please let me know if there are any issues with my trace statements or needed to try a different way.

Please post the result of the SQL trace (and probably the WebFOCUS code) so we can help you further...

13.00.32 AE SELECT
13.00.32 AE (CASE ((CASE (SUBSTRING((CASE WHEN ((CASE WHEN
13.00.32 AE ((T1."IP_PORT_CDE" = '101') AND (T1."STATPORT_CDE" = 'DCSH'))
13.00.32 AE THEN '10Stock Futures' WHEN ((T1."IP_PORT_CDE" = '101') AND
13.00.32 AE (T1."STATPORT_CDE" <> 'DCSH') AND (T1."STATPORT_CDE" = 'BOND'))
13.00.32 AE THEN '00TCIs' WHEN ((T1."IP_PORT_CDE" IN('101', '102')) AND
13.00.32 AE (T1."STATPORT_CDE" <> 'DCSH') AND (T1."PORT_CDE" NOT IN('NON$',
13.00.32 AE 'FS1', 'FS2')) AND (T1."STATPORT_CDE" = 'TCI')) THEN '00TCIs'
13.00.32 AE WHEN (T1."IP_PORT_CDE" = '202') THEN '00Misc. Accts.
13.00.32 AE Receivable*' WHEN (T1."IP_PORT_CDE" = '134') THEN '00Cash in
13.00.32 AE Bank*' WHEN ((T1."INVESTOR_CDE" = 'NLTC') AND (T1."IP_PORT_CDE"
13.00.32 AE = '102') AND (T1."PORT_CDE" = 'MGD') AND (T1."TAX_EXEMP_TXT" =
13.00.32 AE 'Taxable/US Treas Instrumnt')) THEN '04Government' WHEN
13.00.32 AE ((T1."IP_PORT_CDE" = '102') AND (T1."PORT_CDE" NOT IN('CNVF',
13.00.32 AE 'FS1', 'FS2'))) THEN '01Public Bonds' WHEN ((T1."IP_PORT_CDE" =
13.00.32 AE '103') AND (T1."PORT_CDE" NOT IN('FS1', 'FS2'))) THEN '02Public
13.00.32 AE Preferred Stock' WHEN (T1."IP_PORT_CDE" = '104') THEN
13.00.32 AE '03Private Placement Notes' WHEN (T1."IP_PORT_CDE" = '105')
13.00.32 AE THEN '04Private Preferred Stock' WHEN (T1."IP_PORT_CDE"
13.00.32 AE IN('106', '107')) THEN '05Non J.V. Mortgage Loans' WHEN
13.00.32 AE ((T1."IP_PORT_CDE" = '123') AND (T1."PORT_CDE" <> 'EMER')) THEN
13.00.32 AE '16Public High Yield Bonds' WHEN ((T1."IP_PORT_CDE" IN('303',
13.00.32 AE '304')) AND (T1."INVESTOR_CDE" = 'NLTC')) THEN '17Private
13.00.32 AE Mezzanine' WHEN (T1."PORT_CDE" = 'CNVF') THEN '21Public
13.00.32 AE Converts Fixed' WHEN (T1."IP_PORT_CDE" = '303') THEN
13.00.32 AE '19Mezzanine' ELSE '????' END) <> '????') THEN (CASE WHEN
13.00.32 AE ((T1."IP_PORT_CDE" = '101') AND (T1."STATPORT_CDE" = 'DCSH'))
13.00.32 AE THEN '10Stock Futures' WHEN ((T1."IP_PORT_CDE" = '101') AND
13.00.32 AE (T1."STATPORT_CDE" <> 'DCSH') AND (T1."STATPORT_CDE" = 'BOND'))
13.00.32 AE THEN '00TCIs' WHEN ((T1."IP_PORT_CDE" IN('101', '102')) AND
13.00.32 AE (T1."STATPORT_CDE" <> 'DCSH') AND (T1."PORT_CDE" NOT IN('NON$',
13.00.32 AE 'FS1', 'FS2')) AND (T1."STATPORT_CDE" = 'TCI')) THEN '00TCIs'
13.00.32 AE WHEN (T1."IP_PORT_CDE" = '202') THEN '00Misc. Accts.
13.00.32 AE Receivable*' WHEN (T1."IP_PORT_CDE" = '134') THEN '00Cash in
13.00.32 AE Bank*' WHEN ((T1."INVESTOR_CDE" = 'NLTC') AND (T1."IP_PORT_CDE"
13.00.32 AE = '102') AND (T1."PORT_CDE" = 'MGD') AND (T1."TAX_EXEMP_TXT" =
13.00.32 AE 'Taxable/US Treas Instrumnt')) THEN '04Government' WHEN
13.00.32 AE ((T1."IP_PORT_CDE" = '102') AND (T1."PORT_CDE" NOT IN('CNVF',
13.00.32 AE 'FS1', 'FS2'))) THEN '01Public Bonds' WHEN ((T1."IP_PORT_CDE" =
13.00.32 AE '103') AND (T1."PORT_CDE" NOT IN('FS1', 'FS2'))) THEN '02Public
13.00.32 AE Preferred Stock' WHEN (T1."IP_PORT_CDE" = '104') THEN
13.00.32 AE '03Private Placement Notes' WHEN (T1."IP_PORT_CDE" = '105')
13.00.32 AE THEN '04Private Preferred Stock' WHEN (T1."IP_PORT_CDE"
13.00.32 AE IN('106', '107')) THEN '05Non J.V. Mortgage Loans' WHEN
13.00.32 AE ((T1."IP_PORT_CDE" = '123') AND (T1."PORT_CDE" <> 'EMER')) THEN
13.00.32 AE '16Public High Yield Bonds' WHEN ((T1."IP_PORT_CDE" IN('303',
13.00.32 AE '304')) AND (T1."INVESTOR_CDE" = 'NLTC')) THEN '17Private
13.00.32 AE Mezzanine' WHEN (T1."PORT_CDE" = 'CNVF') THEN '21Public
13.00.32 AE Converts Fixed' WHEN (T1."IP_PORT_CDE" = '303') THEN
13.00.32 AE '19Mezzanine' ELSE '????' END) WHEN ((CASE WHEN
13.00.32 AE (T1."IP_PORT_CDE" = '207') THEN '65Private Equity' WHEN
13.00.32 AE (T1."IP_PORT_CDE" = '210') THEN '62Limited Partnerships' WHEN
13.00.32 AE (T1."IP_PORT_CDE" IN('214', '216', '209')) THEN '65Private
13.00.32 AE Equity' WHEN ((T1."IP_PORT_CDE" = '304') AND (T1."INVESTOR_CDE"
13.00.32 AE <> 'NLTC')) THEN '67Private Mezzanine' WHEN (T1."IP_PORT_CDE" =
13.00.32 AE '137') THEN '66Currency Swaps' WHEN (T1."IP_PORT_CDE" = '217')
13.00.32 AE THEN '64Sec. Dept. Pub Stock' WHEN (T1."IP_PORT_CDE" = '219')
13.00.32 AE THEN '08Fixed Income Partnerships' WHEN ((T1."IP_PORT_CDE" =
13.00.32 AE '222') AND (T1."PORT_CDE" IN('HYLD', 'HY2'))) THEN '16Public
13.00.32 AE High Yield Bonds' ELSE '????' END) <> '????') THEN (CASE WHEN
13.00.32 AE (T1."IP_PORT_CDE" = '207') THEN '65Private Equity' WHEN
13.00.32 AE (T1."IP_PORT_CDE" = '210') THEN '62Limited Partnerships' WHEN
13.00.32 AE (T1."IP_PORT_CDE" IN('214', '216', '209')) THEN '65Private
13.00.32 AE Equity' WHEN ((T1."IP_PORT_CDE" = '304') AND (T1."INVESTOR_CDE"
13.00.32 AE <> 'NLTC')) THEN '67Private Mezzanine' WHEN (T1."IP_PORT_CDE" =
13.00.32 AE '137') THEN '66Currency Swaps' WHEN (T1."IP_PORT_CDE" = '217')
13.00.32 AE THEN '64Sec. Dept. Pub Stock' WHEN (T1."IP_PORT_CDE" = '219')
13.00.32 AE THEN '08Fixed Income Partnerships' WHEN ((T1."IP_PORT_CDE" =
13.00.32 AE '222') AND (T1."PORT_CDE" IN('HYLD', 'HY2'))) THEN '16Public
13.00.32 AE High Yield Bonds' ELSE '????' END) WHEN ((T1."IP_PORT_CDE" =
13.00.32 AE '206') AND (T1."PORT_CDE" NOT IN('FN', 'FS1', 'FS2', 'XXXX',
13.00.32 AE 'GTE', 'ME1', 'SE1', 'EMER', 'CNVE'))) THEN '31Public Stock'
13.00.32 AE WHEN ((T1."IP_PORT_CDE" = '206') AND (T1."PORT_CDE" IN('GTE',
13.00.32 AE 'ME1', 'SE1'))) THEN '32Tactical Stock' WHEN ((T1."IP_PORT_CDE"
13.00.32 AE IN('206', '218')) AND (T1."DER_TYPE_CDE" = '03')) THEN
13.00.32 AE '33Covered Calls**' WHEN ((T1."IP_PORT_CDE" IN('206', '218'))
13.00.32 AE AND (T1."DER_TYPE_CDE" = '39') AND (T1."EFF_CNTRY_CDE" =
13.00.32 AE 'USA')) THEN '37Equity Swaps-Replication' WHEN
13.00.32 AE ((T1."IP_PORT_CDE" = '218') AND (T1."PORT_CDE" IN('LC', 'MID',
13.00.32 AE 'SC'))) THEN '34Futures' WHEN (T1."IP_PORT_CDE" = '137') THEN
13.00.32 AE '66Curency Swaps' WHEN (T1."PORT_CDE" = 'CNVE') THEN '35Public
13.00.32 AE Converts Equity' WHEN ((T1."IP_PORT_CDE" = '206') AND
13.00.32 AE (T1."PORT_CDE" = 'FN')) THEN '56Foreign - Developed Markets'
13.00.32 AE WHEN ((T1."IP_PORT_CDE" IN('222', '206', '123')) AND
13.00.32 AE (T1."PORT_CDE" = 'EMER')) THEN '57Foreign - Emerging Markets'
13.00.32 AE WHEN (T1."PORT_CDE" IN('FS1', 'FS2')) THEN '58Foreign -
13.00.32 AE Sub-advised' WHEN ((T1."IP_PORT_CDE" IN('206', '218')) AND
13.00.32 AE (T1."DER_TYPE_CDE" = '39') AND (T1."EFF_CNTRY_CDE" <> 'USA'))
13.00.32 AE THEN '59Equity Swaps-Replication' WHEN (T1."IP_PORT_CDE"
13.00.32 AE IN('201', '203')) THEN '61Real Estate Equities' WHEN
13.00.32 AE (T1."IP_PORT_CDE" = '205') THEN '41REIT Stock' WHEN
13.00.32 AE ((T1."IP_PORT_CDE" = '213') AND ((CASE ((CASE WHEN
13.00.32 AE (T1."PORT_CDE" IN('XX', 'SML')) THEN 'SC' WHEN (T1."PORT_CDE" =
13.00.32 AE 'BASE') THEN 'B ' WHEN (T1."PORT_CDE" = 'REIT') THEN 'RE' WHEN
13.00.32 AE (T1."IP_PORT_CDE" = '205') THEN 'RE' ELSE T1."PORT_CDE" END))
13.00.32 AE WHEN ' ' THEN 'No STKPORT_CODE' WHEN 'AG' THEN 'Agressive
13.00.32 AE Growth' WHEN 'ASA' THEN 'Asian Stock' WHEN 'B ' THEN 'Base'
13.00.32 AE WHEN 'CNVE' THEN 'Convertibles - Equity' WHEN 'COMD' THEN
13.00.32 AE 'Strategic Commodities' WHEN 'DET' THEN 'Directed Equity
13.00.32 AE Tactical' WHEN 'EM' THEN 'Emerging Market' WHEN 'EMER' THEN
13.00.32 AE 'Emerging Market' WHEN 'FN' THEN 'Foreign' WHEN 'FS1' THEN
13.00.32 AE 'Foreign' WHEN 'FS2' THEN 'Foreign' WHEN 'GTE' THEN 'General
13.00.32 AE Tactical Equity' WHEN 'IN' THEN 'S & P 500 Index' WHEN 'LC'
13.00.32 AE THEN 'Large Cap' WHEN 'ME1' THEN 'MSA Directive Equity 1' WHEN
13.00.32 AE 'ME2' THEN 'MSA Directive Equity 2' WHEN 'MID' THEN 'Mid Cap'
13.00.32 AE WHEN 'MLPT' THEN 'Master Limited Partnerships' WHEN 'MLRE' THEN
13.00.32 AE 'Mortgage Loan Real Estate' WHEN 'MM' THEN 'Multifactor Market'
13.00.32 AE WHEN 'MP' THEN 'Multifactor Plus' WHEN 'MS' THEN 'Morgan
13.00.32 AE Stanley Loan' WHEN 'NIS' THEN 'Foreign' WHEN 'OS' THEN 'Oil

This message has been edited. Last edited by: WF1326,

That certainly doesn't look efficient! Let's see the WF code...

Also, here are the trace commands I use. SET TRACESTAMP to OFF so that the time is not added to the trace - makes it a lot easier to copy/paste the SQl into a SQL client.

-*** Set up SQL tracing

-*** Deactivate SQL tracing
SET TRACEOFF = ALL

-*** Show Commands and data exchange between thephysical and
-*** the logical layers of the data adapter
-*SET TRACEON = SQLCALL

-*** Enable Trace for the SQL Translator
SET TRACEON = SQLTRANS

-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT

-*** Show Optimization information
SET TRACEON = SQLAGGR//CLIENT

-*** Show SQL generated statement trace
-*SET TRACEON = STMTRACE/1/CLIENT

-*** Show SQL generated sub-statement trace
-*SET TRACEON = STMTRACE/2/CLIENT

-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF

-*** Set trace line wrapping - # of characters
SET TRACEWRAP = 132

-*** Set trace to output file
-*SET TRACEUSER=C:\ibi\apps\baseapp\my_sql_trace.txt

-*** Activate SQL tracing
SET TRACEUSER = ON

-RUN

This message has been edited. Last edited by: Francis Mariani,

As the message says, you need to simplify the SQL query.

You didn't post your WF code, and your trace is truncated. It appears you issue a set of DEFINEs against your SQL Server source, and your TABLE request pulls columns that are the result of a complex cascade of defined items. (Hence the heavily nested, repetitious CASE structures in the generated SQL)

See whether you can just pull the underlying real fields, hold them, and then issue the defines against the HOLD file. -- Much depends on whether you are utilizing the defined fields for screening and aggregation. Since the code you posted is cut off before the FROM clause, we cannot really tell.

Jack,

I agree with you and, as mentioned previously, i actually got it working by holding the required data first and then defining the columns later. This improved the performance as well.

Sadly, our old way of querying had been used in several fex's and it is hard to find each one of them and make changes.

Also, the program did not return an errors until we switched to WebFocus V8. So just wondering whether there is a quick fix , like a set statement, to ignore any limitations.


Sorry for the trace being truncated. The trace is like 3500 lines and I think there is a limit to the number of lines being posted in a message.

Hey.. i found a solution to my issue.

Just need to add an optimization setting for aggression control on DEFINE statements

SQL SQLMSS SET OPT NOAGGR


Thank you for your replies and suggestions.

Yes, those passive-aggressive Defines can get quite unruly.

Francis,

Thanks for the SQL Tracing code.

Never can find it when you need it.

Also good to know that you can turn off the timestamp on the SQL.

quote:

Originally posted by Waz:
Francis,

Thanks for the SQL Tracing code.

Never can find it when you need it.

Also good to know that you can turn off the timestamp on the SQL.

I have those stored in a file name sqltrace.fex and use a -INCLUDE whenever I need them.

-*** Set trace to output file
-SET TRACEUSER=C:\ibi\apps\baseapp\my_trace_file.txt

This is something I learned about only recently - you can redirect the SQL trace to a file on the reporting server.