I get this message in both 8203 and 8207 when I do a run wit...

[Debra Waybright]

I get this message in both 8203 and 8207 when I do a run with SQL Trace.

08.33.31.592000 (FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):

08.33.31.592000 (FOC2519) THE JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBSYSTEM

The difference is 8203 will finish running the program and I get my report. In 8207 it fails and I only get an error message. Is there a way to get 8207 to finish running the program Or is it a code tightening thing and we have to rework all our reports that have this issue

By the way, the different nodes are a temporary SQL_Script format file joining to a SQL table.

[Toby Mills]

Morning Deb

Im not sure this is a reasonable answer, but I wondered if it would help try adding FOC2519 to your list of errors to ignore. Id think this should be thought of as a Warning as opposed to Error anyway.

Want to try adding 2510,2519 to your list of errors to convert to warnings

 

image.png1503578 85 KB

 

Also, I think it might be worth rethinking how your code is written It might be more efficient to split these queries up into a MATCH perhaps Youd have to test to know.

Good luck!

[Debra Waybright]

Thanks @toby.mills, Ill give that a try. Cant use MATCH these are reports written by the business folks via InfoAssist. It has just been an interesting journey seeing what ran fine in 8203 but wont run in 8207.

[Debra Waybright]

Well, that was apparently a red herring. The report doesnt show that error any more, but it still fails. The error is

(FOC1500) : SQL OLE DB Interface: Unexpected Error: Attempt to bind Columns of a

(FOC1500) : NULL Rowset Object

(FOC1407) SQL FETCH CURSOR ERROR.

It seems to be something about permanent hold files and/or files we have residing on a different SQL server than most of our reporting tables these are tables that users created using the Upload Data function in 8203. I saw this on another report and got around it by uploading the data as a spreadsheet instead of into a SQL table. Why that worked, I have no idea.

[Toby Mills]

Sounds like its time to get the SQL captured and try plugging it right into SQL Server Management Studio just to make sure that works.

 

 

 

debra.waybright:

 

SQL OLE DB Interface: Unexpected Error: Attempt to bind Columns of a NULL Rowset Object

 

 

That may mean that SQL Server thinks the table is there, but its empty I dont know enough about it. Id start with capturing the SQL and see if it runs okay in SQL Server Management Studio. If that works, then Id make a .fex file on the WF Reporting server - maybe you could use ibiappsbaseapp as a place to put your SQL passthrough attempt for now.

This way you isolate if its really just a SQL Server thing, or if its WebFOCUS. Also you get a nice way to run a short fex to use for server side traces if you need to go that far.

Let me know if you need help with getting the reporting server side fex created top do SQL Passthrough.

[Debra Waybright]

That is a good thought. The SQL that is generated has question marks in it, so a strict copy into SSMS it wont run (eg: WHERE (T3.SK004_AGENCY = ); ). It should be joining to the hold file that was generated from the uploaded SQL table. It is like it doesnt even recognize it for the join

So, heres whats up - a temporary hold file is created from TABLE1 in SQL_Script format. A second temporary hold file is created from TABLE2 in SQL_Script format. A third temporary hold file is created in SQL_Script format, but this one is from the uploaded data. The report joins the three hold files together, but seems to choke on that last hold file, presumably because it is coming from a different node or subsystem. When I run with SQL Trace, I see the SQL generated for all the hold files and the SQL for the report except where the field from the third hold file should be in the WHERE clause, it has .

[Debra Waybright]

Funny story - I thought Id run it with SQL Trace in 8203. I see the same thing! Only WF 8203 doesnt error, it gives the report, but looking closer at the report, the data is suspect. What have my users been doing all this time

Now I have to look closer at all the reports that are getting this error. I have another one I was sure was working fine in 8203 but got this error in 8207. But is it really working in 8203

[Charles Morris 2]

It would not surprise me that the HOLD files from TABLE1 and TABLE2 are being created in one tempspace and the HOLD file from the uploaded data is being created in a different tempspace. Have you tried forcing all of the HOLD files into someplace like foccache The error is suggesting different subsystems for the files which isnt always a gamekiller but will definitely make it inefficient

[Debra Waybright]

@charles.morris I tested that by editing the fex in the text editor and adding SET APPHOLD=foccache at the top. Is that what you meant I still get the NULL Rowset error.

[Charles Morris 2]

or just try ON TABLE HOLD AS foccache/TABLE1

I believe the APP command is APP HOLD foccache, no SET

not sure if SQL_Script files need to be FILEDEFd or not, dont normally use them

[Debra Waybright]

@charles.morris it was a good thought, but trying both of those, I still get the segments reside in more than one node message and the report still fails with the null rowset error.