Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

[ Edited ]

 Base SAS and SAS Visual Analytics were installed in Server. I can able to connect SQl server from SAS. But, I cannot do the same when accessed via SAS Visual Analytics. Tried importing data source from "ODBC" options with user ID/password but it throws an error "Libref DB_ODBC is not assigned.". Is there a way to solve this ?

 

 


Accepted Solutions
Solution
Tuesday
Occasional Contributor
Posts: 7

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

Thanks @alexal and @SASKiwi 

 

We were able to solve the problem.

While setting up ODBC data sources we were giving User DNS instead of  System DNS.

 

View solution in original post


All Replies
SAS Employee
Posts: 115

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

@KarunaTiwari,

 

The library with name DB_ODBC is pre-assigned in the metadata server or you specified the connection options in VA interface? I would like to see SAS log with an error.

Occasional Contributor
Posts: 7

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

[ Edited ]

Hi Alex,

 

We are specifying the connection options in the VA Interface.

Please find the SAS log below:

 

Libref DB_ODBC is not assigned.

1 The SAS System 17:29 Thursday, August 10, 2017

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M4 MBCS3170)
Licensed to  Site 11604339.
NOTE: This session is executing on the X64_SR12R2 platform.

 

NOTE: Additional host information:

X64_SR12R2 WIN 6.3.9600 Server

NOTE: SAS Initialization used (Total process time):
real time 0.02 seconds
cpu time 0.03 seconds

NOTE: The autoexec file, D:\SASConfig\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1
2 The SAS System 17:29 Thursday, August 10, 2017


1 The SAS System 17:29 Thursday, August 10, 2017

1
2
/* *********************************************** */
/* Assign the libref for the data in a ODBC table */
/*
2 ! *********************************************** */


LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
2 ! DATAsrc="Test" USER=***** PASSWORD="{sas002}9A8C3F4E20629B7F34D2C88A5569E5995AE23782" ;


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.
3 options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;

 

Rgds,

Karuna

Super User
Posts: 3,105

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

Have you defined the data source Test in the ODBC Administrator of your SAS VA application server? The error message indicates it hasn't.

Occasional Contributor
Posts: 7

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

Hi,

 

Please could you guide how to define the data source in the ODBC Administrator of your SAS VA application.

We tried doing it but failed.

 

Rgds,

Karuna

SAS Employee
Posts: 115

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

@KarunaTiwari,

 

The ODBC Driver Manager and Administrator are Microsoft products that are included with all ODBC drivers. When you install an ODBC driver, the ODBC Driver Manager and Administrator are also installed. The ODBC Administrator should appear as an icon in the Control Panel. However, it can also appear in the Start menu.

 

After you install the ODBC driver, you can use the ODBC Administrator to define and manage the data sources. A data source associates a particular ODBC driver with the data you want to access through that driver. It can consist of information about the data you want to access and its associated operating system, DBMS, and network platform (if any) used to access the DBMS. For information about how to configure your data sources, refer to the documentation provided with your ODBC drivers.

Occasional Contributor
Posts: 7

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

@alexal

 

              Are you talking about ODBC driver installation in server or desktop ? We can access data when using it in server but can't do that when SAS VA is used in Web App from Desktop.

SAS Employee
Posts: 115

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

@KarunaTiwari,

 

On the server. 

Super User
Posts: 3,105

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

If you are having problems setting up ODBC data sources in the server ODBC Administrator you might want to consider doing everything in a connection string on your LIBNAME statement instead. This post shows you how:

 

https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...

 

We use this type of connection exclusively as it avoids having to maintain data sources in the ODBC Administrator.

Solution
Tuesday
Occasional Contributor
Posts: 7

Re: Cannot access ODBC tables from MIcrosft SQL server in SAS Visual Analytics

Thanks @alexal and @SASKiwi 

 

We were able to solve the problem.

While setting up ODBC data sources we were giving User DNS instead of  System DNS.

 

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 282 views
  • 3 likes
  • 3 in conversation