Rocket U2 | UniVerse & UniData

ROCKETEER Mike Rajkowski posted 11-15-2022 11:03

Karl,

I have more questions for you than answers, and suggest you contact your UniVerse Support Provider for further assistance.

That being said, here are my initial questions:
What version of UniVerse are you running?
What is the Flavor of the Account?  ( From TCL execute ".L RELLEVEL"
Have you tried with the XDEMO account?


Note that one thinYou could try unchecking "First Normal Form" in the U2 ODBC DNS setup options.

Karl Diethrick posted 11-15-2022 12:29

Thanks for the prompt reply @Mike Rajkowski.

I tried your suggestion regarding "First Normal Form" but didn't notice any changes in behavior, even after restarting my SQL Server and unlinking / relinking the server.​

When it comes to your other questions, this is my first time working with Rocket Software and their database platforms.  I am not familiar with UniVerse, nor do I have an account.  I only installed the ODBC drivers, as I have no experience using the TCL tool or the XDEMO account.  I only have experience with Oracle and SQL Server.  I've only dabbled in DB2 via ODBC drivers and was hoping I could do the same here with this UniVerse database.

I'm several steps removed from the UniVerse provider.  I am consulting with a customer that uses a client application.  The application provider hosts the UniVerse environment.  I am trying to provide some 3rd party reporting for them that is not available in their client application.  I was able to do this as a proof-of-concept using ODBC in Excel, Access, and even PowerBI.  However, I would like to be able to link in SQL Server as that is the preferred environment for my developers and tool set.

Are there any other ideas I can try without getting the software provider involved?

John Jenkins posted 11-15-2022 20:14

Karl,

I suggest you start by checking the user ID being used has access to the views using the U2 ODBC tester client tool to retrieve specific data from the same server and using the same DSN as used by SQL Server - re-checking the credentials used by SQL Server to connect match and using a FQDN user ID. If that is the case then turn on ODBC tracing client side and server side and look at the logs.

HINT - try using the HS.SALES account first as that is fully set up with known data dictionaries and open access permissions. If that works then you can focus on account and table specifics or  general connectivity issues as appropriate,

HINT - turn on ODBC logging both client side and server side and check the logs - they often show the cause of an issue.

If all else fails then the UniVerse license holder will need to open a Support call through their normal commercial support channel.

Regards

JJ

Karl Diethrick posted 11-16-2022 09:03

Thanks for the additional insight @John Jenkins. I may need to figure out how to enable that logging.

As it stands, the "Test" button in the ODBC Connection itself works.

Further, I don't think it's a permission issue because using the same ODBC DSN and credentials, I can connect with PowerBI and see all tables, columns, and data.

When connecting as a Linked Server in Microsoft SQL Server Management Studio, I can only see the list of table names, but can not see any columns or select any data.


​

Karl Diethrick posted 11-16-2022 13:21

I found the tool at C:\U2\UniDK\U2ODBC_64bit\U2ODBC_Tester and things worked well there, although I wasn't able to execute the  ".L RELLEVEL" that @Mike Rajkowski mentioned.  I was able to execute CALL Subroutine (*HS.OLEDBINFO) and see all the tables, and also was able to select * from tables of interest and see data.

Interestingly, when looking at the OLEDBINFO results, TABLE_CAT was blank, TABLE_SCHEM was populated with a value that wasn't showing in SQL Server via the OLE DB for ODBC connection, and all table names where shown with THIS.FORMAT with TABLE_TYPE = TABLE.

When using the "ODBC-Compliant SQL" portion of the U2ODBC Tester (64bit) tool, it's odd that I had to query as select * from THIS_TABLE to get results (using underscore rather than dot).

I then 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 then able to find posts on this in various places on the web, and the solutions offered there worked.

Although I still can't browse columns in the left pane of SSMS on the linked server, I am now able to query it and see columns and data in SSMS.

select * from [LINKEDSERVERVIAOLEDBFORODBC]...[THIS_TABLE];

Solution:
1. In SQL Server Management Studio, 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
​​