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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

12 REPLIES 12
Reeza
Super User

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;
Reeza
Super User

1. Are you running on a server?

2. What does proc setinit say?

 

3. Have you tried including user and pwd?

mduarte
Quartz | Level 8

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
Super User

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

mduarte
Quartz | Level 8

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
PROC Star

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
Rhodochrosite | Level 12

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
Quartz | Level 8

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

Kurt_Bremser
Super User

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
Rhodochrosite | Level 12

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
Tourmaline | Level 20

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

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
  • 12 replies
  • 12277 views
  • 5 likes
  • 7 in conversation