BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aj
Fluorite | Level 6 Aj
Fluorite | Level 6

 

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 ;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

12 REPLIES 12
Aj
Fluorite | Level 6 Aj
Fluorite | Level 6

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 ;

Aj
Fluorite | Level 6 Aj
Fluorite | Level 6

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 ;

ballardw
Super User

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>

Aj
Fluorite | Level 6 Aj
Fluorite | Level 6

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. 

 

 

 

Reeza
Super User

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 ; 
Aj
Fluorite | Level 6 Aj
Fluorite | Level 6

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.

Reeza
Super User

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. 

Aj
Fluorite | Level 6 Aj
Fluorite | Level 6

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.

Aj
Fluorite | Level 6 Aj
Fluorite | Level 6
Thank you @Reeza. Your inputs helped me to solve the problem. the DSN= should be the server DSN
SASKiwi
PROC Star

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.

Aj
Fluorite | Level 6 Aj
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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