topic Re: Importing SQL using a temp table in Discussions

Importing SQL using a temp table

vince_faller — Thu, 02 Apr 2015 18:58:03 GMT

I am having a difficult time using Stored Procedures and Temp tables in JMP 11.2.1. I simply get no return

db = Create database Connection("DRIVER=SQL Server;

SERVER=localserver\SQLEXPRESS;

DATABASE=Test"

);

Execute SQL(db,

CREATE TABLE #Test ( ID int)

Insert INTO #Test (ID)

VALUES (1), (2), (3), (4), (5)

SELECT *

FROM #Test

DROP TABLE #Test

":-("

);

close database connection(db);

This problem has plagued me for some time. From the looks of it SAS can do it so I don't think it's an issue with ODBC.

Re: Importing SQL using a temp table

pmroz — Fri, 03 Apr 2015 18:22:45 GMT

Try separating out each SQL statement:

db = Create database Connection("DRIVER=SQL Server;

SERVER=localserver\SQLEXPRESS;

DATABASE=Test"

);

sql1 = "CREATE TABLE #Test ( ID int)";

Execute SQL(db, sql1);

sql2 = "Insert INTO #Test (ID) VALUES (1), (2), (3), (4), (5)";

Execute SQL(db, sql2);

sql3 = "SELECT * FROM #Test";

dt = Execute SQL(db, sql3, "#Test");

sql4 = "DROP TABLE #Test";

Execute SQL(db, sql4);

close database connection(db);

Re: Importing SQL using a temp table

vince_faller — Fri, 03 Apr 2015 18:45:05 GMT

It looks like it gets rid of it as soon as the Execute is finished.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#Test'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table '#Test', because it does not exist or you do not have permission.

Re: Importing SQL using a temp table

pmroz — Fri, 03 Apr 2015 20:23:22 GMT

I'm not a SQL server expert - I mostly use Oracle. It appears that SQL server doesn't like the name of your table. Try Test instead of #Test.

Have you validated the SQL commands in a SQL Server console?

Re: Importing SQL using a temp table

vince_faller — Mon, 06 Apr 2015 16:47:59 GMT

Without # wouldn't be a temp table.
It works correctly in SQL SERVER.

Re: Importing SQL using a temp table

pmroz — Mon, 06 Apr 2015 18:12:58 GMT

I'll have to defer to the SQL Server experts on this one then.

Re: Importing SQL using a temp table

briancorcoran — Tue, 07 Apr 2015 14:20:15 GMT

Hi Vince,

I'm sorry about the delayed reply, but I wanted to do some investigation on this. Unfortunately this seems to be an issue with the Microsoft SQL Server ODBC drivers (both Native Client and Office version). The table creation is failing silently when we submit the SQL. With a driver like Actual Technologies SQL Server driver for the Mac, this succeeds just fine. I know in older versions of JMP this would work, because at the time we were not enforcing ODBC 3.0. This level is required to utilize block fetching within SQL Server and other database vendors. Block fetching makes retrieval of large tables much, much faster. However, with this comes the failing of TEMP tables. This initialization is done at startup time for JMP, so it is really not possible to selectively disable it for TEMP tables, even if we could parse the SQL to detect the TEMP table usage.

I wish I could give you better news.

Brian Corcoran

JMP Development

Re: Importing SQL using a temp table

vince_faller — Tue, 07 Apr 2015 14:35:37 GMT

Thanks Brian that was really informative.

Is it the same issue that possibly causes stored procedures to fail as well?

Re: Importing SQL using a temp table

briancorcoran — Tue, 07 Apr 2015 15:05:25 GMT

Hi Vince,

I am not aware of a problem with stored procedures. I was able to get one working here, although we don't have a lot of good examples on SQL Server. I know quoting is a big issue. What are you seeing?

Brian

Re: Importing SQL using a temp table

vince_faller — Thu, 09 Apr 2015 15:37:06 GMT

Thanks Brian, I must have just been calling it wrong.

Re: Importing SQL using a temp table

Eric_Hill — Thu, 09 Apr 2015 20:17:55 GMT

It might not be your fault. There is a bug in JMP 12 causing stored procedures not to return any rows to JMP (I'm frankly not sure how Brian got one to work). It will be fixed in the 12.1 maintenance release, which should be out in a few months.

UPDATE: I was confused. The bug regarding stored procedures not returning any rows was actually *not* in JMP 12 - it was introduced and fixed within the JMP 12.1 cycle. So you may need to look at your stored procedure code. We have seen scenarios where a table is created and then selected from within a single stored procedure where the rows do not get returned to JMP.

Eric

Re: Importing SQL using a temp table

sfayer0 — Wed, 06 Jul 2016 21:12:22 GMT

Thanks for the explanation. However, I don't like how this works. I should simply be able to take any query that runs fine in other applications, and see/expect the same result when ran within JMP/JSL.

I've read though this thread and one thing is clear... the workaround discussed, splitting out all temp tables into separate queries, is NOT a good solution for us JSL Developers. I have a large, older query that works just fine and it will literally take me hours to split it out. What's the point of that? How is this the preferred method? I also noticed that in my first query split attempt, JMP isn't loading OIDs into the new Data Table. So I'm not really sure if I can split out this query. If I continue, I can't join on a blank OID column.... When I run this identical query portion through SQL Server, my column does have data.

I was also surprised that this topic was difficult to find -it took a few Google searches. Limitations should be clearly stated in the JSL Manual, but this is the first I'm reading it. I specifically wrote several helper functions -which now aren't very helpful. Now I'm realizing it was a waste of time. It will actually be faster for me to use another Application to write to csv, and have JMP load that. This software is not cheap, so I'd expect Temp Tables/SQL to work in JMP instead of needing an external app to save me time.

At the very least, I'd expect this application to have better notifications. If JMP detects temp table usage either within JSL or the "Open Table > Advanced" Platform, maybe a message should be sent to the developer to inform them that JMP cannot handle the operation...?

This thread doesn't appear active anymore... Does SAS/JMP plan on fixing this?

Re: Importing SQL using a temp table

sfayer0 — Wed, 06 Jul 2016 21:15:20 GMT

I've noticed that saving the results from each query portion as literal JMP Data Tables, and then Joining them manually through within JSL seems to work some of the time. It can really eat up your time though, but you'll be able to keep the results active until you run a Close Data Table.

Re: Importing SQL using a temp table

vince_faller — Wed, 06 Jul 2016 21:23:34 GMT

The way I understood it, this isn't on the JMP side to fix. The problem is on Microsoft's side in they way their drivers for ODBC 3.0 call it. They could possibly try to detect #Tables as you said, but I don't think I'd want to interrupt a script to tell me that my SQL is written in an unacceptable fashion. Passing something to the log I could see being useful though.

Re: Importing SQL using a temp table

sfayer0 — Wed, 06 Jul 2016 21:52:47 GMT

Vince,

Thanks. Would it make sense to look for older ODBC 2.0 downloads? I haven't had much luck yet.