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
Why not use a view instead of a Temporary table? Since Temporary Tables are just Temporary and created at the time of execution how are you setting privs on the Temporary Table.
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.
I don't like using temp tables, I would just write the data to another table then truncate/drop the table when I am done if necessary. The syntax between the different SQL system can be a little tricky and my guess there is an incompatibility between MS SQL and InterSystems.