topic Re: Connecting to SQL server in SAS Grid in Administration and Deployment

Connecting to SQL server in SAS Grid

spillemm — Tue, 21 Dec 2021 16:02:21 GMT

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?

Re: Connecting to SQL server in SAS Grid

Kiranmadhu — Tue, 21 Dec 2021 19:08:56 GMT

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

Re: Connecting to SQL server in SAS Grid

SASKiwi — Tue, 21 Dec 2021 20:50:27 GMT

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?

Re: Connecting to SQL server in SAS Grid

Sajid01 — Thu, 23 Dec 2021 16:07:19 GMT

Looks like you are having Data Direct odbc drivers.
Please review the documentation.

Re: Connecting to SQL server in SAS Grid

Patrick — Sun, 26 Dec 2021 23:34:09 GMT

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.

Re: Connecting to SQL server in SAS Grid

Acamacho85 — Thu, 10 Nov 2022 17:31:48 GMT

You have mentioned you are working in GRID, so make sure the configuration is done in every node of the GRID.