10-20-2016 03:41 PM
We are migrating our SAS server and upgrading from 9.3 to 9.4. We use SAS/Access for Netezza and when we create LIBNAMES for our Netezza data and try to drag and drop and open a Netezza dataset we get an error on the SQL run in Netezza. The SQL has TWO LIMIT clauses.
SAS Enterprise Guide cannot open the data file: "Libref.tablename"
[Error} CLI open cursor error: ERROR: 'SELECT * FROM DB.schema.tablename LIMIT 500 limit 500' error
^ found "LIMIT" (at char44) expecting a keyword
We do ask our users to set the Maximum number of rows to display in the data grid to 500. (Tools -> Options -> Data -> Performance).
When this option is changed (to 100), both LIMIT clauses change. When we change it to 0, we don't get the error -- but most of our netezza tables are much too large to NOT have a limit clause! We ask our users not to open the data sets, but... users will be users!
Current libname statement:
libname netezza server=&nzSERVER. database=&nzDATABASE. schema=&nzSCHEMA. user=&msUSERID. password=&msPASSWORD. connection=globalread defer=yes;
Is there another setting? Something I can add to the libname?
Query Builder works fine.
Screen prints of settings and errors are attached
Any ideas are GREATLY appreciated!
10-21-2016 01:36 PM
Thanks for the reply Anja!
Yes. It worked in 9.3. It started failing when we migrated to 9.4.
We also upgraded our users from EG v5.1. They are now on 6.1 or 7.1 -- it is failing on both. (Probably should have mentioned that!)
10-21-2016 01:46 PM
FYI: I just opened my old v5.1 EG and connected to the new v9.4 SAS server. When I opened a table, It failed with the duplicate LIMIT clause...