SQL Server ODBC DSN Connection Error: CLI error trying to establish ...

[Paul_OldenKamp_org]

Hello,

I am trying to use the SAS/ACCESS Interface to PC Files included in University Edition to read data from SQL Server db's into my University Edition session.  Here's a section of the log with the error message I keep getting:

 56         libname mysqlsvr pcfiles

 57                 server='<Computer name>'

 58                 dsn=<User DSN Name>

 59                 user=<SQL Server user Id>

 60                 pwd=XXXXXXXX

 61                 schema=dbo

 62                 ;

 ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

 ERROR: Error in the LIBNAME statement.

The information on this error refers one to check the bit level of one's Office applications and the version of the SAS PC Files Server and I have checked that these are both 32 bit.  The pcfiles engine works for reading Excel workbooks and MS Access database tables:

 56         libname test

 57                 pcfiles

 58                 path="C:\SASUniversityEdition\myfolders\Access\Northwind\Northwind.accdb"

 59                 server='<Computer name>'

 60                 ;

 NOTE: Libref TEST was successfully assigned as follows:

       Engine:        PCFILES

       Physical Name: C:\SASUniversityEdition\myfolders\Access\Northwind\Northwind.accdb

 61        

 62         data customers;

 63            set test.customers;

 64         run;

 NOTE: There were 29 observations read from the data set TEST.customers.

 NOTE: The data set WORK.CUSTOMERS has 29 observations and 18 variables.

 NOTE: DATA statement used (Total process time):

       real time           0.11 seconds

       cpu time            0.03 seconds

Thanks for your assistance,

Paul

[SASKiwi]

I don't think SAS/ACCESS to PC Files will work with SQL Server. You need SAS/ACCESS to ODBC with the correct SQL Server ODBC driver installed on the same server. I'm not sure if that's available in the University Edition or not. Run proc setinit to check if it is or not.

[Paul_OldenKamp_org]

Hi,



I'm trying to do what is shown in Example 3 on this page of the Interface to PC Files: Reference,

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n1qk7lv0f535qkn1my...



Paul

[DaniChiavegatto]

@Paul_OldenKamp_org, 

Unfortunately SAS/ACCESS to ODBC is not included in University Edition, so you will not be able to connect to a SQL server. If you require SAS/ACCESS to ODBC, you would need to purchase a SAS license.

[Paul_OldenKamp_org]

So, does one need both SAS/ACCESS Interface to PC Files and SAS/ACCESS to ODBC to use the functionality described in the SAS/ACCESS Interface to PC Files: Reference?  Where is this documented?

[Paul_OldenKamp_org]

I’m still working on this because I don’t think that the lack of SAS/ACCESS Interface to ODBC product in University Edition is relevant to this error.  I added the sastrace and sastraceloc system options and have included the resulting log below.  One can see that the pcfiles engine is working up to a point and it is communicating through the SAS PC Files Server to the ODBC Manager on the PC.  For some reason the value of the DSN= option is not matching up to the User DSN Name in the ODBC Manager.  

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

 55        

 56         options

 57            sastrace='d,,,d'

 58            sastraceloc=saslog

 59            ;

 60         run;

 61        

 62         libname mysqlsvr pcfiles

 63                 server='<Computer name>'

 PCFILES: Successfully connected to the PC Files Server as user '<Computer name>\SYSTEM'. 121 1456518129 no_name 0 OBJECT_E

 PCFILES: ENTER SQLAllocHandle 122 1456518129 no_name 0 OBJECT_E

                               1 <SQL_HANDLE_ENV> 123 1456518129 no_name 0 OBJECT_E

              0x0000000000000000 124 1456518129 no_name 0 OBJECT_E

              0x000000009a66a630 125 1456518129 no_name 0 OBJECT_E

 PCFILES: EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS) 126 1456518129 no_name 0 OBJECT_E

                               1 <SQL_HANDLE_ENV> 127 1456518129 no_name 0 OBJECT_E

              0x0000000000000000 128 1456518129 no_name 0 OBJECT_E

              0x000000009a66a630 (0x0000000071e383e0) 129 1456518129 no_name 0 OBJECT_E

 PCFILES: ENTER SQLSetEnvAttr 130 1456518129 no_name 0 OBJECT_E

              0x0000000071e383e0 131 1456518129 no_name 0 OBJECT_E

                             200 <SQL_ATTR_ODBC_VERSION> 132 1456518129 no_name 0 OBJECT_E

              0x0000000000000003 133 1456518129 no_name 0 OBJECT_E

                              -6 134 1456518129 no_name 0 OBJECT_E

 PCFILES: EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS) 135 1456518129 no_name 0 OBJECT_E

              0x0000000071e383e0 136 1456518129 no_name 0 OBJECT_E

                             200 <SQL_ATTR_ODBC_VERSION> 137 1456518129 no_name 0 OBJECT_E

              0x0000000000000003 138 1456518129 no_name 0 OBJECT_E

                              -6 139 1456518129 no_name 0 OBJECT_E

 PCFILES: ENTER SQLAllocHandle 140 1456518129 no_name 0 OBJECT_E

                               2 <SQL_HANDLE_DBC> 141 1456518129 no_name 0 OBJECT_E

              0x0000000071e383e0 142 1456518129 no_name 0 OBJECT_E

              0x0000000071a00278 143 1456518129 no_name 0 OBJECT_E

 PCFILES: EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS) 144 1456518129 no_name 0 OBJECT_E

                               2 <SQL_HANDLE_DBC> 145 1456518129 no_name 0 OBJECT_E

              0x0000000071e383e0 146 1456518129 no_name 0 OBJECT_E

              0x0000000071a00278 (0x0000000071e38420) 147 1456518129 no_name 0 OBJECT_E

 PCFILES: ENTER SQLSetConnectAttr 148 1456518129 no_name 0 OBJECT_E

              0x0000000071e38420 149 1456518129 no_name 0 OBJECT_E

                             110 <SQL_ODBC_CURSORS> 150 1456518129 no_name 0 OBJECT_E

                               2 <SQL_CUR_USE_DRIVER> 151 1456518129 no_name 0 OBJECT_E

                              -6 <SQL_IS_INTEGER> 152 1456518129 no_name 0 OBJECT_E

 PCFILES: EXIT SQLSetConnectAttr with return code 0 (SQL_SUCCESS) 153 1456518129 no_name 0 OBJECT_E

              0x0000000071e38420 154 1456518129 no_name 0 OBJECT_E

                             110 <SQL_ODBC_CURSORS> 155 1456518129 no_name 0 OBJECT_E

                               2 <SQL_CUR_USE_DRIVER> 156 1456518129 no_name 0 OBJECT_E

                              -6 <SQL_IS_INTEGER> 157 1456518129 no_name 0 OBJECT_E

<Here is where the error occurs.  The return code is -1 instead of 1 when this call succeeds.>

 PCFILES: ENTER SQLConnect 158 1456518129 no_name 0 OBJECT_E

              0x0000000071e38420 159 1456518129 no_name 0 OBJECT_E

              0x0000000071a00560 [      12] "<User DSN Name>" 160 1456518129 no_name 0 OBJECT_E

                              12 161 1456518129 no_name 0 OBJECT_E

              0x0000000071a004e0 [      12] "<SQL Server user Id>" 162 1456518129 no_name 0 OBJECT_E

                              12 163 1456518129 no_name 0 OBJECT_E

              0x0000000000000000 [       0] XXXXXXX 164 1456518129 no_name 0 OBJECT_E

                               0 165 1456518129 no_name 0 OBJECT_E

 PCFILES: EXIT SQLConnect with return code -1 (SQL_ERROR) 166 1456518129 no_name 0 OBJECT_E

              0x0000000071e38420 167 1456518129 no_name 0 OBJECT_E

              0x000000009a66a53c [      24] "                       R" 168 1456518129 no_name 0 OBJECT_E

                              24 169 1456518129 no_name 0 OBJECT_E

              0x000000009a66a4fa [      24] "                       i" 170 1456518129 no_name 0 OBJECT_E

                              24 171 1456518129 no_name 0 OBJECT_E

              0x0000000000000000 [       0] XXXXXXX 172 1456518129 no_name 0 OBJECT_E

                               0 173 1456518129 no_name 0 OBJECT_E

 DIAG:    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 174 1456518129 no_name 0

 OBJECT_E

 PCFILES: ENTER SQLFreeHandle 175 1456518129 no_name 0 OBJECT_E

                               1 <SQL_HANDLE_ENV> 176 1456518129 no_name 0 OBJECT_E

              0x0000000071e383e0 177 1456518129 no_name 0 OBJECT_E

 PCFILES: EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS) 178 1456518129 no_name 0 OBJECT_E

                               1 <SQL_HANDLE_ENV> 179 1456518129 no_name 0 OBJECT_E

              0x0000000071e383e0 180 1456518129 no_name 0 OBJECT_E

 64                 dsn='<User DSN Name>'

 65                 uid=<SQL Server user Id>

 66                 pwd=XXXXXXXX

 67                 schema=dbo

 68                 ;

 ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver

        specified

 ERROR: Error in the LIBNAME statement.

 69        

[Reeza]

SAS UE can't connect to SQL databases. 

There's a post from Cynthia on here somewhere that covers that. 

But I didn't think libname access to access DB was possible either. 

[Cynthia_sas]

Paul:
Reeza is correct. SAS University Edition cannot use ODBC, OLE-DB, Oracle or many other engines. If you look under "snippets" in SAS University Edition, you will find examples for how to import and export. The examples do not include ODBC or MySQL or SQL Server. There have been a few previous posts on the subject -- SAS UE is intended for learning purposes, and for most learning purposes, reading CSV files or using the XLSX capability is sufficient.

cynthia

[Paul_OldenKamp_org · Accepted solution]

Hi Cynthia,

There are a lot of things to learn with SAS.  We’re interested in learning DS2 and the integration of database access through FedSQL and program logic.  The use of SQL Server data would make these programs more realistic and even allow some performance benchmarking between different techniques.

The references to SAS/ACCESS for ODBC not being part of SAS University or listing engines that are part of other SAS/ACCESS products that are not part of SAS University is an unhelpful red herring.  My question and the code used to illustrate it only use SAS/ACCESS for PC Files and the pcfiles engine that is included with University Edition.

***SOLUTION***  I have figured out the solution to my problem and am now able to read a SQL Server database table into my program in SAS University.  I had to stop the PC Files Server service running on the PC where I had installed the SAS PC Files Server and restart it as a desktop application.  The CLI error stopped occurring in University Edition and I could read a database table in a SAS proc or datastep using the libname created in University Edition.

I think that the fact that the SAS PC Files Server generates an error when running as a service is a bug that should be fixed.  This would be a good situation to document as a SAS Note in the Knowledge Base.

Paul