CTE SQL no longer usable as Data Source ?

0

Hi Damian,

I assume something was changed which unfortunately causes this issue. I don't assume that this has been intended.
Did you test whether:
- You Can use the "Test " button of the expression editor of the data source itself?
- You can use the data source for a data table?

There has been one other forum post with this error message but in an unrelated context.
https://community.webcon.com/forum/thread/6425?messageid=6425

Best regards,
Daniel

Hi Daniel,

It looks like the data table is having an error when using the Data Source as well. When I copied and pasted the CTE SQL to the Data Table config, it worked as normal.


Error on Data Table

---> System.Exception: Błąd pobierania danych ze źródła o nazwie: [Vendor] Różnica danych (v3) ID: 138

---> Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ';'.
Incorrect syntax near the keyword 'as'.
Incorrect syntax near ','.
Incorrect syntax near ','.
Incorrect syntax near ','.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Hi Damian,

ok, now we have two different kinds of errors. This definitely looks more like a bug then a feature. :)

Can you open a ticket? We aren't using the version, so I wouldn't be able to answer any questions from the support team.

Best regards,
Daniel

Unfortunately, we do not have an SLA, so I cannot create a ticket.

Hi Damian,
I'm using same veresion, and CTE are used around our configurations too, so i'll post the ticket to Webcon team next week.

Assuming you don't need to fetch data from the current transaction, as a workaround that doesn't require major changes (like changing the data source to a connection at each point of use), I suggest changing the connection in your data source configuration from "current BPS database" to a new connection to the same database. Then the data source won't hook into the transaction and the error won't occur.

Hi,

Thanks for the reply.

Your method works for an action performed on a path, -> but the data source won't hook into the transaction, so it is not possible to work on data in a transaction -> action needs to be moved to next steps where Data Base data is updated -> so it is not good in some scenarios.

UPDATE:
I've already figured out why CTE didn't work on Data Table -> in earlier versions of Webcon semicolon before

;With T1 as
(Select ISNULL(Old,'') as Old, pivotcolumn1
from WFHistoryElements...

It was ok in 2025.2.1.42 - in 2025.2.1.91 it throws an error :)

Glad it's solved, I won't add a ticket then, semicolons are statement terminators in SQL so the behavior seems logical with that added to the context.
Error message isn't pretty, and explaining unfortunately.

Hi,

I hope they will fix this "bug" anyway -> the action using the additional database connection is executed outside the transaction -> so it does not use the data changed during the action. To use the data, it must be previously saved in the database -> so the CTE action must be executed on subsequent steps.

I believe there won’t be any changes to this functionality.
According to the help documentation in WEBCON BPS Designer Studio, such queries are not supported due to the way they are wrapped.
Additionally, the official online documentation confirms that the WITH syntax is not supported:
https://docs.webcon.com/docs/2025R2/Studio/ConnectionsAndDataSource/DataSources/DataSource_MSSQL#12-sql-query

It appears that the fact it worked previously was more of a feature, and in future versions, even potential workarounds may no longer be effective.