.net – Reading Netezza database table from C# via ODBC not working in Windows 7

64-bitnetnetezzaodbcregistry

Why can't .NET connect to my Netezza box via the installed {NetezzaSQL} driver? 64bit applications also cannot connect via this ODBC connection. Why would that be? I've built both user and system Netezza ODBC connections in Control Panel, and both work fine when I click "Test Connection"? I see the value in the registry, but when I traverse the registry drivers, .NET does not see "NetezzaSQL". According to Netezza, they don't have a 64 bit ODBC driver. The driver they provide should work for 32 and 64 bit applications. Could this be a permissions issue perhaps with Windows 7?

static void CreateNetezzaTableObjectFolders()
{
    //string activeDir = @"C:\Source\EDW\dw-objects\trunk";

    OdbcConnection conn = new OdbcConnection();
    conn.ConnectionString = "Driver={NetezzaSQL};servername=10.1.170.18;port=5480;database=DEV_SANDBOX; username=mac;password=secret;";

    OdbcDataReader rdr = null;

    try
    {
        conn.Open();
        System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("SELECT OBJECT_NAME FROM QA_ETL..STG_OBJECTS", conn);
        rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine(rdr[0]);
            // Create Folder
            //string objectName = rdr[0].ToString();
            //string newPath = System.IO.Path.Combine(activeDir, objectName);
            //System.IO.Directory.CreateDirectory(newPath);
        }
    }
    finally
    {
        // close the reader
        if (rdr != null)
        {
            rdr.Close();
        }

        // close the connection
        if (conn != null)
        {
            conn.Close();
        }
    }
}

Open in new tab to see Registry Settings for ODBC and zoom in:

enter image description here

============================

7/13/2012 4:56pm update:

Apparently, the driver name goes inside of the curly brackets. When I get a list of drivers programmatically, I don't see the driver. How do I add to this list in the Windows user interface? I do however, see the installed Netezza driver under the Control Panel > Administrative Tools > ODBC Drivers.

Here is some code that verifies what I am explaining:

public static void GetSystemDriverList()
{
    //List<string> names = new List<string>();
    // get system dsn's
    Microsoft.Win32.RegistryKey reg = (Microsoft.Win32.Registry.LocalMachine).OpenSubKey("Software");
    if (reg != null)
    {
        reg = reg.OpenSubKey("ODBC");
        if (reg != null)
        {
            reg = reg.OpenSubKey("ODBCINST.INI");
            if (reg != null)
            {

                reg = reg.OpenSubKey("ODBC Drivers");
                if (reg != null)
                {
                    // Get all DSN entries defined in DSN_LOC_IN_REGISTRY.
                    foreach (string sName in reg.GetValueNames())
                    {
                        Console.WriteLine(sName);
                        //names.Add(sName);
                    }
                }
                try
                {
                    reg.Close();
                }
                catch { /* ignore this exception if we couldn't close */ }
            }
        }
    }

    Console.ReadLine();
}

enter image description here

Here is the connection error I get from Microsoft Access 2007 from the Windows 7 box when I use the user ODBC connection. The system one was not visible.

Error: "ODBC–call failed. [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application (#0)"

enter image description here

Best Answer

Microsoft never renamed their "system32" folder, so system32 really has all of the 64 bit drivers. And SysWow64 folder has all of the 32 bit drivers. By default, a .NET project created in Visual Studio 2010 will have a "Target Platform" of x86. I just changed it to x64 and my connection worked to get my 64 bit NetezzaSQL driver working for .NET.

Just realize that you can launch the ODBC (Data Sources) dialog under Control Panel > Administrative Tools by running the "odbcad32.exe" file in either the C:\Windows\system32\ folder (this is the 64 bit version of the dialog to build 64 bit drivers), or in the C:\Windows\SysWow64\ folder (this is the 32 bit/x86 version of the dialog to build 32 bit drivers). Yes, the folders are reversed because Microsoft is weird that way.

Furthermore, there are two different folders in the Registry that get built. Turns out that the list above (in my black screen shot) comes from the Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ list because those are 32 bit drivers. The dll I installed, "nzodbcsetup.exe", apparently installed as a 64 bit driver. I am not exactly sure how to install this as a 32 bit driver on 64 bit windows, so that's why I changed the "Target Platform" in my .NET project from the default x86 to x64. There has to be a way to install it as a 32 bit driver as well because when I installed this on a 32 bit Windows XP box, the driver worked fine.

What's weird is that Netezza's OLE drivers are separated. They have a 32 bit and a 64 bit dll exe file (for version 6.0.3). "nzoledbsetup.exe" (~82MB x86) and "nzoledbsetup64.exe" (~102MB x64). But the ODBC just has one, which is apparently both. I tried manually building a 32 bit driver in the SysWOW6432Node (under run > regedit) and pointed to the Driver file and Setup file (in the system32 folder, but I got an error. Please comment is there a way in Windows to automatically install as 32 bit, not 64.

Related Topic