I'm trying to get the results of a MS SQL query into a manifold table using EXECUTE. This normally works without issue except in this case, the SQL uses a temp table. I understand I need to modify it to use RESULT SETS otherwise I get a SQLSTATE: 42000 error. So, I can get the query to run in Manifold without error, but no results are returned.
Here's a simple example using a temp table where results are returned when running the SQL, within the EXECUTE block, in SSMS but not in Manifold.
SELECT * FROM (
DECLARE @sqlCommand VARCHAR(max);
SET QUOTED_IDENTIFIER OFF;
SET @sqlCommand = "
CREATE TABLE #myTempTable (
INSERT INTO #myTempTable VALUES
SELECT * FROM #myTempTable
SET QUOTED_IDENTIFIER ON;
WITH result sets((
]] on [my MS SQL Database]);