topic Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete in SAS Programming

libname schema reference (username) has dashes (special characters) and causes CLI error on Netezza

jmspy — Tue, 16 Jun 2020 18:04:46 GMT

Hello-

I have a username that it similar to this: "MY-USER-NAME" and this is causing me issues when it comes to using it as a schema in my libname. I have tried creating a secondary variable that upcases and double quotes the username and passing that in, which allows me to assign the libname just fine, but once I attempt to use it in a data step i receive the below error.

User name secondary variable:

%let nUser = "%upcase(&user.)";

Then I successfully assign the libname:

libname nzp netezza user=&nUser. password="&nzpass" server="server.co.net" database="DBC" schema=&nUser. connection=shared;

NOTE: Libref nzp was successfully assigned as follows:
      Engine:        NETEZZA

However, attempting to create a dataset on Netezza with a data step causes this:

	data nzp.mytable (bulkload=yes);
		set work.have;
	run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR:  'USER-NAME.MYTABLE' error   ^ found "-" (at
       char 0) expecting `AS'.
NOTE: The DATA step has been abnormally terminated.

The issue appears to be that when the libname references the schema inside the data step, it is unquoted, thereby causing this error. Also to note, there are tables in Netezza currently that have this username as their schema, they simply show double quotes around the schema name.

Thank you in advance for any assistance!

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

smantha — Tue, 16 Jun 2020 18:16:58 GMT

can you try putting %quote or %str as following

libname nzp netezza user=%quote(&nUser.) password="&nzpass" server="server.co.net" database="DBC" schema=%quote(&nUser.) connection=shared;

libname nzp netezza user=%str(&nUser.) password="&nzpass" server="server.co.net" database="DBC" schema=%str(&nUser. ) connection=shared;

Let me know what works.

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

Tom — Tue, 16 Jun 2020 18:31:58 GMT

Try putting the quotes into the value you tell the LIBNAME statement to use for the SCHEMA.

libname nzp netezza  ... schema="""USER-NAME""" ... ;

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

jmspy — Tue, 16 Jun 2020 18:35:16 GMT

I tried both and am still getting the same error

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

jmspy — Tue, 16 Jun 2020 18:41:13 GMT

This one gave me a new error:
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: zero-length delimited identifier.
NOTE: The DATA step has been abnormally terminated.

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

Tom — Tue, 16 Jun 2020 18:45:14 GMT

@jmspy wrote:
This one gave me a new error:
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: zero-length delimited identifier.
NOTE: The DATA step has been abnormally terminated.

And what remote SQL code did SAS actually generate? Make sure to turn on the logging so you can see the generated code that SAS sends.

You should open a ticket with SAS support and get them to help you debug this.

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

jmspy — Tue, 16 Jun 2020 19:47:31 GMT

I'm actually not sure how to do that, i use the normal log and/or project log. Is there a different log you are referring to?

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

smantha — Tue, 16 Jun 2020 19:53:42 GMT

Netezza by default writes log to some place on your local machine. You need to find where it is located.

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

Tom — Tue, 16 Jun 2020 20:04:41 GMT

There are many SAS options that control how much information is written to the SAS log. Here are some example settings I have used in the past.

options msglevel=i nostsuffix sastrace=',,,ds' sastraceloc=saslog sql_ip_trace=source;

Also try checking if you can access that schema using explicit pass thru code. For example if there is a table named MYTABLE in the USER-NAME schema can you run this query to count how many observations it has?

proc sql;
connect using nzp;
select * from connection to nzp
  (select count(*) as nobs from user-name.mytable)
;
quit;

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

r_behata — Tue, 16 Jun 2020 20:15:11 GMT

This SAS Note provides some insights into this issue :

https://support.sas.com/kb/35/003.html

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

jmspy — Tue, 16 Jun 2020 21:15:24 GMT

Here's the output with those additional log options:

NETEZZA: AUTOCOMMIT is NO for connection 1

NETEZZA_1: Executed: on connection 1
SELECT * FROM DBC.MY-USER-NAME.MYTABLE WHERE 0=1


Summary Statistics for NETEZZA are:
Total SQL execution seconds were:                   0.000619
Total seconds used by the NETEZZA ACCESS engine were     0.000782


NETEZZA_2: Prepared: on connection 0
SELECT * FROM DBC.MY-USER-NAME.MYTABLE
^L220                                                        The SAS System                               17:09 Tuesday, June 16, 2020



Summary Statistics for NETEZZA are:
Total SQL prepare seconds were:                     0.000824
Total seconds used by the NETEZZA ACCESS engine were     0.001073

620      !  proc append base=usb.MYTABLE (bulkload=yes) data=work.have;
ERROR: CLI prepare error: ERROR:  'USER-NAME.MYTABLE error   ^ found "-" (at char 0) expecting a keyword

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

SASKiwi — Wed, 17 Jun 2020 00:33:30 GMT

@jmspy - Looks similar to this SAS note: https://support.sas.com/kb/58/291.html

I suspect SAS Tech Support is your best bet.

Re: libname schema reference (username) has dashes (special characters) and causes CLI error on Nete

jmspy — Mon, 22 Jun 2020 14:43:00 GMT

A member of my company was able to help fix the issue for me

schema='"MY-USER-NAME"'

I've only gotten it to work by hard coding the username and surrounding the double-quoted username with single quotes.