DATA Step, Macro, Functions and more

Suggest me the Connectivity code for SQL in SAS

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Suggest me the Connectivity code for SQL in SAS

[ Edited ]

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.


Accepted Solutions
Solution
a month ago
Super User
Posts: 3,920

Re: Suggest me the Connectivity code for SQL in SAS

[ Edited ]
Posted in reply to Sathish_jammy

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

 

View solution in original post


All Replies
Moderator
Posts: 327

Re: Suggest me the Connectivity code for SQL in SAS

Posted in reply to Sathish_jammy

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!

Contributor
Posts: 30

Re: Suggest me the Connectivity code for SQL in SAS

Posted in reply to AndrewHowell

Thanks for your suggestion. Smiley Happy 

You guys are really awesome!. @AndrewHowell @SASKiwi

& The code works GREAT!...

 

Got all the tables from SQL. 

 

 

Solution
a month ago
Super User
Posts: 3,920

Re: Suggest me the Connectivity code for SQL in SAS

[ Edited ]
Posted in reply to Sathish_jammy

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 71 views
  • 0 likes
  • 3 in conversation