Products Resources Support About Us

Need help query Universe database via a linked server in SQL Server 2012


#1

Hi all.

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.

Thanks

Jason


#2

On UniVerse, we support the account with or without schema. For example, the default HS.SALES account does not have any schema name. It is a blank node name on SQL Server linked server, but it is fine with OpenQuery statement.

Here is an OpenQuery example against HS.SALES account.
select * from openquery(“UNIVERSE”,‘SELECT * FROM STATES’);


#3

Hi Paul,
I tried several SELECT queries based on the OPENQUERY example you provided but my query all errored out with the following message:

Object reference not set to an instance of an object.

I even re-configured my DSN to use HS.SALES account, re-add linked server and execute the exact OPENQUERY you have and I still got the same “object reference” error.

Do you know what else I can try?

Thank you


#4

I assume you are working on 64-bit SQL Server 2012 and 64-bit U2 ODBC SYSTEM DSN with default options against HS.SALES account. The system dsn name is called XYZ.

Here is the Linked Server Properties - UNIVERSE.
Linked Server: UNIVERSE
Provider: Microsoft OLE DB Provider for ODBCDrivers
Product name: UNIVERSE
Data Source: XYZ
Location: HS.SALES

Server Options: Set RPC and RPC Out to True.
Security: Remote login with password.

You should see the STATES table name on UNIVERSE\Catalogs""\Tables\System Tables list.


#5

Hi Paul,
Yes, I’m using 64-bit SQL Server 2012 and installed 64-bit U2 ODBC driver.

I reconfigured everything (DSN and linked server with RPC & RPC Out set to true) based on your suggestion but I still get “Object reference not set to an instance of an object” error message.

I’m pretty sure ODBC connection works because I can execute SQL query in U2ODBC_Tester64.exe successfully.

If you have any other suggestions on what I can try, please let me know.

Thank you
Jason


#6

Hi Paul,
I tried the same query again today and it worked; I was able to query STATES file from HS.SALES account.

I didn’t change any ODBC or DNS configuration. The only thing happened was the test Windows server rebooted after Windows Update last night. Now I recall I did not reboot the server after U2 ODBC drivers were installed because the driver installation didn’t require server reboot. Perhaps a server reboot after ODBC driver installation was all I need to fix the problem. (Or may be restarting SQL server service without server reboot?).

Nevertheless, I will need to do more testing to make sure accessing Universe data files via linked server is reliable and the success I have just now is not a fluke.

Thank you for your assistance.
Jason


#7

Hi Paul,

Do you know how to limit the number of rows returned from the query?

Universe SQL supports a “SAMPLE n” syntax (similar to LIMIT n syntax of MySQL) but it doesn’t work when I use that syntax in my OPENQUERY.

SELECT * FROM OPENQUERY(MY_UV_LINKED_SERVER, ‘SELECT * FROM MY_TABLE SAMPLE 10’)

I also tried “TOP (n)” syntax and obviously it didn’t work because only Microsoft’s own SQL uses the "TOP n) syntax:

SELECT * FROM OPENQUERY(MY_UV_LINKED_SERVER, ‘SELECT TOP (10) * FROM MY_TABLE SAMPLE 10’)

I could do SELECT TOP (10) * FROM OPENQUERY but that will be very slow because it needs to wait for OPENQUERY to return all rows from the Universe file first.

Any suggestion on how to limit number of rows would be appreciated.

Thank you
Jason