New post
Question
· Aug 11, 2022

EnsLib.SQL.OutboundAdapter cannot select from temporary table

#SQL #InterSystems IRIS for Health

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

Product version: IRIS 2021.2
$ZV: 2021.2 (Build 651U) [Health:3.4.0]
Discussion (3)0
Log in or sign up to continue
Glenn Bickel · Aug 12, 2022

In my case, the query is generated to export data for one report into another system. It won’t repeat and the next query will look totally different. Creating a view won’t help me here. Stored procedures and functions won’t do either.

 

I will share a little more detail for my use case.

My query runs on a huge database with many patients. First, I am declaring and filling a table with patient id and case id which will be used later to restrict data to relevant cases. Just like in the final select, I can’t retrieve any data from this table either.

In the next step, data is selected from different tables and stored in the @results table as mentioned in my first post. Here I am joining the patient id table since the patient id and case id are the only keys working on (almost) all tables.

Finally, everything from @result is selected and handled by the IRIS.

Everything works fine unless I am using a temporary table in my query. I used a simple version for debugging avoiding the temp table, which worked, but this won’t do for the final version.

0 0