Home > Forum > Data sources > CTE SQL no longer usable as Data Source ?

CTE SQL no longer usable as Data Source ?
0

Hi,

ver. 2025.2.1.91

Is there any changes to CTE SQL availability when used as a Data Source? After the update from version. 2025.2.1.42 to 2025.2.1.91 we found that all functions using CTE from Data Sources stopped working.

Strange - when you use CTE in the action config, it still works. Can you confirm whether it is a bug or not ?

Error:

Error in On path action Wgrywa nadgodziny (zrodlo danych). (Step: Rozliczenie nadgodzin, path: Pobierz nadgodziny)
Error getting data for DataSource name: [Pracownicy] wszyscy - poszerzone (Licencje) ID: 597
Multiple concurrent database operations on the same TransactionInstance object are not supported. Additional log with stack trace of all operations is possible.

MVP

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

In reply to: Daniel Krüger (Cosmo Consult)

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)

In reply to: Maksymilian Stachowiak

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.

In reply to: Adrian Motyka

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 :)

In reply to: Maksymilian Stachowiak

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.

In reply to: Damian Kryński

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.

Privacy overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognizing you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.


To see a full list of the cookies we use and learn more about their purposes, visit our Privacy Policy.