Hi everybody,

I am trying to feed some dropdown list boxes using SQL PASSTHROUGH fex procedure, but I got the following errors:


Error Parsing MASTER File SQLOUT((FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT(205))

AND

(FOC1400) SQLCODE is 4145 (HEX:00001031) XOPEN:42000L (FOC1405) SQL PREPARE ERROR


Note that:
1) when I run the fex procedure by itself it works without any issue
2) when I run the html page that uses such SQL queries for feeding the dropdown list, it works without any issue

The code for the external procedure called in the property panel is:

 
-SET &ECHO=ALL;
ENGINE SQLMSS SET DEFAULT_CONNECTION RVHSSQLENT_Reporting
SQL SQLMSS PREPARE SQLOUT FOR
SELECT DISTINCT Period
  FROM [RVHS_FIN_DIM_FACTS].[dbo].[FACT_ExpenseCostCentres_GL]
  JOIN [RVHS_FIN_DIM_FACTS].dbo.DIM_InternalGLAccount ON [fkInternal_GLAccount] = [skInternal_GLAccount]
  JOIN [RVHS_FIN_DIM_FACTS].dbo.DIM_CostCentre ON fkCostCentre=DIM_CostCentre.skCostCentre
  JOIN [RVHS_FIN_DIM_FACTS].dbo.DIM_ResponsibilityReporting ON fkResponsibilityReporting=skResponsibilityReporting
  LEFT JOIN [RVHS_Mapping].dbo.MAN_FIN_User ManagerTable ON Meditech_Lv2_Responsibility = ManagerTable.Name
  LEFT JOIN [RVHS_Mapping].dbo.MAN_FIN_User DirectorTable ON Meditech_Lv3_Responsibility = DirectorTable.Name
  LEFT JOIN [RVHS_Mapping].dbo.MAN_FIN_User VicePresidentTable ON Meditech_Lv4_Responsibility = VicePresidentTable.Name
  WHERE ManagerTable.Account = '&IBIMR_user.EVAL' OR
        ManagerTable.ExtraAccount = '&IBIMR_user.EVAL' OR
		DirectorTable.Account = '&IBIMR_user.EVAL' OR
        DirectorTable.ExtraAccount = '&IBIMR_user.EVAL' OR
		VicePresidentTable.Account = '&IBIMR_user.EVAL' OR
        VicePresidentTable.ExtraAccount = '&IBIMR_user.EVAL' OR
		(SELECT MAX(Administrator) FROM [RVHS_Mapping].dbo.MAN_FIN_User WHERE Account = '&IBIMR_user.EVAL')=1
END
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLPRD
END
-*
TABLE FILE SQLPRD
PRINT Period
BY Period
ON TABLE PCHOLD FORMAT XML
END

 

Does someone have an idea?

Thanks in advance for your help

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

If it works when you run it on its own and when it's called by the html page, when does it NOT run?

You may code the SQL a little differently:

ENGINE SQLMSS SET DEFAULT_CONNECTION RVHSSQLENT_Reporting
SQL SQLMSS
SELECT DISTINCT Period
  FROM [RVHS_FIN_DIM_FACTS].[dbo].[FACT_ExpenseCostCentres_GL]
  JOIN [RVHS_FIN_DIM_FACTS].dbo.DIM_InternalGLAccount ON [fkInternal_GLAccount] = [skInternal_GLAccount]
  JOIN [RVHS_FIN_DIM_FACTS].dbo.DIM_CostCentre ON fkCostCentre=DIM_CostCentre.skCostCentre
  JOIN [RVHS_FIN_DIM_FACTS].dbo.DIM_ResponsibilityReporting ON fkResponsibilityReporting=skResponsibilityReporting
  LEFT JOIN [RVHS_Mapping].dbo.MAN_FIN_User ManagerTable ON Meditech_Lv2_Responsibility = ManagerTable.Name
  LEFT JOIN [RVHS_Mapping].dbo.MAN_FIN_User DirectorTable ON Meditech_Lv3_Responsibility = DirectorTable.Name
  LEFT JOIN [RVHS_Mapping].dbo.MAN_FIN_User VicePresidentTable ON Meditech_Lv4_Responsibility = VicePresidentTable.Name
  WHERE ManagerTable.Account = '&IBIMR_user.EVAL' OR
        ManagerTable.ExtraAccount = '&IBIMR_user.EVAL' OR
		DirectorTable.Account = '&IBIMR_user.EVAL' OR
        DirectorTable.ExtraAccount = '&IBIMR_user.EVAL' OR
		VicePresidentTable.Account = '&IBIMR_user.EVAL' OR
        VicePresidentTable.ExtraAccount = '&IBIMR_user.EVAL' OR
		(SELECT MAX(Administrator) FROM [RVHS_Mapping].dbo.MAN_FIN_User WHERE Account = '&IBIMR_user.EVAL')=1

TABLE ON TABLE HOLD AS SQLPRD
END
-*
TABLE FILE SQLPRD
PRINT Period
BY Period
ON TABLE PCHOLD FORMAT XML
END

Hi Francis,

Thanks to help me on this, I much appreciate.

answering to your question:

quote:

If it works when you run it on its own and when it's called by the html page, when does it NOT run?

1) it runs on its own (I can see the XML file with the result I am expecting)
2) when I open the composer I got the errors I posted. This leads to crash in some cases
3) when I run the html page from inside the composer, or normally. it works.

the point is that the errors keeping showing up in the composer, and is frustrating each time I make some change, or go in the parameters panel.

I did change the code as you suggested, Francis; but now it also does not work on its own, here the result (maybe, I did something wrong... I am getting so many headaches to figure this out )

Thanks again for your support

 
--------------------------------------------------------------------------------

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request. 


--------------------------------------------------------------------------------


 ENGINE SQLMSS SET DEFAULT_CONNECTION RVHSSQLENT_Reporting
 SQL SQLMSS
 -*PREPARE SQLOUT FOR
 SELECT DISTINCT Period
 FROM [RVHS_FIN_DIM_FACTS].[dbo].[FACT_ExpenseCostCentres_GL]
 JOIN [RVHS_FIN_DIM_FACTS].dbo.DIM_InternalGLAccount ON [fkInternal_GLAccount] = [skInternal_GLAccount]
 JOIN [RVHS_FIN_DIM_FACTS].dbo.DIM_CostCentre ON fkCostCentre=DIM_CostCentre.skCostCentre
 JOIN [RVHS_FIN_DIM_FACTS].dbo.DIM_ResponsibilityReporting ON fkResponsibilityReporting=skResponsibilityReporting
 LEFT JOIN [RVHS_Mapping].dbo.MAN_FIN_User ManagerTable ON Meditech_Lv2_Responsibility = ManagerTable.Name
 LEFT JOIN [RVHS_Mapping].dbo.MAN_FIN_User DirectorTable ON Meditech_Lv3_Responsibility = DirectorTable.Name
 LEFT JOIN [RVHS_Mapping].dbo.MAN_FIN_User VicePresidentTable ON Meditech_Lv4_Responsibility = VicePresidentTable.Name
 WHERE ManagerTable.Account = 'dbarone'
 OR ManagerTable.ExtraAccount = 'dbarone'
 OR DirectorTable.Account = 'dbarone'
 OR DirectorTable.ExtraAccount = 'dbarone'
 OR VicePresidentTable.Account = 'dbarone'
 OR VicePresidentTable.ExtraAccount = 'dbarone'
 OR (SELECT MAX(Administrator) FROM [RVHS_Mapping].dbo.MAN_FIN_User WHERE Account = 'dbarone')=1
 -*END
 -*TABLE FILE SQLOUT
 -*PRINT *
 -*ON TABLE HOLD AS SQLPRD
 -*END
 -*TABLE FILE SQLPRD
 -*PRINT Period
 -*BY Period
 -*ON TABLE PCHOLD FORMAT XML
 -*END
 TABLE ON TABLE HOLD AS SQLPRD
 END
 -*
 TABLE FILE SQLPRD
 PRINT Period
 BY Period
 ON TABLE PCHOLD FORMAT XML
 END
 (FOC1400) SQLCODE IS 156 (HEX: 0000009C) XOPEN: 42000
 : Microsoft SQL Server Native Client 10.0: [42000] Incorrect syntax near t
 : he keyword 'TABLE'. [42000] Statement(s) could not be prepared. [] Defer
 : red prepare could not be completed.
 L    (FOC1405) SQL PREPARE ERROR.
 0 ERROR AT OR NEAR LINE     33  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLPRD
 BYPASSING TO END OF COMMAND

Sorry - you need a semi-colon on a line just before TABLE ON TABLE:

OR VicePresidentTable.Account = 'dbarone'
OR VicePresidentTable.ExtraAccount = 'dbarone'
OR (SELECT MAX(Administrator) FROM [RVHS_Mapping].dbo.MAN_FIN_User WHERE Account = 'dbarone')=1
;
TABLE ON TABLE HOLD AS SQLPRD
END

I have still the issue, Francis. Any clue? I am lost
thanks for your support!

Daniele, I hope you have the first issue, not the second one, which I caused you.

You could try the following:

In Dev Studio, open the html file in HTML Composer.

Click on "Tools" in the toolbar at the top and select "Settings...".

Deselect the "Report and Graph Preview" checkbox and click OK.

Close the file (without saving).

Reopen the file. I would expect HTML Composer to not run the external procedure, but I am not 100% sure I am right.

The error you get is probably because the SQL is a little complex - the nested select (select within a select) is perhaps confusing HTML Composer...

Hi Francis,
thanks again for your answers and sorry for my late answer.

Your solution solved the error FOC1400, but I have still the error FOC205, it does not find the master file, although when I run it works.

Any clue?