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

[michaelpatton]

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.

[VenuKadari]

Have you looked at advanced options of the library options?

Perhaps add PRESERVE_COL_NAMES=YES option.

[michaelpatton]

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

[VenuKadari]

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

[michaelpatton]

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.

[VenuKadari]

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

[VenuKadari · Accepted solution]

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

EnableQuotedIdenitifers=1

EnableQuotedIdentifiers=1

[ChrisNZ]

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.