Architecting, installing and maintaining your SAS environment

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

Reply
New Contributor
Posts: 3

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

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!

SAS Super FREQ
Posts: 299

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

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

New Contributor
Posts: 3

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

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

New Contributor
Posts: 3

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

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

SAS Super FREQ
Posts: 299

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

Hi,

 

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

 

Thanks

Anja

Ask a Question
Discussion stats
  • 4 replies
  • 369 views
  • 0 likes
  • 2 in conversation