Subscribe to this thread
Home - General / All posts - M9 Problem with MS SQL temp tables
dj2k24 post(s)
#21-Nov-22 12:55

Hi,

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.

-- $manifold$

SELECT * FROM (

EXECUTE[[

DECLARE @sqlCommand VARCHAR(max);

SET QUOTED_IDENTIFIER OFF;

SET @sqlCommand = "

CREATE TABLE #myTempTable (

ID int,

myValue varchar(50)

);

INSERT INTO #myTempTable VALUES

(1,'row1'),

(2,'row2'),

(3,'row3'),

(4,'row4');

SELECT * FROM #myTempTable

";

SET QUOTED_IDENTIFIER ON;

EXEC (@sqlCommand)

WITH result sets((

id INT

,myValue VARCHAR(max)

));

]] on [my MS SQL Database]);

adamw


10,239 post(s)
#23-Nov-22 09:24

We'll check what's going on, however, a quick question: do things change if you use a global temp table -- ##myTempTable? (I agree a temp table local to a connection makes the most sense in the context of a command, the above is simply a quick test.)

dj2k24 post(s)
#23-Nov-22 11:21

I get the same result (or lack of) when I make it a global temp table.

adamw


10,239 post(s)
#23-Nov-22 15:22

We found this. The culprit seems to be WITH RESULT SETS, not the use of a temporary table. We will have the fix (or maybe an adjustment, SQL Server behaves a little strange here) in the next cutting edge build.

Thanks for the note and for a clear example.

dj2k24 post(s)
#25-Nov-22 08:54

Thanks Adam.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.