topic Duplicate LIMIT clauses in SAS/Access for Netezza v9.4 in Administration and Deployment

Duplicate LIMIT clauses in SAS/Access for Netezza v9.4

bzenk — Thu, 20 Oct 2016 19:41:11 GMT

Gurus,

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!

Re: Duplicate LIMIT clauses in SAS/Access for Netezza v9.4

anja — Fri, 21 Oct 2016 16:20:26 GMT

Hi,

did that work before and just now started throwing this error - after you migrated to SAS 9.4?

What EG version are you running?

Thanks

Anja

Re: Duplicate LIMIT clauses in SAS/Access for Netezza v9.4

bzenk — Fri, 21 Oct 2016 17:36:51 GMT

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

~Belinda

Re: Duplicate LIMIT clauses in SAS/Access for Netezza v9.4

bzenk — Fri, 21 Oct 2016 17:46:41 GMT

Anja,

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

Thanks, again!

~Belinda

Re: Duplicate LIMIT clauses in SAS/Access for Netezza v9.4

anja — Fri, 21 Oct 2016 18:04:59 GMT

Hi,

maybe its best to address this with Tech Sup to get it resolved as soon as possible?

Thanks

Anja