topic Re: Tables not shown up in Administration and Deployment

Tables not shown up

Analysts12 — Thu, 03 Jun 2021 19:21:33 GMT

I have a sql library created in sas . The database team is able to view tables at db level , but in SAS EG , no tables are getting populated.
What to do ?

Re: Tables not shown up

SASKiwi — Thu, 03 Jun 2021 22:30:26 GMT

How is the data library being created in SAS? Via a LIBNAME statement in code or is it being created in SAS metadata via SAS Management Console.

Does the library show in the EG Server List? If so what happens when you expand the library?

Re: Tables not shown up

Analysts12 — Fri, 04 Jun 2021 02:20:53 GMT

It is created via management console .
Yes, the library is visible on SAS EG but tables are not getting populated there as well.

Re: Tables not shown up

SASKiwi — Fri, 04 Jun 2021 02:38:42 GMT

Try adding this to your SMC data library definition:

This should ensure SQL library tables get listed.

Re: Tables not shown up

Analysts12 — Fri, 04 Jun 2021 06:33:38 GMT

that didn't work ...
😞

Re: Tables not shown up

DavidHD — Fri, 04 Jun 2021 07:00:15 GMT

What happens if you submit the libname statement in code from EG? Do the tables then show up?

Re: Tables not shown up

SASKiwi — Fri, 04 Jun 2021 08:40:42 GMT

There is probably something wrong with the data library definition then. Try @DavidHD 's suggestion to trial and get it working. For the SQL LIBNAME to work you need to define the server, database and schema.

Re: Tables not shown up

Analysts12 — Fri, 04 Jun 2021 12:07:04 GMT

Ok.. I tried via code as well, it didn't work.

However, the database team suggested that the user credentials are not created via Active directory, so maybe that is the reason, its not linking properly

Re: Tables not shown up

DavidHD — Fri, 04 Jun 2021 12:22:28 GMT

Check that you can reach the DB Server from the SAS Server (e.g. ping it) - if this doesn't work check with the Firewall/DNS-Team.
Validate your user credentials with the DB Team
Submit via code libname statement with this works move the SMC
If you get errors search for that error online and fix as appropriate - you might need to get in contact with the DB admins again

Re: Tables not shown up

Ksharp — Fri, 04 Jun 2021 12:45:21 GMT

Check SCHEMA= option of LIBNAME .

Re: Tables not shown up

Sajid01 — Sun, 06 Jun 2021 00:09:54 GMT

Hello @Analysts12
The information you provided in insufficient. It will lead to many questions.
1.From your other posts, I presume the SAS is installed on Linux Server and the user is accessing the server using EG.

2.What is your database ? Have you installed the database client / odbc drivers on the App server? In a corporate environment this is typically done by Database Administrators.

3.If the answers to the above questions is yes, can you access the database using libname statement?

(You have said in an earlier answer that library is visible but not answered about the libname statement to the post by @DavidHD )

4. If the answer to the above is no, then go back to step 2. If it is yes proceed to the next step.

5.Please read this https://documentation.sas.com/doc/en/bicdc/9.4/bidsag/titlepage.htm If you name your database somebody will be able to provide a more relevant answer..

Re: Tables not shown up

SASKiwi — Mon, 07 Jun 2021 02:28:03 GMT

Please post the SAS log of your LIBNAME statement. Just saying it didn't work doesn't help us to see what might be going wrong.

Re: Tables not shown up

Analysts12 — Thu, 17 Jun 2021 13:55:59 GMT

I get the following error: when I submit via code

ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified
NOTE: Statements not processed because of errors noted above.

Re: Tables not shown up

Analysts12 — Thu, 17 Jun 2021 13:57:06 GMT

This is the error I receive via code:
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified

Re: Tables not shown up

Sajid01 — Thu, 17 Jun 2021 14:26:35 GMT

"I get the following error: when I submit via code
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified
NOTE: Statements not processed because of errors noted above."

There is something wrong with the way connections have been setup.
Have the odbc drivers installed ?
Please check the odbc.ini (on unix)/ODBC Administrator on windows.
I had mentioned this in my last post (item no 2).

Re: Tables not shown up

Analysts12 — Thu, 17 Jun 2021 14:29:00 GMT

odbc drivers are installed, as all other SQL libraries are running fine.

We got Active directory domain user Service account created for this new library and Database team updated details, now if we try to create library on Management console, tables are still not visible.

Re: Tables not shown up

Sajid01 — Thu, 17 Jun 2021 14:33:27 GMT

Then you must check the odbc.ini file.
Looks like you have Data Direct drivers. Please have a look in the documentation.

Prima facie SAS and the database are not able to communicate.
Typically it is the odbc configuration one has to look into.

Re: Tables not shown up

Sajid01 — Thu, 17 Jun 2021 14:45:42 GMT

Also please make sure you have updated the sasenv_local file with appropriate paths

Re: Tables not shown up

Analysts12 — Mon, 21 Jun 2021 08:10:03 GMT

Hi All,

so we checked the odbc.ini file and entry was no created for this SQL Library.
We updated all the details.
Still tables are not visible and we are receiving the same error : "ERROR: CLI error trying to establish connection: [SAS][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host Name
and Port Number."

Re: Tables not shown up

Sajid01 — Mon, 21 Jun 2021 09:16:54 GMT

As I pointed earlier sasenv_local also needs to be updated.
Once done I would restart SAS.