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

[mduarte]

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.

[Reeza]

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;

[mduarte]

I get the same error

[Reeza]

1. Are you running on a server?

2. What does proc setinit say?

3. Have you tried including user and pwd?

[mduarte]

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;

[Reeza]

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

[mduarte]

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?

[SASKiwi · Accepted solution]

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. 

[PaulHomes]

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.

[mduarte]

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

[Kurt_Bremser]

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.

[Doc_Duke]

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.

[LinusH]

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.