My team is currently migrating from SAS Enterprise Guide to SAS Grid and I am attempting to transfer my frequently used programs to the Grid. This is how I connect to a specific server in SAS Enterprise Guide:
PROC SQL; CONNECT TO SQLSVR AS sasqrm (user=&eid_sqlconn password=&pwd_sqlconn datasrc=servername);
This connection string is not effective in SAS Grid.
The code below was my best attempt at connecting to this server in SAS Grid based off of other questions asked with SAS community. When I run the code I receive this error: "ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Driver Manager Message file not found. Please check for the
value of InstallDir in your odbc.ini."
libname abc odbc noprompt ="uid=&eid_sqlconn; pwd=&pwd_sqlconn;dsn=servername; DRIVER=SQL Server Native Client 11.0; Trusted_Connection=yes" DATABASE = database_name schema = dbo;
Is there an obvious error I'm making in this connection string?
Check odbc.ini file under SASHome/MSSQLODBC/ for what driver your connection is pointing to..
your driver below is giving the description not the actual driver file location, it should be something like, Driver =$SASHome/MSSQLODBC/lib/XXX.so
What OS is your SAS Grid running on versus your current SAS server? Are both SAS 9.4? What SAS product are you using to connect to SQL Server? Your existing connection appears to be using SAS/ACCESS to SQL Server whereas your SAS Grid test is using SAS/ACCESS to ODBC. Is this the first attempt to get SAS Grid working with SQL Server or was it working and now it isn't?
Your "old" code should continue to work "as-is". It's just some install & config that still needs to be done as part of migration.
Moving to a SAS Grid indicates that you're working in a company big enough to have an IT department with a SAS Admin. Contact these people. They need to support you/make it work for you.
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.
Find more tutorials on the SAS Users YouTube channel.