Problems when connecting SAS EG to MS SQL Server databases

Accepted Solution Solved
Reply
Contributor Aj
Contributor
Posts: 20
Accepted Solution

Problems when connecting SAS EG to MS SQL Server databases

 

Any inputs would be very useful.Smiley Sad

I am not able to connect SAS EG 7.1 to SQL Server. I tried different syntax but nothing works,

 

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.

 

My system is 64 bit. I am using SAS EG is 32 bit to connect to SQL Server. I have checked the ODBC connection to SQL Server  (64 bit) which is successful. 

I tried connecting to SQL server using SAS EG 7.1 (64 bit) but no success.

 

Codes I am trying:

LIBNAME temp ODBC DSN='CLARITY_CLONE' user=XXXXX pwd=XXXX schema=CLARITY; 

 

libname mydblib odbc
noprompt="uid=XXXX;pwd=XXXX;dsn=CLARITY_CLONE"
stringdates=yes;

 

proc sql;
connect to odbc (dsn=CLARITY_CLONE user=XXXXX pwd=XXXX);
create table test as select * from connection to odbc;
quit ;

 

 

 


Accepted Solutions
Solution
‎10-09-2017 02:02 PM
Super User
Posts: 3,861

Re: Problems when connecting SAS EG to MS SQL Server databases

Your error message is clear. The SQL Server data source CLARITY_CLONE is not defined on the server where SAS is running. I can only assume you have defined the data source perhaps on your PC, not where SAS is running as @Reeza also says.

View solution in original post


All Replies
Contributor Aj
Contributor
Posts: 20

Error Connecting SAS EG to SQL Server

Any inputs would be very useful.

I am not able to connect SAS EG 7.1 to SQL Server. I tried different syntax but nothing works,

 

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.

 

My system is 64 bit. I am using SAS EG is 32 bit to connect to SQL Server. I have checked the ODBC connection to SQL Server  (64 bit) which is successful. 

I tried connecting to SQL server using SAS EG 7.1 (64 bit) but no success.

 

Codes I am trying:

LIBNAME temp ODBC DSN='CLARITY_CLONE' user=XXXXX pwd=XXXX schema=CLARITY; 

 

libname mydblib odbc
noprompt="uid=XXXX;pwd=XXXX;dsn=CLARITY_CLONE"
stringdates=yes;

 

proc sql;
connect to odbc (dsn=CLARITY_CLONE user=XXXXX pwd=XXXX);
create table test as select * from connection to odbc;
quit ;

Super User
Posts: 9,923

Re: Error Connecting SAS EG to SQL Server

Please do not double-post; it only makes keeping track of the answers difficult.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor Aj
Contributor
Posts: 20

CLI ERROR to Microsoft ODBC Driver Manager SQL Server ODBC DSN Connection Error:

Any inputs would be very useful.

I am not able to connect SAS EG 7.1 to SQL Server. I tried different syntax but nothing works,

 

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.

 

My system is 64 bit. I am using SAS EG is 32 bit to connect to SQL Server. I have checked the ODBC connection to SQL Server  (64 bit) which is successful. 

I tried connecting to SQL server using SAS EG 7.1 (64 bit) but no success.

 

Codes I am trying:

LIBNAME temp ODBC DSN='CLARITY_CLONE' user=XXXXX pwd=XXXX schema=CLARITY; 

 

libname mydblib odbc
noprompt="uid=XXXX;pwd=XXXX;dsn=CLARITY_CLONE"
stringdates=yes;

 

proc sql;
connect to odbc (dsn=CLARITY_CLONE user=XXXXX pwd=XXXX);
create table test as select * from connection to odbc;
quit ;

Super User
Posts: 13,338

Re: CLI ERROR to Microsoft ODBC Driver Manager SQL Server ODBC DSN Connection Error:

Have you actually verified that your computer has access to MS SQL Server ODBC - does it appear in the ODBC Data source Administrator drivers?

Is there either a System DSN or File DSN named 'CLARITY_CLONE' in the ODBC administrator>

Contributor Aj
Contributor
Posts: 20

Re: CLI ERROR to Microsoft ODBC Driver Manager SQL Server ODBC DSN Connection Error:

Thank you very much for a quick look Ballard.Any inputs would be extremely helpful.

 

Yes.I have added it to System DSN. Please see the attachment. 

 

 

 

Super User
Posts: 23,323

Re: Problems when connecting SAS EG to MS SQL Server databases

I don't believe the DSN name should be in quotations. Try it first without a schema and then check it again with a schema to see if you can even connect to the DB.

 

LIBNAME temp ODBC DSN=CLARITY_CLONE user=XXXXX pwd=XXXX ; 
Contributor Aj
Contributor
Posts: 20

Re: Problems when connecting SAS EG to MS SQL Server databases

Thank you very much for checking on this Rezza. I tried it out without quotes and I get the same error.

 

LIBNAME temp ODBC DSN=CLARITY_CLONE user=munjula pwd=XXXXXXXXXX;
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.

Super User
Posts: 23,323

Re: Problems when connecting SAS EG to MS SQL Server databases

Is your EG on a server or working locally? If it's on a server the DSN needs to be set up on the server not your computer. 

Otherwise, make sure it's running locally. 

Contributor Aj
Contributor
Posts: 20

Re: Problems when connecting SAS EG to MS SQL Server databases

Thank you very much @Reeza . you are correct. My SAS EG connects to a server and I am giving a

DSN  = <My local ODBC connection name> (CLARITY_CLONE). I have requested for the server DSN name. Hopefully, that would resolve the problem. Thanks again for think through it. 

 

I am not able to run it locally so I will wait to get the DSN name at the server for the requested database.

Contributor Aj
Contributor
Posts: 20

Re: Problems when connecting SAS EG to MS SQL Server databases

Thank you @Reeza. Your inputs helped me to solve the problem. the DSN= should be the server DSN
Solution
‎10-09-2017 02:02 PM
Super User
Posts: 3,861

Re: Problems when connecting SAS EG to MS SQL Server databases

Your error message is clear. The SQL Server data source CLARITY_CLONE is not defined on the server where SAS is running. I can only assume you have defined the data source perhaps on your PC, not where SAS is running as @Reeza also says.

Contributor Aj
Contributor
Posts: 20

Re: Problems when connecting SAS EG to MS SQL Server databases

Thanks @SASKiwi and you are right. I will notify you and @Reeza and also @ballardw when I am able to connect using Server DSN names instead of my local DSN names. I appreciate all of your quick responses.

☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 782 views
  • 0 likes
  • 5 in conversation