BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michaelpatton
Calcite | Level 5

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
VenuKadari
SAS Employee

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

 

EnableQuotedIdenitifers=1

EnableQuotedIdentifiers=1

 

View solution in original post

7 REPLIES 7
VenuKadari
SAS Employee

Have you looked at advanced options of the library options?

Perhaps add PRESERVE_COL_NAMES=YES option.

michaelpatton
Calcite | Level 5

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
SAS Employee

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

michaelpatton
Calcite | Level 5

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
SAS Employee

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
SAS Employee

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

 

EnableQuotedIdenitifers=1

EnableQuotedIdentifiers=1

 

ChrisNZ
Tourmaline | Level 20

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.

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2008 views
  • 0 likes
  • 3 in conversation