BookmarkSubscribeRSS Feed
JS3
Calcite | Level 5 JS3
Calcite | Level 5

Hi, I am having a problem with connecting to data in sql database.

 

I am not sure if I don't have certain driver or if the code I am using is incorrect. Could someone please help guiding me to the right direction?

 

1. Below is the product license that I have after running Proc Setinit

Inline image 1

 

2. Below is product installed after I ran Proc Product_Status

Inline image 2

 

It seems like sas access to sql server is not installed right?

 

I am also trying these codes to connect and all got error. Which direction of code should I be using? ODBC or sqlsvr?

 

I tried sqlsvr and got this error message

Code:

LIBNAME database1 sqlsvr user=datareader password=myspassword DATAsrc=clientA;

Error:

LIBNAME database1 sqlsvr user=datareader password=XXXXXXX DATAsrc=clientA;

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.

 

When I tried ODBC route, I got this message

Code:

LIBNAME database1 ODBC DSN=clientA User=datareader Password=myspassword ;

Error:

LIBNAME database1 ODBC DSN=clientA User=datareader Password=XXXXXXX;

ERROR: The ODBC engine cannot be found. ERROR: Error in the LIBNAME statement.

 

Could someone please point me to the right direction? Should DSN be the name of the database of each client as "ClientA" etc or should it be the name of the server?

 

Thank you so much!

7 REPLIES 7
ChrisNZ
Tourmaline | Level 20

1. Your images didn't make it to the post.

2. DSN is the name of the data source. It is visible in the list of sources when running ODBC Manager.

JS3
Calcite | Level 5 JS3
Calcite | Level 5

1.png2.png

Thank you.  Above are the image again.  Thank you!

JS3
Calcite | Level 5 JS3
Calcite | Level 5

Hi KurtBremser,

Here's the copy.paste version of log.  Do you have any recommendation on my questions?  Thank you

 

Current version: 9.04.01M5P091317


Expiration: 14FEB2018.
Grace Period: 45 days (ending 31MAR2018).
Warning Period: 45 days (ending 15MAY2018).
System birthday: 13DEC2017.
Operating System: WX64_WKS.
Product expiration dates:
---Base SAS Software
14FEB2018
---SAS/STAT
14FEB2018
---SAS/GRAPH
14FEB2018
---SAS/Secure 168-bit
14FEB2018
---SAS/Secure Windows
14FEB2018
---SAS Enterprise Guide
14FEB2018
---SAS/ACCESS Interface to PC Files
14FEB2018
---SAS/ACCESS Interface to Microsoft SQL Server
31DEC2017
---SAS Workspace Server for Local Access
14FEB2018
---SAS/ACCESS to Amazon Redshift
31DEC2017
---High Performance Suite
14FEB2018

16 proc Product_Status ; run;

For Base SAS Software ...
Custom version information: 9.4_M5
Image version information: 9.04.01M5P090617
For SAS/STAT ...
Custom version information: 14.3
For SAS/GRAPH ...
Custom version information: 9.4_M5
For SAS/ACCESS to Amazon Redshift ...
Custom version information: 9.42
For High Performance Suite ...
Custom version information: 2.2_M6
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M5
NOTE: PROCEDURE PRODUCT_STATUS used (Total process time):
real time 0.24 seconds
cpu time 0.00 seconds

SASKiwi
PROC Star

The expiry date for the product SAS/ACCESS to SQL Server is 31Dec2017 so you need a new SAS licence to get it working again.

ChrisNZ
Tourmaline | Level 20

The whole licence is expired Smiley LOL. What's your system date? Or have you not restarted SAS in the last 3 months?

JBailey
Barite | Level 11

Hi @JS3

 

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.

 

This error message points to a configuration issue in the ODBC environment. Have your Systems Administrator check the odbc.ini and odbcinst.ini files. Chances are there is no stanza for the "clientA" data source. You can try a DSN-less connection. Here is an example:

 

 

libname mssql sqlsvr complete="DRIVER={SAS ACCESS to SQL Server};HOST='mysqlserver.mycompany.com';
PORT=1433;UID=myuserid;PWD=mypassword;
EnableScrollableCursors=3";

 

If this doesn't work SAS/ACCESS Interface to Microsoft SQL Server may not be configured properly. 

 

Configuration is covered here: https://support.sas.com/en/documentation/install-center.html

 

Configuration for Windows

Configuration for Linux 

 

EnableScrollableCursors=3 is covered in this post

 

Good luck,

Jeff

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1686 views
  • 0 likes
  • 5 in conversation