Desktop productivity for business analysts and programmers

SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

I am able to connect an SQL SERVER ODBC data source by selecting from the menu File > Open > ODBC...  and then Machine Data Source, mydatasourcename (of type user).  I then select a table and all is fine.  

 

By checking properties of the resulting link, advanced, here is the connection string:

ODBC;Provider=MSDASQL.1;Extended Properties="DSN=mydatasourcename;Description=description of my data source anme;UID=XXXXX;Trusted_Connection=Yes;APP=SAS Enterprise Guide 7.1;WSID=YYYY;DATABASE=ZZZZZ"

 

However, if I try to create a connection, e.g. ,via:

LIBNAME libref ODBC DATAsrc='mydatasourcename';

 

I get the following error:

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.

 

I have tried many variations to the above LIBNAME statement and all result in the same error.  

 

Is anyone able to offer a suggestion of how to get this to work?  I need access to the DSN programmatically as I would like to run a stored procedure.  I am using SAS Enterprise Guide 7.11.

 

Thanks.


Accepted Solutions
Solution
‎01-28-2016 07:03 PM
Respected Advisor
Posts: 3,060

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

[ Edited ]

You can specify the complete ODBC connection definition on a LIBNAME statement like so:

 

libname TEST odbc noprompt = "server=MYSQLServer;DRIVER=SQL Server;Trusted Connection=yes" 
QUALIFIER = Mydatabase SCHEMA = Myschema;

 

When you use File\Open\ODBC in EG you are using your PC to connect to the database so all the setup has to be on your PC.

 

With the LIBNAME statement you are using your SAS Application Server to connect, so all the setup must be done on the server. Fortunately you can but only for Windows-based SAS servers. 

View solution in original post


All Replies
Grand Advisor
Posts: 17,316

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

Don't use libref as your library name, that's confusing and probably a potential issue down the road.

 

What happens with the following:

 

LIBNAME mylib ODBC dsn='mydatasourcename' schema=dbo;
Frequent Contributor
Posts: 75

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

I get the same error
Grand Advisor
Posts: 17,316

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

1. Are you running on a server?

2. What does proc setinit say?

 

3. Have you tried including user and pwd?

Frequent Contributor
Posts: 75

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

[ Edited ]

1. I am using a local installation of Enterprise Guide, but running it on a server (i.e. via Tools > Connections)

2. PROC SETINT includes SAS/ACCESS Interface to ODBC 

3. Yes - have used both user and pwd

 e.g. 

LIBNAME mylib ODBC dsn='mydsn' user=myuser schema=myschema pwd=mypwd;

 

Grand Advisor
Posts: 17,316

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

I'm out of idea's, hopefully, someone else can help further Smiley Sad

Frequent Contributor
Posts: 75

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

[ Edited ]

Okay. I am running a local installation of Enterprise Guide, but SAS is installed on a server.  I was using a DSN on my local machine, but when the DSN is created on the server, I can connect programmatically via:

 

libname sql odbc dsn='mydsn' user=myuser pw=mypw schema=myschema;

However, I am not happy with this as a solution because I don't have remote access to the server in order to create DSNs as required and also, I don't understand  why when I use the menu options (File > Open > ODBC...) I can connect via a DSN on my local machine.  

 

If I want to use a local DSN programmatically, do I need to go to Tools > Connections and then select <do not use a profile> and then at the top of my program window, select "Local" for selected server?  If so, then the problem (I believe) would be that I have SAS/ACCESS Interface to ODBC on the server side but not on my local PC (i.e. running PROC SETINT no longer shows SAS/ACCESS Interface to ODBC).  Would this be correct?

 

 

Solution
‎01-28-2016 07:03 PM
Respected Advisor
Posts: 3,060

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

[ Edited ]

You can specify the complete ODBC connection definition on a LIBNAME statement like so:

 

libname TEST odbc noprompt = "server=MYSQLServer;DRIVER=SQL Server;Trusted Connection=yes" 
QUALIFIER = Mydatabase SCHEMA = Myschema;

 

When you use File\Open\ODBC in EG you are using your PC to connect to the database so all the setup has to be on your PC.

 

With the LIBNAME statement you are using your SAS Application Server to connect, so all the setup must be done on the server. Fortunately you can but only for Windows-based SAS servers. 

Super Contributor
Posts: 387

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

It sounds like you want a DSN-less connection where you specify everything that is needed in code so you don't have to set up a DSN on the machine. There is an example of this (and the method used to find all the parameters you need) in the SAS Global Forum 2014 paper An Insider’s Guide to SAS/ACCESS® Interface to ODBC by Jeff Bailey from SAS Institute.

Frequent Contributor
Posts: 75

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

[ Edited ]

Great link - If I could accept more than one answer I would!  

Esteemed Advisor
Posts: 6,646

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

In your setup, when you use the File menu of EG, EG accesses your local DSN, and then automagically transfers the data to the server and runs a step there to read the data.

Anything you do in SAS code runs in the Workspace Server instance on your SAS server, and has only access to resources defined on the server.

The server can never see DSNs defined on your PC.

 

If you wanted to access your local DSN from SAS code, you would need a local SAS installation, and the necessary licenses for that.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 2,111

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

mduarte,

 

Kurt's comments are key to understanding the data flow.

 

If you build a DSN using the file menu, the data are read to your local PC and transferred to the remote server for processing.  If the data are large, this can take a lot more time than directly processing on the remote server.  It will double the I/O and can increase the elapsed time even more depending on the connection speeds.  Databases and servers are often connected via multi-gigabit lines and workstations may only be 100 megabit; the connection between workstations and servers in different physical facilities can be even slower.

Esteemed Advisor
Posts: 5,194

Re: SAS EG 7.11 LIBNNAME ODBC says Data source name not found (but can connect via menu)

I don't know the reasons for your SAS server setup, but one aspect is that a server is a way of centralize data management. Benefits would be better control, and reuse of data and logic between users/projects.

So even if you can work on your own, it's not sure if that's the best in all situations. So getting help by defining DSN is maybe not only a bad thing. This is true for any library and table definitions in the metadata.

Data never sleeps
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 1417 views
  • 5 likes
  • 7 in conversation