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:
============================
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();
}
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)"