Apache NiFi 1.11.4连接MS SQL失败求助：无法执行SQL查询

Hey there, let's get your NiFi flow pulling data from MS SQL sorted out—your error points straight to a JDBC URL formatting issue, which is easy to fix. Here's a step-by-step breakdown:

1. Fix the Core Issue: Incorrect JDBC URL #

Your current connection URL jdbc:sqlserver://server_name=STI04\SQL2014;database=Sales uses invalid syntax for SQL Server's JDBC driver. The driver is interpreting server_name='STI04 as the actual hostname (hence the UnknownHostException), which doesn't exist.

Use one of these valid formats for your named instance:

• Option 1 (using instance name parameter):

  jdbc:sqlserver://STI04;instanceName=SQL2014;databaseName=Sales
  

• Option 2 (direct instance syntax):

  jdbc:sqlserver://STI04\SQL2014;databaseName=Sales
  

Both formats tell the driver to connect to the STI04 server and target the SQL2014 named instance, then use the Sales database.

2. Verify Connection Pool Configuration #

After updating the URL, confirm these settings in your DBCPConnectionPool are correct:

• Database Driver Class Name: com.microsoft.sqlserver.jdbc.SQLServerDriver (you already have this right)
• Database Driver Location: Ensure the path C:\Program Files\Microsoft JDBC DRIVER 8.4 for SQL Server\sqljdbc_8.4\enu\mssql-jdbc-8.4.1.jre8.jar is accessible to NiFi (no typos, and NiFi has read permissions for this file)
• Click the Test Connection button in the connection pool's config page to confirm the connection works before proceeding.

3. Alternative Processors to Pull SQL Data #

If QueryDatabaseTable still gives you trouble, try these reliable alternatives:

• ExecuteSQL: This processor is straightforward—just link it to your fixed DBCPConnectionPool, enter your query SELECT * FROM PassCount, and it will output the results as FlowFiles (exactly what you need for your queue). It's great for one-time full loads or ad-hoc queries.
• GenerateTableFetch: If you need to paginate large datasets (to avoid overwhelming NiFi or SQL), this processor splits the query into chunks and works with ExecuteSQL to fetch data incrementally.

4. Quick Double-Checks #

Even though you ruled out firewalls, confirm these to be safe:

• The SQL Server Browser Service is running on STI04 (required to resolve named instance ports)
• Your SQL Server instance allows remote connections, and the TCP/IP protocol is enabled (check in SQL Server Configuration Manager)

Once you fix the JDBC URL, your flow should start pulling data from the PassCount table into the NiFi queue without issues!

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