Desktop productivity for business analysts and programmers

Connecting SAS EG 5.1 to a MS SQL Server db

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Connecting SAS EG 5.1 to a MS SQL Server db

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!


Accepted Solutions
Solution
‎05-19-2015 04:36 PM
Respected Advisor
Posts: 3,065

Re: Connecting SAS EG 5.1 to a MS SQL Server db

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


All Replies
Respected Advisor
Posts: 3,065

Re: Connecting SAS EG 5.1 to a MS SQL Server db

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?

Occasional Contributor
Posts: 11

Re: Connecting SAS EG 5.1 to a MS SQL Server db

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.

Solution
‎05-19-2015 04:36 PM
Respected Advisor
Posts: 3,065

Re: Connecting SAS EG 5.1 to a MS SQL Server db

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.

Occasional Learner
Posts: 1

Re: Connecting SAS EG 5.1 to a MS SQL Server db

Finally know the reason why I cannot connect to sqlserver

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 3996 views
  • 4 likes
  • 3 in conversation