Unable to connect to ODBC

Reply
Occasional Contributor
Posts: 12

Unable to connect to ODBC

I'm trying to making a connection to an ODBC server however I'm getting this error message:

 

ERROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Data source name not found, and no default driver
specified
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
687 create table test1 as select * from connection to ODBC (
688 select
689 a.*
690 FROM rptingdata.roactreacc a
691 FETCH FIRST 20 ROWS ONLY
692
693 ) ;
NOTE: Statement not executed due to NOEXEC option.
694 disconnect from ODBC;
NOTE: Statement not executed due to NOEXEC option.
695 quit;
NOTE: The SAS System stopped processing this step because of errors.

 

Here's my code:

 

 

 

proc sql;
CONNECT TO ODBC (DSN=SERVER user=U212XXXX password = XXXXXXX );
create table test1 as select * from connection to ODBC (
select
a.*
FROM rptingdata.roactreacc a
FETCH FIRST 20 ROWS ONLY

) ;
disconnect from ODBC;
quit;

Any help would be appreciated!

 

 

Super User
Posts: 24,026

Re: Unable to connect to ODBC

Posted in reply to jmoore168
What happens if you try a libname connection instead of doing SQL Pass Through?
Super User
Posts: 4,030

Re: Unable to connect to ODBC

Posted in reply to jmoore168

The error message is telling you there is no data source called SERVER defined in your odbc.ini (since you are on Unix) nor do you have a default ODBC driver defined. These need to be added before your connection will work.

 

Alternatively you could try defining everything in the connection string which will look similar to this:

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;uid=myusr1;pwd=mypwd1;");
  create table Want  as 
  select * from connection to odbc
  (SELECT *
   FROM [MyDatabase].[MySchema1].[MyTable]
   )
  ;
  disconnect from odbc;
quit;
Ask a Question
Discussion stats
  • 2 replies
  • 55 views
  • 2 likes
  • 3 in conversation