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!
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.
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?
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.
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.
Finally know the reason why I cannot connect to sqlserver
How did you resolve it. Even I am getting the same error
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.