SQL query to ODBC datasource / SQL server with temporary tables

[#1]

Dear Community,

I am building a SQL script where utilizing temporary tables at SQL server. Simplified example script below (which works fine elsewhere) runs without error but does NOT return any data (ie. JMP data table does not pop up).

query= "
CREATE TABLE #tmpTable (ID int PRIMARY KEY NOT NULL, WaferNum int NOT NULL);
INSERT INTO #tmpTable (ID, WaferNum) (
SELECT
ID,
WaferNum
FROM WareHouse
);
SELECT * FROM #tmpTable;
DROP TABLE #tmpTable;
";

dbc = Create Database Connection(
"DSN=DatabaseName; ... ; UID=email@abc.com;"
);
Execute SQL (dbc, query);
close database connection(dbc);

My next iteration was to split the execution as below; this trick was mentioned on some thread.

query1= "
CREATE TABLE #tmpTable (ID int PRIMARY KEY NOT NULL, WaferNum int NOT NULL);
INSERT INTO #tmpTable (ID, WaferNum) (
SELECT
ID,
WaferNum
FROM WareHouse
);
";
query2= "
SELECT * FROM #tmpTable
DROP TABLE #tmpTable;
";
query3= "
DROP TABLE #tmpTable;
";
dbc = Create Database Connection(
"DSN=DatabaseName; ... ; UID=email@abc.com;"
);
Execute SQL (dbc, query1);
Execute SQL (dbc, query2, "Table");
Execute SQL (dbc, query3);
close database connection(dbc);

Now JMP log window captures messages from SQL server (without returned data): Invalid object name #tmpTable and Cannot drop the table #tmpTable as does not exist.

I am running JMP16. Any advices are highly appreciated.

[#2]

Hi Jannel,

I'm hoping it is not this old issue Importing SQL using a temp table. Hopefully someone else on the forum has used SQL Server Temp tables more recently with JMP.

Brian Corcoran

JMP Development

[#3]

Hi Brian, Thanks for pointing the old thread; indeed very related. Fascinating issue!

I did some debugging by looking the logs on test SQL Server I have established on my laptop. First note is that same query (with temporary tables or table variables) executed using very same DNS connection / ODBC driver in Excel works fine but not from JMP. When looking the incoming traffic to server, Excel sends the query string as it is (eg SELECT * FROM Table)  but from JMP, query arrives in format:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,NULL,N'SELECT * FROM Table'
select @p1

I believe that the SQL statement is wrapped with extra stuff by ODBC driver. Especially the system stored procedure sp_prepexec looks to be the one causing issues. May or may not be related to quotation marks; difficult to say what really happens inside that procedure.

Ok, it is ODBC driver issue. However, as far as know, ODBC enables also direct execution. I believe this is what happens with Excel. Could this same be possible also with JMP? Couple related links below:

https://techcommunity.microsoft.com/t5/datacat/watch-out-those-prepared-sql-statements/ba-p/305074

https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/direct-execution-odbc?view=sql-serve...

[#4 · Accepted solution]

Jane,

If you modify your first SQL to:

query= "SET NOCOUNT ON;
CREATE TABLE #tmpTable (ID int PRIMARY KEY NOT NULL, WaferNum int NOT NULL);
INSERT INTO #tmpTable (ID, WaferNum) (
SELECT
ID,
WaferNum
FROM WareHouse
);
SELECT * FROM #tmpTable;
DROP TABLE #tmpTable;
";

dbc = Create Database Connection(
"DSN=DatabaseName; ... ; UID=email@abc.com;"
);
Execute SQL (dbc, query);
close database connection(dbc);

You should get the table into JMP.

The addition, (in case it got lost in the JSL) is:

SET NOCOUNT ON;

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-ver15

-Bryan

[#5]

Hi Bryan,

Your solution SET NOCOUNT ON; works great!!! I am amazed how easy the solution was. I have spent quite some time by looking the solution from various forums and threads but no one has suggested this one.

Great thanks;

Janne

[#6]

SET NOCOUNT ON is an odd option indeed.
Glad it worked out for you!