ODBC file on unix

[Riana]

Hi,

Hopefully I'm in the right place to ask this, it is more admin than programing. I've a connection in odbc.ini file for a SQL server. SQL tables have name with special characters like $, -,... When I try to connect to the tables I'm getting error:

SAS/ACCESS][ODBC SQL Server Legacy Driver][SQL Server]Incorrect syntax near '-'.

Which option should be added to the connection related to this sql server in odbc file so that be able to connect to these tables in SAS?

[alexal]

@Riana,

The problem occurs because the ODBC driver by default does not use quoting for table names. To resolve the problem, do the following:

Enable EnableQuotedIdentifers in the ODBC driver entry. If you are on a UNIX system, you can add EnableQuotedIdenitifers=1 to the odbc.ini file. If you are on a Windows system, select Enable Quoted Identifiers on the Advanced tab of your ODBC driver entry as shown below.

[Riana]

Thank you @alexal, I'm on a unix system. I added the option but I still getting the same error message.

Is there anything more that should be added or should be changed in odbc.ini file?

[alexal]

@Riana,

Please add these options at the top of your SAS program, run it again and show me full SAS log:

options fullstimer source source2 msglevel=i mprint notes; 
options sastrace=",,,dsa" sastraceloc=saslog nostsuffix;

[Riana]

1          %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
5          %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
29         
30         options fullstimer source source2 msglevel=i mprint notes;
31         options sastrace=",,,dsa" sastraceloc=saslog nostsuffix;
32        
33         libname ab1 sqlsvr dsn="ABCD" user=user1 password=XXXXXXXX schema=dbo ;
ERROR: CLI error trying to establish connection: [SAS/ACCESS][ODBC SQL Server Legacy Driver][libssclient27]General network error. 
       Check your network documentation. : [SAS/ACCESS][ODBC SQL Server Legacy Driver][libssclient27]ConnectionOpen (()).
ERROR: Error in the LIBNAME statement.
34         
35         
36         %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
49         
50         
51         %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
54         

and in odbc:

[ABCD]
Driver=*****
Description=SAS Institute Inc. 7.0 SQL Server Legacy Wire Protocol
AlternateServers=
AlwaysReportTriggerResults=0
AnsiNPW=1
ApplicationName=
ApplicationUsingThreads=1
AuthenticationMethod=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadFieldDelimiter=
BulkLoadOptions=2
BulkLoadRecordDelimiter=
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=
EnableBulkLoad=0
EnableQuotedIdentifiers=1
EncryptionMethod=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
GSSClient=native
HostName=**********
HostNameInCertificate=
InitializationString=
Language=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
PacketSize=-1
Password=
Pooling=0
PortNumber=1433
QuoteId=yes
QueryTimeout=0
ReportCodePageConversionErrors=0
SnapshotSerializable=0
TrustStore=
TrustStorePassword=
ValidateServerCertificate=1
WorkStationID=
XMLDescribeType=-10

[alexal]

@Riana,

This is a different error, please remove EnableQuotedIdentifiers=1 and re-run your SAS program with the same options.

[Riana]

Removed the line and here is the result:

1          %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
5          %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
29         
30         
31         
32         options fullstimer source source2 msglevel=i mprint notes;
33         options sastrace=",,,dsa" sastraceloc=saslog nostsuffix;
SQLSRV: COMMIT performed on connection 0.
SQLSRV: AUTOCOMMIT is NO for connection 0

35         libname ab1 sqlsvr dsn="ABCD" user=user1 password=XXXXXXXX schema=dbo ;
NOTE: Libref ab1 was successfully assigned as follows: 
      Engine:        SQLSVR 
      Physical Name: ABCD
36         
         
SQLSRV: AUTOCOMMIT is NO for connection 1
SQLSRV: AUTOCOMMIT turned ON for connection id 1
 
SQLSRV_1: Prepared: on connection 1
SELECT * FROM  dbo . abcd$H-Name Type 
 
SQLSRV: ROLLBACK performed on connection 1.
 
Summary Statistics for ODBC are:
Total SQL prepare seconds were:                     0.000216
Total SQL describe seconds were:                    0.037952
Total seconds used by the ODBC ACCESS engine were     0.054703
 
42         data a1;
43         set ab1."abcd$H-Name Type"n;
ERROR: CLI describe error: [SAS/ACCESS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near '-'.
44         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.A1 may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.A1 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.25 seconds
      user cpu time       0.03 seconds
      system cpu time     0.00 seconds
      memory                  371.65k
      OS Memory           17568.00k
      Timestamp           09/15/2017 09:25:44 AM
      Step Count                        5  Switch Count  0
2                                                          The SAS System                           08:56 Friday, September 15, 2017

      Page Faults                       0
      Page Reclaims                     97
      Page Swaps                        0
      Voluntary Context Switches        13
      Involuntary Context Switches      5
      Block Input Operations            0
      Block Output Operations           8
      

45         
46         %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
59         
60         
61         %_eg_hidenotesandsource;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nonotes;
MPRINT(_EG_HIDENOTESANDSOURCE):   options nosource;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options NOTES;
MPRINT(_EG_RESTORENOTESANDSOURCE):   options SOURCE;
64     

[Kurt_Bremser]

Oh, my. Somebody really created a table named like that:

"abcd$H-Name Type"n

?

That somehow reminded me of https://xkcd.com/327/.

If someone tried similar in our production DB, we'd probably hear the screams caused by the ministrations of the DB administrator for days ....

[Riana]

 

It is more funny if I say that in this library there are about  200 tables with name similar to this  🙂

I'm working  as support and try to help a user who work with these tables.

It is strange, I changed the odbc file in test environment(we have test and prod), added EnableQuotedIdentifiers=1. No error and I could open the tables(with special characters in their name) but the same has been done in prod and getting error!

[cpagrawal]

I think it should be set in the SQL Server itself. I have experience with a weired situation where the issue related to table name cases, it was not case insensitive and we resolved with setting collation. 
What I can remember as well there are some options in the SAS Management Console as well, we need to  check. 

[Riana]

Thank you @cpagrawal  it works fine in test with the added line though any thing has been changed in SAS MC.

Can you please guide me more that what could be done in SAS MC or which options need to be changed there? 

[alexal]

@Riana,

Are you sure that you using the same ODBC drivers on test and prod environments?

[Riana]

Here are connection for both test and prod:

Test(works fine):

[ABCD]
Driver=*****
Description=SAS Institute Inc. 7.0 SQL Server Legacy Wire Protocol
AnsiNPW=1
AuthenticationMethod=1
EnableQuotedIdentifiers=1
Database=
EnableQuotedIdentifiers=0
HostName=******
PortNumber=1433

In prod it has been changed to be similar to test, but still error:

SAS/ACCESS][ODBC SQL Server Legacy Driver][SQL Server]Incorrect syntax near '-'.

Prod:

[ABCD]
Driver=*****
Description=SAS Institute Inc. 7.0 SQL Server Legacy Wire Protocol
Address=*****,1433
AlternateServers=
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=
EnableQuotedIdentifiers=1
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
LoadBalancing=0
QuotedId=No
ReportCodepageConversionErrors=0
SnapshotSerializable=0

[alexal]

@Riana,

There is some difference, let's compare this file from both servers:

/<SASHome>/SASFoundation/9.4/bin/sasenv_local

[Riana]

I checked the files and it seems that there are some differences.

What difference(s) should I search for that is(or might be) related to this issue?