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

[KarunaTiwari]

 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 ?

[alexal]

@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.

[KarunaTiwari]

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

[SASKiwi]

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

[KarunaTiwari]

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

[alexal]

@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.

[KarunaTiwari]

@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.

[alexal]

@KarunaTiwari,

On the server. 

[SASKiwi]

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.

[KarunaTiwari · Accepted solution]

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.