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

Problems when connecting SAS EG to MS SQL Server databases

Aj — Wed, 04 Oct 2017 17:22:44 GMT

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 ;

Error Connecting SAS EG to SQL Server

Aj — Wed, 04 Oct 2017 17:25:25 GMT

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 ;

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

Aj — Wed, 04 Oct 2017 17:28:48 GMT

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 ;

Re: Error Connecting SAS EG to SQL Server

Kurt_Bremser — Wed, 04 Oct 2017 17:29:24 GMT

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

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

Reeza — Wed, 04 Oct 2017 17:38:56 GMT

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 ;

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

ballardw — Wed, 04 Oct 2017 17:42:52 GMT

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>

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

Aj — Wed, 04 Oct 2017 18:06:33 GMT

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.

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

Aj — Wed, 04 Oct 2017 18:09:25 GMT

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.

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

Reeza — Wed, 04 Oct 2017 18:13:23 GMT

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.

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

SASKiwi — Wed, 04 Oct 2017 19:15:27 GMT

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.

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

Aj — Wed, 04 Oct 2017 19:27:21 GMT

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.

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

Aj — Wed, 04 Oct 2017 19:29:30 GMT

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.

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

Aj — Mon, 09 Oct 2017 18:04:19 GMT

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