cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
vince_faller
Super User (Alumni)

Importing SQL using a temp table

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.

Vince Faller - Predictum
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Importing SQL using a temp table

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

View solution in original post

14 REPLIES 14
pmroz
Super User

Re: Importing SQL using a temp table

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);

vince_faller
Super User (Alumni)

Re: Importing SQL using a temp table

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]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.

Vince Faller - Predictum
pmroz
Super User

Re: Importing SQL using a temp table

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?

vince_faller
Super User (Alumni)

Re: Importing SQL using a temp table

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

Vince Faller - Predictum
pmroz
Super User

Re: Importing SQL using a temp table

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

sfayer0
Level II

Re: Importing SQL using a temp table

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

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

vince_faller
Super User (Alumni)

Re: Importing SQL using a temp table

Thanks Brian that was really informative. 

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

Vince Faller - Predictum

Re: Importing SQL using a temp table

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