New Parameterized DB Query Reader do not work as before

KNIME Analytics Platform
1

In ver 3.7.2 It worked with below query. Now it gives a message -
Cannot retrieve an output data specification. Caused by incorrect syntax near the keyword ‘exec’
Please see text below

exec(’
SELECT Specialty_c,Specialty_N,E_M_CPT,E_M_Type,E_M_Weight
, DateName( mm , DateAdd( mm , Month , -1 ) ) as Y_Month,Year
from BR_to_Trans a
where [Year] between
Year(’’$${S@BaseD_From}$$’’) and Year(’’$${S@MonthD_To}$$’’)
and specialty_N in (’ + $Concatenate(T_Specialty)$ + ‘)’
)

I need to make it work urgently.
Please advise.

2

Hi,
Which database system are you trying to query?
Kind regards
Alexander

3

I use SQL Server connector and DB Table Selector before Parametrized DB Query Reader.

4

Hi,
I don’t find any information on the exec function anywhere online. Can you provide a link to some documentation so I can see what it does and try it out myself?
Kind regards
Alexander

5

Here we go
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver15

6

Hi,
I found that but why do you need to use it here? From the documentation it seems like EXEC is used to execute a stored procedure, but you simply want to query the database, don’t you? Why don’t you remove the exec() completely simply insert the parameters using the KNIME placeholders? Additionally it seems like it should be EXEC ‘…’ and not exec(…). Can you try it?
Kind regards
Alexander

7

@AlexanderFillbrunn, I have a versions compatibility issue. Exec used because I use dynamic parameter and KNIME placeholders simply do not work.
$Concatenate(T_Specialty)$ has a string like ‘Val1’, ‘Val2’, ‘Val3’

8

Hi,
unfortunately I don’t have access to an MS SQL Server right now. What if you use EXEC ‘’. Does that work?
Kind regards
Alexander

9

SQL Server is not case sensitive. Could you please forward this to developers to look why compatibility is lost.

10

Hi,
it’s not about the case, but about the parentheses. Could you try exec ‘…’ without the parentheses? I will let the DB developers know that something seems to have changed here!
Kind regards
Alexander

11

No it does not. Also, please report my previous post on long string incompatibility in DB Writer. Thank you.

12

Hi,
you can still make it work without the exec() by building the query using a String Manipulation node and then feeding it into the Parameterized DB Query Reader node. There you can then make use of it using the Flow Variables tab.
Kind regards
Alexander

13

Thank you, @AlexanderFillbrunn. I switched, so far, to DB Query Reader. But still question from my previous post on a long string varchar(6000) did not been reviewed. Could you please pass it to developers as well?

14

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.