topic Re: Unable to Connect to Database in SQL - can connect to other DBs on same server in Connectivity & Data Prep

Unable to Connect to Database in SQL - can connect to other DBs on same server

rbpeterson2 — Tue, 21 Dec 2021 16:57:26 GMT

Unusual situation; we have a single user that we are using against two databases within the same server. The user is granted the exact same permissions both on the Server level and the Database level:
User associated to DBs

Doing a simple Select * load on a table in each database, the one database gives a non-descriptive generic error:

Failed Load

... while the other successfully completes.

Successful Load

The Load script simple states the library connection (not changed between the two loads); along with

Select * From <databasename>.dbo.<tablename> ;

On the server, the ODBC connection has been created and tested; user can connect using SSMS without error.

In looking at the QvOdbcConnectorPackage log file, there are a couple of items that stand out, but given that the connection works with one DB but not another doesn't jive with the warning message in the log. The parsed log file is attached.

Re: Unable to Connect to Database in SQL - can connect to other DBs on same server

Anil_Babu_Samineni — Mon, 04 Nov 2019 07:11:37 GMT

1. Check in the discovery directory (I meant, use direct script which is equal to QvOdbcConnectorPackage) with in the same error where you face problem

2. Check if the connection name is same as per SQL Statement?

Re: Unable to Connect to Database in SQL - can connect to other DBs on same server

rbpeterson2 — Tue, 05 Nov 2019 18:29:16 GMT

Thanks for responding Anil but not following what you are suggesting in #1. For the SQL connection, it is the exact same connection but to two different databases.

Re: Unable to Connect to Database in SQL - can connect to other DBs on same server

Anil_Babu_Samineni — Wed, 06 Nov 2019 09:58:04 GMT

Try to use one by one and then see to test? It is actually odd to explain what is there with you. Have you said that there is only one query which is SELECT * FROM ...

Hence, It shouldn't be a case ..