topic Re: Suggest me the Connectivity code for SQL in SAS in SAS Programming

Suggest me the Connectivity code for SQL in SAS

Sathish_jammy — Thu, 21 Jun 2018 06:19:13 GMT

Hi,

I have SAS 9.4 and SQL(2008) application in my PC.My entire database are in SQL application.

I want to use SQL database in SAS. There i found some code for connectivity in internet but those are not effective.

Can any one please suggest the perfect code with an example for me to proceed further.

Libname New_lib ODBC NoPrompt="Driver=C:; Server=192.168.1.14; Database=Mvdsc1302; Uid=IT; pwd=***";


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.


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.

How/what should i insert in Driver filed, Please help me with correct format.

Re: Suggest me the Connectivity code for SQL in SAS

AndrewHowell — Thu, 21 Jun 2018 06:42:49 GMT

Forgetting SAS as the client application for the time being, let's first ensure the underlying SQL database client can connect to the SQL database server - because without that, any client applications will fail to connect.

So:

Can you verify your SQL client/server connectivity is working?
Perhaps look in your ODBC Administrator settings.
If your SQL client can connect, that is the connection string you should be using in your SAS libname statement.
If you have an ODBC entry which (successfully) connects to your SQL database, then use the simpler "dsn='MySqlDetails' rather than the lengthy connection string.

Good luck!

Re: Suggest me the Connectivity code for SQL in SAS

SASKiwi — Thu, 21 Jun 2018 07:53:25 GMT

Open the Windows ODBC Administrator tool and select the Drivers tab to find out the names of the ODBC drivers you have installed. As I suspect you are using SQL Server the likely driver name is SQL Server.

Libname New_lib ODBC NoPrompt="Driver=SQL Server;Server=192.168.1.14;Database=Mvdsc1302;Uid=IT;pwd=***";

Re: Suggest me the Connectivity code for SQL in SAS

Sathish_jammy — Thu, 21 Jun 2018 12:11:22 GMT

Thanks for your suggestion.

You guys are really awesome!. @AndrewHowell @SASKiwi

& The code works GREAT!...

Got all the tables from SQL.