topic SAS DI studio: Issue inserting into SQLServer table where column names contain spaces in SAS Programming

SAS DI studio: Issue inserting into SQLServer table where column names contain spaces

michaelpatton — Tue, 09 Oct 2018 08:39:51 GMT

I have a relatively simple SAS DI studio job that reads from an Oracle view and writes to a SQLServer table.

The SQLServer table I'm inserting into has spaces in the column names.

The table in in a 3rd party database so I cannot rename the columns.

This is the error that I get.

Line 255: ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'ID'. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'Date'. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Statement(s) could not be prepared.

This is the statement that is failing. It doesn't like the "Personnel ID"n column name (and others with spaces)

insert into scmms."tmp_Personnel"n

("Import_PersonnelID"n, "ImportError"n, "Name"n, "Personnel ID"n,

"Display Name"n, "Honorary"n, "Initials"n, "Reference"n, "Job Title"n,

"Extension"n, "Telephone"n, "Mobile"n, "Home Phone"n, "Email Address"n,

"Fax"n, "Pager"n, "Geography Selection"n, "Site"n, "Location"n, "Unit"n,

"Partition"n, "Start Date"n, "End Date"n, "Division"n, "Department"n,

"Grade"n, "Authorisation Level"n, "Allocation"n, "Cost Code"n,

"Registration"n, "Export Value"n, "Post Box"n, "Show Message"n,

"Messages"n, "Remarks"n, "Event PO Authoriser"n, "Fire Warden"n,

"First Aider"n, "Has Secretary"n, "Stock PO Authoriser"n)

select

"Import_PersonnelID"n, "ImportError"n, "Name"n, "Personnel ID"n,

"Display Name"n, "Honorary"n, "Initials"n, "Reference"n, "Job Title"n,

"Extension"n, "Telephone"n, "Mobile"n, "Home Phone"n, "Email Address"n,

"Fax"n, "Pager"n, "Geography Selection"n, "Site"n, "Location"n, "Unit"n,

"Partition"n, "Start Date"n, "End Date"n, "Division"n, "Department"n,

"Grade"n, "Authorisation Level"n, "Allocation"n, "Cost Code"n,

"Registration"n, "Export Value"n, "Post Box"n, "Show Message"n,

"Messages"n, "Remarks"n, "Event PO Authoriser"n, "Fire Warden"n,

"First Aider"n, "Has Secretary"n, "Stock PO Authoriser"n

from &etls_lastTable

;

If I take out the column names with spaces, I do not get the error. But I need to provide data in these columns.

In the table definition, I have the "Enable special characters within table or column object names" selected. See attached image file for more details regarding table definition.

Environment details

SAS 9.4 on a Linux server

DI studio 4.901 installed on Windows 10.

As a test, I copied the job back into our old environment.

SAS 9.3 Windows server

DI studio 4.6 installed on Windows 7

and everything works fine.

Is there some server configuration that we need to adjust?

Patches?

Let me know if there is anything else I can provide that would help.

Re: SAS DI studio: Issue inserting into SQLServer table where column names contain spaces

VenuKadari — Tue, 09 Oct 2018 15:27:25 GMT

Have you looked at advanced options of the library options?

Perhaps add PRESERVE_COL_NAMES=YES option.

Re: SAS DI studio: Issue inserting into SQLServer table where column names contain spaces

michaelpatton — Wed, 10 Oct 2018 00:32:34 GMT

VenuKadari,

I tried that but it did not work.

LIBNAME scmms ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES

I also tried proc sql dquote=ansi;

in the insert statement.

No luck with either, I still get the same error message

Re: SAS DI studio: Issue inserting into SQLServer table where column names contain spaces

VenuKadari — Wed, 10 Oct 2018 13:41:54 GMT

Can you make sure the SQL Server ODBC DSN configuration has Enable Quoted Identifiers option is enabled?

Re: SAS DI studio: Issue inserting into SQLServer table where column names contain spaces

michaelpatton — Thu, 01 Nov 2018 01:22:58 GMT

Apologies for the delay.

It took a while for the systems team to make the requested change to the odbc.ini file

The following line has been added but I still get the same behavior.

EnableQuotedIdenitifers=1

Another question, where do I have to add this?

Is it connection specific?

Sample odbc.ini file attached.

Re: SAS DI studio: Issue inserting into SQLServer table where column names contain spaces

VenuKadari — Mon, 05 Nov 2018 16:38:59 GMT

A colleague tried this out. This syntax worked. Please make sure that SQL server database database is created with ANSI enabled.

52 options validvarname=any;
53 libname ora oracle uid=USER pwd=XXXXXX path=mypath preserve_names=yes;
NOTE: Libref ORA was successfully assigned as follows:
Engine: ORACLE
Physical Name: mypath
54 libname sql odbc uid=USER pwd=XXXXXX dsn=dsnname preserve_names=yes;
NOTE: Libref SQL was successfully assigned as follows:
Engine: ODBC
Physical Name: dsnname

55 proc sql dquote=ansi;
56 insert into sql.blank_test("ID NUM"n, "SALARY"n, "BEG DATE"n, "END DATE"n, "JOB CODE"n)
57 select IDNUM, SALARY, BEGDATE, ENDDATE, JOBCODE from ora.SALARY;
NOTE: 319 rows were inserted into SQL."blank_test".

58 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.41 seconds
cpu time 0.01 seconds

59 proc sql;
60 select count(*) from sql.blank_test;
61 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.08 seconds
cpu time 0.04 seconds

Re: SAS DI studio: Issue inserting into SQLServer table where column names contain spaces

VenuKadari — Mon, 05 Nov 2018 22:40:34 GMT

Noticed you have a typo in your odbc.ini file. Hopefully, this will fix your issue.

~~EnableQuotedIdenitifers=1~~

EnableQuotedIdentifiers=1

Re: SAS DI studio: Issue inserting into SQLServer table where column names contain spaces

ChrisNZ — Mon, 05 Nov 2018 23:23:19 GMT

As long as you have the right number of columns in the right order, you don't need to specify the column names after INSERT INTO.

Same thing for the SELECT clause actually.