Since version 4.1, the database reader no longer works when a SQL procedure is called.

[PatrikS]

Hallo,
The following workspace from Knime 4.01 was started in Knime 4.1 before migration with “with lagacy nodes”.

This worked fine with Knime 4.0.1 if "Run SQL query only during execute, skips configure" was selected.

With Knime 4.1 it no longer works with the lagacy notes.
Execute: Node creat an emty Table

Migrated Workspace to Knime 4.1:

Configuration dialog is new and missing: „Run SQL query only during execute, skips configure „
Evaluate: Error Message Syntax Error near Execute
Execute: Node creat an emty Table

After researching the internet, I tried the Node DB SQL EXECUTE with Microsoft SQL Connector.
But only works in combination with DB Query Reader.
However, the following SQL statement must be entered for both.
EXECUTE [dbo].[SP_X_PS_GetPrognose_GTS_FOR_ALL_WS_By_Date]
$${SDatum_2017}$$
,$${SNGruppe}$$
,$${IPWD_IDX}$$
,$${IAReife}$$
,$${IHistory_1}$$

Should that be the solution? Not very functional because it is prone to errors. Is there another solution?
I hope it is fairly understandable.
Why doesn’t DB Query Reader work like in Knime 4.0.1, or how?
Thank you for your feedback.

Patrik

[tobias.koetter]

Hello Patrik,
I’m not aware of any changes in the legacy database nodes between KNIME 4.0.1 and 4.1. Is it possible that you need to call the procedure twice to have it return a result? What happens if you execute the Database Reader (legacy) node twice with KNIME 4.1?
The evaluation of the statement in the new DB Query Reader node fails since it wraps the statement into a limit query as you can see in the Parsed Statement which will fail. However the node execution should be the same as in the old framework if you have only a single statement which is the case here.
Bye
Tobias

[PatrikS]

Hallo Tobias,
very suspicious, did some tests today to show the problem again.
Suddenly it works without DB SQL Executer with the new nodes.

I don’t know why it suddenly works.

However, with the legacy nodes I do not receive any data from the DB.
But if it doesn’t matter, I have already switched my workspace to the new nodes without DB SQL Executer and it works.