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

I'm attempting to connect SAS EG 5.1 to a MS SQL server db, and from what I've seen online, I should create an ODBC data source for the database (done successfully), then in SAS, connect to it using the libname statement.

My problem is, when I try to connect via the libname statement in SAS I get the following error:

                       "

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

ERROR: Error in the LIBNAME statement.

"

My statement is:

LIBNAME SQL ODBC DSN='db1' ;

Which according to other people should work (db1 is the name of my odbc data source). Does anybody know what is going wrong and how I might fix it?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

For a server setup it would be best to do that for all required SQL Server data sources and SAS users and it should be up to your SAS admin folks to sort out.

It is best practice to set up LIBNAMEs in SAS metadata so end users don't have to figure things out for themselves.

Here is a link to the required set up for SAS 9.3:

http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/64205/PDF/default/config.pdf

By the way you can confirm what SAS version EG is using by going into the Properties of your SAS server (usually SASApp) and viewing the SAS startup log.

View solution in original post

6 REPLIES 6
SASKiwi
PROC Star

Where did you define your ODBC data source? It has to be done on the SAS server that EG is connecting to. Is your SAS server local - your PC - or remote? If remote it must be defined there.

If your SAS server is running on Windows you have the option of bypassing setting up an ODBC data source and doing it all in the LIBNAME statement assuming Windows Authentication:

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = MyDatabase schema = dbo;

If your SAS server uses Unix then you have to configure an ODBC.INI file with your data sources. What version of SAS are you using?

genericuserid111
Calcite | Level 5

I defined the ODBC data source locally, and I'm connecting to a SAS unix server, so I guess that is the problem.

On the unix server would I be able to define the ODBC.ini file in a local dir or is it something that is system wide? I believe I'm using SAS 9.3 but I'm not 100% sure as to how the software was installed on my computer.

SASKiwi
PROC Star

For a server setup it would be best to do that for all required SQL Server data sources and SAS users and it should be up to your SAS admin folks to sort out.

It is best practice to set up LIBNAMEs in SAS metadata so end users don't have to figure things out for themselves.

Here is a link to the required set up for SAS 9.3:

http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/64205/PDF/default/config.pdf

By the way you can confirm what SAS version EG is using by going into the Properties of your SAS server (usually SASApp) and viewing the SAS startup log.

ChaoChen
Calcite | Level 5

Finally know the reason why I cannot connect to sqlserver

sasquestions
Calcite | Level 5

How did you resolve it. Even I am getting the same error

SASKiwi
PROC Star

Please start a new post including the code you are trying to connect to SQL Server with, what OS you are running on and what version of SAS you are using. You can add a link to this post if you think it would be helpful.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 13051 views
  • 6 likes
  • 4 in conversation