Suggest me the Connectivity code for SQL in SAS

[Sathish_jammy]

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.

[AndrewHowell]

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!

[Sathish_jammy]

Thanks for your suggestion.  

You guys are really awesome!. @AndrewHowell @SASKiwi

& The code works GREAT!...

Got all the tables from SQL. 

[SASKiwi · Accepted solution]

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=***";