Netezza数据导入MS SQL Server：ODBC连接字符串示例求助

Hey there, let's tackle that ODBC connection string issue you're facing while moving data from Netezza to MS SQL Server. Below are concrete, ready-to-use examples for both systems, plus some key tips to get past this stuck point.

Netezza ODBC Connection String Examples #

First, you'll need a valid connection string to pull data from your Netezza source. The exact driver name depends on the version you've installed—double-check it in your system's ODBC Data Source Administrator if you're unsure.

• Basic Username/Password Authentication
  This is the most common setup for Netezza:

  Driver={IBM Netezza ODBC Driver};Server=your_netezza_host_or_ip;Port=5480;Database=your_target_netezza_db;UID=your_netezza_username;PWD=your_netezza_password;
  

  Breakdown of parameters:

  • Server: Your Netezza server's hostname or public IP address
  • Port: Default is 5480 (adjust if your admin changed it)
  • Database: The specific Netezza database containing your data
  • UID/PWD: Your Netezza login credentials

• Read-Only Connection (Optional)
  If you only need to pull data (no writes to Netezza), add the ReadOnly parameter to lock down the connection:

  Driver={IBM Netezza ODBC Driver};Server=your_netezza_host_or_ip;Port=5480;Database=your_target_netezza_db;UID=your_netezza_username;PWD=your_netezza_password;ReadOnly=1;
  

MS SQL Server ODBC Connection String Examples #

Next, you'll need a connection string to push data to your MS SQL Server target. There are two common driver options—use the newer one if possible.

• ODBC Driver 17 for SQL Server (Recommended)
  This is the latest, most supported driver for SQL Server:

  Driver={ODBC Driver 17 for SQL Server};Server=your_sqlserver_host\instance_name;Database=your_target_sql_db;UID=your_sql_username;PWD=your_sql_password;
  

  For Windows Authentication (using your logged-in Windows account), replace UID/PWD with Trusted_Connection=Yes:

  Driver={ODBC Driver 17 for SQL Server};Server=your_sqlserver_host\instance_name;Database=your_target_sql_db;Trusted_Connection=Yes;
  

  Note: If using the default SQL Server instance, you can omit \instance_name from the Server parameter.

• SQL Server Native Client 11.0 (Legacy)
  If you're working with an older environment that requires this driver:

  Driver={SQL Server Native Client 11.0};Server=your_sqlserver_host\instance_name;Database=your_target_sql_db;UID=your_sql_username;PWD=your_sql_password;
  

Key Troubleshooting Tips #

• Match the Driver Name Exactly: The driver name in your connection string must match what's installed on your machine. Open ODBC Data Source Administrator (64-bit or 32-bit, depending on your tool) to confirm the exact driver name (e.g., IBM Netezza ODBC Driver 7.1 instead of just IBM Netezza ODBC Driver).
• Test First with DSN: Create a test DSN in ODBC Data Source Administrator for both Netezza and SQL Server. Once the DSN connects successfully, you can extract the parameters to build your connection string.
• Check Firewall Rules: Ensure your machine can reach both Netezza (port 5480) and SQL Server (default port 1433) over the network.

内容的提问来源于stack exchange，提问作者ayca_bayraktar