Problems when connecting SAS EG to MS SQL Server databases

[Aj]

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]

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 ;

[Kurt_Bremser]

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

[Aj]

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]

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]

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]

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]

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]

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]

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]

Thank you @Reeza. Your inputs helped me to solve the problem. the DSN= should be the server DSN

[SASKiwi · Accepted solution]

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]

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.