Has anyone successfully connected to a Universe database as a “linked server” in Microsoft SQL Server 2012 and able to query Universe files?
I am having problem query Universe files through linked server.
Here are the systems involved and what I have done.
Universe 11.2.5 running on Red Hat SELLinux.
Microsoft SQL Server 2012 running on Windows Server 2008 R2.
U2 ODBC driver (both 32-bit and 64-bit) from Client Tools 5.1.0 installed on Windows Server 2008 R1.
I have setup a Universe account, ran HS.ADMIN utility and configured the appropriate HS_FILE_ACCESS in the account to expose a few Universe files (not tables) to ODBC client. Universe files I would like to query through SQL linked server all have “READ” permission in HS_FILE_ACCESS and also have the appropriate @SELECT dictionary setup.
U2 ODBC drivers (both 32-bit and 64-bit) are installed on Windows Server 2008 R1.
uci.config files for both 32-bit and 64-bit U2 ODBC drivers have been configured to use the Universe account I setup.
ODBC data sources have been setup for both 32-bit and 64-bit ODBC DSN in Windows Server 2008 R2.
Test connections passed for both DSNs I setup.
I also ran the U2ODBC_Tester installed by U2 ODBC drivers and confirmed both 32-bit and 64-bit U2 ODBC driver can connect to the Universe account successfully.
Finally, I execute a few SQL queries in U2ODBC_Tester and both 32-bit and 64-bit ODBC DSN work properly.
Now onto the linked server setup.
I have no problem setting up the DSN for U2 ODBC as a linked server in Microsoft SQL Server 2012. The linked server I setup is called “UNIVERSE”
In SQL server Management Studio, I can see Universe files listed under the following nodes:
UNIVERSE -> Catalogs -> “” -> Tables
One of the node has “blank” name.
I don’t know why one of the node has “blank” name but I could not figure out the proper object reference to query any tables.
Does anyone know what I need to do to in order to query Universe files through SQL linked server ?
Any information would be greatly appreciated.