I'm using the latest U2 64-+Bit ODBC Driver Version 7.241.02.9004.
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?
Connect to U2 Universe DB as a Linked Server in Microsoft SQL Server
+1- Rocketeer
- 162 replies
I'm using the latest U2 64-+Bit ODBC Driver Version 7.241.02.9004.
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 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.
I'm using the latest U2 64-+Bit ODBC Driver Version 7.241.02.9004.
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 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?
+1I'm using the latest U2 64-+Bit ODBC Driver Version 7.241.02.9004.
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 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
I'm using the latest U2 64-+Bit ODBC Driver Version 7.241.02.9004.
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?
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.
I'm using the latest U2 64-+Bit ODBC Driver Version 7.241.02.9004.
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 found the tool at C:U2UniDKU2ODBC_64bitU2ODBC_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
I'm using the latest U2 64-+Bit ODBC Driver Version 7.241.02.9004.
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?
Karl's final post has back to front advice. "Level zero only" must be checked in the MSDASQL Provider, rather than cleared, for four part naming to work.
BTW The linked server can be written to if RPC Out is set to True in its Properties.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
