Chris Miller Blog

This all applies to SQL Server 2008 btw.

You may run into a whole bunch of problems trying to get OLE DB drivers to work with a Netezza NPS for the purposes of linked servers.  I'm going to run through my morning's work here to save you some trouble down the line, because it's (as all OLE DB and ODBC interactions are) poorly documented, badly worded, and doesn't configure properly on its own.

I'm about to say some things that look extremely critical of Netezza's support website.  Netezza as a company has a really great, well designed and well engineered product that does some incredible things.  Netezza's support people are friendly, helpful, efficient, and fast.  Netezza's support website and knowledge base are horrible, with a search function that bounces between "doesn't work" and "doesn't return results".  Meaning that if you want help, don't go to their support website and look anything up first, just create a support incident and get a person to help you.  It costs them more money, and maybe, eventually, they'll upgrade their website from "sucks" to "doesn't suck".

First, you get the OLE DB drivers from Netezza.  If you have a support contract with them, this is an ftp away.  If you're not sure where to look, for the sake of your sanity don't go searching their KB, just create a support incident and let them tell you where it is. 

Second, install the drivers.  Nice, friendly, helpful install program.  Yay.

Third, reboot your server.

Fourth, go into the workbench and open Server Objects, Linked Servers, and Providers.  Right-click on the NZOLEDB provider and choose Properties.  Turn on the "Allow Inprocess" option.  No, this is neither as secure nor as crash resistent as not turning it on.  However, working is better than not working, and it doesn't work if it's not in process.

Fifth, and this is the important thing:  Restart the SQL Server service.  There's no notification that you need to do this after you change the In Process setting.  The old Microsoft axiom, however, applies: When in doubt, restart. 

Then set up the linked server.  Pretty obvious.  As part of the Classic Microsoft User Experience, the "Product Name" field is required, but you can put anything in there and it'll work, but it can't be blank.  Sigh.  You must set up security also, there's no blanket security provision like there is with an ODBC connection.  Go to the security tab and set something up there.  If all else fails use the bottom option and hardcode everyone to use the same username/password, if you're really lazy and don't care about things like security and security auditing.  In other words, don't do that.

So now you've got a linked server.  Mine, currently, only works with OPENQUERY() and always returns errors with 4-part names.  Sigh.  Baby steps.

Enjoy.

Oh, and there's a bug with Netezza's ODBC drivers, sometimes they wait for like 20 minutes to return a result set.  Good luck with that.  It's why I got started down the OLE DB path.