Connect to U2 Universe DB as a Linked Server in Microsoft SQL Server

Question

I'm using the latest U2 64-Bit ODBC Driver Version 7.241.02.9004 from Rocket Software, who took over the U2 Universe and UniData databases from IBM.

The ODBC Connection tests successfully and works perfectly in applications that use ODBC, such as Excel, Microsoft Access, and Microsoft Power BI Desktop.

I am running a freshly installed local instance of Microsoft SQL Server Developer (64-bit) 15.0.2000.5 on Windows 10 Pro locally.

I created a Linked Server connection using the Microsoft OLE DB Provider for ODBC Drivers and it connects to the U2 database successfully, listing out all of the table names.
However, I cannot see any columns, nor can I query any data from the U2 tables.

If I try scripting a select statement from a table, it says:
[MYU2DATABASENAME]...[MYTABLENAME] contains no columns that can be selected or the current user does not have permissions on that object.

Again, I can select all columns and data from these U2 Universe tables using the same credentials in other applications via ODBC.

I need to be able to query data via a SQL Server Linked Server connection. Is there a different provider that I need to use, or something I need to modify with my configuration?

I saw some other threads on this, but they were all quite old and the links no longer worked.

Answer 1

Hi @KarlDiethrick-6413,

Because of not familiar with ODBC and DB you said, it may be a matter of permissions

please check the values and permissions for the login that you are creating linked server from

Or you also can choose to pull the data via SSIS

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I played around a bit more and got SSMS to throw the error "A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema."

I was able to find posts on this in various places on the web, and the solutions offered there worked.

1. Right click on provider named MSDASQL and display properties.
2. Clear the checkbox next to "Level zero only"

Ref:
https://www.kodyaz.com/sql-server-2016/msdasql-provider-doesnot-expose-necessary-interfaces-to-use-catalog-schema.aspx
http://sparkalyn.com/2008/12/invalid-schema-error/
https://stackoverflow.com/questions/31968343/select-from-mysql-linked-server-using-sql-server-without-openquery
http://www.thesqlpost.com/2015/01/sql-server-error-invalid-use-of-schema.html