I am trying to get to the point where we can get data from a MSSQL server and bring that into our Viya Environment for use with CAS and Visual Analytics.
I went through the steps in the deployment guide to configure and verify my ODBC Connection. Results from that were mixed and are listed in the following txt.
At this point I am not sure if I have somehing configured wrong in SAS or what the problem is, any help would be greatly appreciated!
thank you in advance
Kevin
-----------------
SAS Viya running on CENTOS7
The server is set up with MS-ODBC Drivers and unixODBC
Can make a connection to the SQL server from isql and can run SQL commands to get data back from SQL server.
Here are some steps that I have taken and the configuration files that I think will help in diagnosing the issue.
------------------------------------------------------------------------
odbci.ini
[Test]
Driver=ODBC Driver 13 for SQL Server
Description=Test FGC SQL
Server=132.2.2.2
Database=FGC_CAS
[SQLServer]
Driver=ODBC Driver 13 for SQL Server
Description=My MS SQL Server
Trace=No
Server=132.2.2.2
odbcinst.ini
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1
UsageCount=1
SAS Config Files
workspaceserver_usermods.sh
#!/bin/bash -p
#
# Copyright (c) 2016, SAS Institute Inc., Cary, NC, USA, All Rights Reserved
#
# workspaceserver_usermods.sh
#
# This script extends workspaceserver.sh. Add local environment variables
# to this file so they will be preserved.
#
USERMODS_OPTIONS=
export ODBCSYSINI=/etc/
export ODBCINI=/etc/odbc.ini
export ODBCINST=/etc/odbcinst.ini
export LD_LIBRARY_PATH=:/opt/microsoft/msodbcsql/lib64/:$LD_LIBRARY_PATH
----------------------------------------------------------------------------------------
/opt/sas/viya/config/etc/sysconfig/compsrv/default/sas-compsrv
# Config for compute server
#COMPUTESERVER_LOG_PATH="/opt/sas/viya/config/var/log/compsrv/default"
#Uncomment the following line to enable lockdown for the compute server
#export COMPUTESERVER_LOCKDOWN_ENABLE=1
#ARM_SUBSYS=[ ARM_PROC | ARM_DSIO | ARM_ALL | ARM_NONE ]
#export ARM_SUBSYS=ARM_PROC
export ODBCSYSINI=/etc/
export ODBCINI=/etc/odbc.ini
export ODBCINST=/etc/odbcinst.ini
export LD_LIBRARY_PATH=:/opt/microsoft/msodbcsql/lib64/:$LD_LIBRARY_PATH
---------------------------------------------------------------------
/opt/sas/viya/config/etc/cas/default/cas_usermods.settings
export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini
export ODBCINSTINI=/etc/odbcinst.ini
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql17/lib64/
When following the validation from procedures post install here are the results
libname olib odbc dsn="SQLServer" user="sa" password=XXXXXXXXXXXXXXXXXX;
NOTE: Libref OLIB was successfully assigned as follows:
Engine: ODBC
Physical Name: SQLServer
OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
75
76 caslib newLib datasource=(srctype="odbc", username="sa",
77 password=XXXXXXXXXXXXXXXXXX, odbc_dsn="SQLServer");
NOTE: 'NEWLIB' is now the active caslib.
NOTE: Cloud Analytic Services added the caslib 'NEWLIB'.
NOTE: Action to ADD caslib NEWLIB completed for session MYSESSION.
78
79 proc casutil;
NOTE: The UUID 'd21238d1-4b06-b740-885e-320a760686a0' is connected using session MYSESSION.
80 list files incaslib="newLib";
Caslib Information
Library NEWLIB
Source Type odbc
Uid sa
Session local Yes
Active Yes
Personal No
Hidden No
Transient No
Odbc_dsn SQLServer
ERROR: The connection to the data source driver failed.
ERROR: ERROR: 523 80
ERROR: Function failed.
ERROR: The action stopped due to errors.
NOTE: Cloud Analytic Services processed the combined requests in 0.00303 seconds.
81 run;
82
83 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
96
Also have tried running this code
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
75
76 LIBNAME SQLUP ODBC DATAsrc=test SCHEMA=dbo user=sa password=XXXXXXXXXXXXXXXXXX ;
NOTE: Libref SQLUP was successfully assigned as follows:
Engine: ODBC
Physical Name: test
77
78 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
91
with this lib sqlup is added a table is diplayed however only the first char of the table name is shown and if you try and open the table a message reads The data set "SQLUP.t" is password protected and cannot be opened.
Have also tried to make a connection to the SQL from Data Explorer on inside Enviroment Manager with the following results when trying to list tables:
The connection to the data source driver failed.
ERROR: 523 80
Function failed.
The action stopped due to errors.
code: 2-0-310002
uri: /casManagement/dataSources/cas~fs~cas-shared-default~fs~TESTSQL/tables
path: /SASDataExplorer/ui/datasources/children
traceId: 37866a73d0f05006
----------------------------------------------------------------------
running this code from SASStudio 4
LIBNAME SQLUP ODBC DATAsrc=test SCHEMA=dbo user=sa password=XXXXXXXXXXXXXXXXXX ;
NOTE: Libref SQLUP was successfully assigned as follows:
Engine: ODBC
Physical Name: test
74
75 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
88
Lib SQLUP is created and the table is visiable however when you try to open the table the following error is displayed
CLI describe error: [
-------------------------------------------------------------------------------------
