EnsLib.SQL.OutboundAdapter cannot select from temporary table
Hi,
I encountered a strange issue I just can’t solve and I am running out of ideas. After reading the relevant passages of the documentation, asking the community is my last resort.
The task itself is not complicated at all. I am using an EnsLib.SQL.OutboundAdapter to connect to a MS SQL Server (12.0.6433.1) and execute a simple query. Said query is using a temporary table from which I just can’t select any data afterwards. All I get is this error message: <Ens>ErrNoSQLColumns.
I reduced the statement for debugging to this query:
DECLARE @result AS TABLE(
CaseID varchar(50),
PatientID varchar(50),
AssessmentID varchar(50),
FormUsage varchar(50),
CollectedDT varchar(50),
Finding varchar(50),
FindingDataType varchar(50),
Value varchar(max))
INSERT INTO @result VALUES ('123', '234', '345', 'source', '2022-08-11 00:00:00', 'finding', 'type', 'value')
SELECT * FROM @result
Executing this directly via MSSQL Management Studio returns the expected result. I tested it with the same user that is used by the OutboundAdapter to rule out permissions as an issue. Different versions like “CREATE TABLE #result…” or writing field names instead of “*” in the final select deliver the same error.
As I said before I am running out of ideas, I would be grateful for hints and solutions.
Thanks,
Glenn