Unable to connect to ODBC

[jmoore168]

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!

[Reeza]

What happens if you try a libname connection instead of doing SQL Pass Through?

[SASKiwi]

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;