topic Re: Connect SQL server using SAS in SAS Programming

Connect SQL server using SAS

Rixile106 — Mon, 14 Feb 2022 17:00:32 GMT

good day expects.am trying to link to an SQL Server using SAS

I've got an example:

LIBNAME ADPT ODBC DSN='PSQLETSCDSAG01' SCHEMA=HMLN_MIS;

LIBNAME ADPT ODBC DSN='PSQLETSCDSAG01' SCHEMA=DBO;

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

Re: Connect SQL server using SAS

JosvanderVelden — Mon, 14 Feb 2022 17:56:03 GMT

Is SAS on the same server as SQL?
Where are the odbc schema's defined?
Ex. ODBC Data Sources 32-bit on the machine where SAS runs.

Best regards, Jos

Re: Connect SQL server using SAS

Reeza — Mon, 14 Feb 2022 17:56:13 GMT

Did you set up a DSN connection on your server/computer?

https://documentation.sas.com/doc/en/bicdc/9.4/bidsag/p1t9q2knb9tq5ln101dkrdsa9ymf.htm

LIBNAME is the second step in this process.

Re: Connect SQL server using SAS

SASKiwi — Mon, 14 Feb 2022 19:46:05 GMT

What OS does your SAS server run on? If you want to specify everything in the database connection string try something like this:

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = MyDatabase schema = dbo;

You will need to know the SQL Server server name, the ODBC driver name, the database and schema. Check with your SAS administrator who should know all the details. If you specify the DSN option then that has to be defined separately and how you do that depends on the SAS server OS.

Re: Connect SQL server using SAS

Sajid01 — Tue, 15 Feb 2022 00:18:34 GMT

Looks like the connection to SQL server has not been set.
Your SAS administrator should be able to help.