DPR-ERR-2056 when joining Oracle and SQL Server Queries

[cfunkho]

In Report Studio 8.4, I have one query (A) that runs against a SQL Server database, another query (B) that runs against an Oracle database, and a third query (C) that joins queries A and B together (it's a rather routine join).

Queries A and B run just fine separately -- I can view tabular data for either of them.  But when I try to run Query C, I consistently get the DPR-ERR-2056 The Report Server is Not Responding error.

Any thoughts on how to avoid this particular error?  Is it a limitation within Cognos that I can't join queries that run against both Oracle and SQL Server?  Or am I having some other problem?

Thanks,
Chris Funkhouser

[MFGF]

If I had to guess, I would imagine that one or both queries are fetching large volumes of data. Cross-database joins like this have to be performed locally on the Cognos server, and require potentially large amounts of temporary workspace. If you filter each query as a test and re-run the report, do you still see the error?

MF.


Sent from my iPad using Tapatalk

[cfunkho]

I've filtered it down so that each query only returns one row of data, and I still get the error.

Chris F.

[MFGF]

Hmmm. Ok. It wasn't that, then! :)

If you check the columns you are using to join the data, are they the same overall data type? Just wondering if one is a string the the other numeric, for example?

MF.

[CognosPaul]

A few other ideas, and things to check. (off the top of my head, I may have more later if none of these pan out)

Is the data source for the SQL server using the native client?
What happens if you try to connect data from two separate SQL Server databases?
Ditto for Oracle.
Do you have any settings that prevents local processing, maybe in the query hints or the framework governors?
If you use the Oracle and SQL queries as sub queries, drag in a new query to the query explorer for each and drag the SQL/Oracle queries to the right of the newly created ones, to create the join, does the error still occur?
Are these queries handwritten SQL? If so, do all of the fields have aliases, like sum(whatever) as 'whatever'?
Are you using any windowing, analytical, or any other nonstandard function in either of the queries?
Did you remember to sacrifice a goat to Cognos before you started?
Do you get expected results when you click on "show generated sql" for the joined query?
What happens if you create two new queries, each with one field, and try to join or union those?
Are there any further details in the cogserver log?
If you run a trace, do you see any DB activity before the error is thrown?

[cfunkho]

<<If you check the columns you are using to join the data, are they the same overall data type? Just wondering if one is a string the the other numeric, for example?>>
They are different data types (char vs. numeric), but casting both of them to varchar doesn't alleviate the problem.

<<Is the data source for the SQL server using the native client? >>
Yes.

<<What happens if you try to connect data from two separate SQL Server databases? Ditto for Oracle.>>
Don't have any problems joining two separate data sources in those contexts.

<<Do you have any settings that prevents local processing, maybe in the query hints or the framework governors?>>
Nope.

<<If you use the Oracle and SQL queries as sub queries, drag in a new query to the query explorer for each and drag the SQL/Oracle queries to the right of the newly created ones, to create the join, does the error still occur?>>
If I drag a new query in, and drag one subquery to its right, it works for both the SQL Server and Oracle queries.  But if I drag a new query, and drag a join, and join both subqueries, it fails.

<<Are these queries handwritten SQL? If so, do all of the fields have aliases, like sum(whatever) as 'whatever'?>>
The SQL Server query is, and it does.  Here's the query:

select distinct "Fiscal_Year"."keyvaluechar" AS "Fiscal_Year", "Period"."keyvaluechar" AS "Period", "Receipt_Date"."keyvaluedate" AS "Receipt_Date", "Voucher_Number"."keyvaluechar" AS "Voucher_Number" from "onbase"."hsi"."keytable123" "Fiscal_Year", "onbase"."hsi"."keytable124" "Period", "onbase"."hsi"."keyitem162" "Receipt_Date", "onbase"."hsi"."keytable112" "Voucher_Number", "onbase"."hsi"."keyxitem124" "keyxitem124", "onbase"."hsi"."itemdata" "itemdata", "onbase"."hsi"."keyxitem123" "keyxitem123", "onbase"."hsi"."keyxitem112" "keyxitem112"
where "keyxitem123"."keywordnum" = "Fiscal_Year"."keywordnum" and "itemdata"."itemnum" = "keyxitem123"."itemnum" and "keyxitem124"."itemnum" = "itemdata"."itemnum" and "keyxitem124"."keywordnum" = "Period"."keywordnum" and "itemdata"."itemnum" = "keyxitem112"."itemnum" and "keyxitem112"."keywordnum" = "Voucher_Number"."keywordnum" and "Receipt_Date"."itemnum" = "itemdata"."itemnum"

<<Are you using any windowing, analytical, or any other nonstandard function in either of the queries?>>
No.

<<Did you remember to sacrifice a goat to Cognos before you started?>>
You know, it's a suburban area, we're not allowed to have farm animals...

<<Do you get expected results when you click on "show generated sql" for the joined query?>>
No, that fails as well.  Sometimes the error message is slightly different: "DPR-ERR-2077 The request failed because the associated report service process with pid 2936 is unavailable."

<<What happens if you create two new queries, each with one field, and try to join or union those?>>
Union works.  Join fails with the same error.

<<Are there any further details in the cogserver log?>>
My cogserver log hasn't recorded anything for months, and I don't know why.

<<If you run a trace, do you see any DB activity before the error is thrown?>>
Unfortunately, I don't have the ability to run traces on these databases.

Thanks!
Chris F.

[cfunkho]

Fixed it!  Changing the data source for the SQL server query from native to pass-through did the trick!

Chris F.