SQL query will not pull data on a second run

Question

I have a JSL script that will sucessfully pull data on the first run after I open JMP, but if I try to run the script again with new parameters in the SQL query for a new set of data, it will act like it is connecting and pulling data, but I get no table in JMP.  There are no errors in my log.  Sometimes if I change the name of the output table ("MyTable") and what I called the database connection ("ftdb"), it will work again, but it seems odd.  I have Delete Symbols (); at the top to clear everything?  Is there something else that I need to clear?  If I close JMP and re-open it works fine again, but that seems sub-optimal.....

ftdb = Create Database Connection ("DSN=zzzzz;UID=yyyy;PWD=xxxx");

dtmoves = Execute SQL (ftdb, sql, "MyTable");

Close Database Connection (ftdb);

Aleeta · Answer

1. JMP 12

2. MS SQL

I actually found that there is a setting to turn off the ODBC string as a table variable in Preferences > Tables and that seems to have helped this problem. I have a second problem now (which I think was part of why the first one seemed unstable) and that is timing on the query itself, I am having to run the script twice to get the data, it seems to time out the first time, and the second time it will retrieve the data (with or without a Wait of any length) just fine and continue. I am not sure if this is a JSL issue or a SQL issue, currently checking both.

Eric_Hill · Answer

Hey, Aleeta,

This might be one to work through with JMP Technical Support (support@sas.com).  But two questions:

1. What version of JMP is this?

2. Who is the database vendor? (SQL Server, MySQL, Oracle, etc.)

Thanks,

Eric