topic Re: SQL query to ODBC datasource / SQL server with temporary tables in Discussions

SQL query to ODBC datasource / SQL server with temporary tables

JanneI — Sat, 10 Jun 2023 23:43:36 GMT

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.

Re: SQL query to ODBC datasource / SQL server with temporary tables

briancorcoran — Wed, 02 Feb 2022 12:29:33 GMT

Hi Jannel,

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

Brian Corcoran

JMP Development

Re: SQL query to ODBC datasource / SQL server with temporary tables

JanneI — Thu, 03 Feb 2022 12:33:15 GMT

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-server-ver15

Re: SQL query to ODBC datasource / SQL server with temporary tables

bryan_boone — Thu, 03 Feb 2022 15:50:39 GMT

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

Re: SQL query to ODBC datasource / SQL server with temporary tables

JanneI — Thu, 03 Feb 2022 19:45:44 GMT

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

Re: SQL query to ODBC datasource / SQL server with temporary tables

bryan_boone — Thu, 03 Feb 2022 19:52:37 GMT

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