KDB与SQL Server ODBC连接异常：查询执行中途崩溃

Since your stored procedure runs fine in SSMS 2016 but crashes when called via KDB's ODBC interface, the issue is almost certainly tied to how the ODBC connection interacts with SQL Server 2016—not the procedure itself. Here are actionable steps to diagnose and fix this:

1. Verify ODBC Driver Compatibility #

SQL Server 2016 relies on newer ODBC drivers, while SQL Server 2012 might have worked with the older SQL Native Client driver. KDB could be using a driver that’s not fully compatible with 2016:

• Run odbc.drivers[] in your KDB session to check which driver is active.
• Switch to ODBC Driver 17 for SQL Server (or at least ODBC Driver 11) for your 2016 connection. Update your connection string explicitly, like this:

  conn: odbc.open "Driver={ODBC Driver 17 for SQL Server};Server=your2016server;Database=yourdb;UID=user;PWD=pass"
  

  Older drivers often struggle with TDS protocol changes in 2016, especially when handling large result sets.

2. Fetch Data in Batches Instead of All at Once #

odbc.eval tries to load the entire result set into memory immediately, which might hit a breaking point with 2016’s data handling. Use a cursor to stream results incrementally:

• Replace odbc.eval with odbc.cursor to pull chunks of data at a time. Example:

  c: odbc.cursor[conn; "EXEC your_stored_procedure"]
  // Fetch 100,000 rows per batch
  data: ([]); do[not c[0]; data,:c[100000]]
  odbc.close[c]
  

  This reduces immediate memory strain on both KDB and the SQL Server ODBC layer, which can prevent crashes.

3. Align ODBC Connection Settings with SSMS #

SSMS uses specific default SET options that your ODBC connection might not replicate, and SQL Server 2016 is stricter about option consistency:

• Run DBCC USEROPTIONS in SSMS while connected to 2016 to see active settings (e.g., SET ARITHABORT ON, SET ANSI_NULLS ON).
• Explicitly set these options in KDB before executing the procedure:

  odbc.eval[conn; "SET ARITHABORT ON; SET ANSI_NULLS ON; SET ANSI_WARNINGS ON;"]
  odbc.eval[conn; "EXEC your_stored_procedure"]
  

  Mismatched settings can cause unexpected failures in 2016, even if they worked in 2012.

4. Adjust SQL Server 2016 Configuration Limits #

Even with identical hardware, SQL Server 2016 might have default settings that restrict large result sets:

• Compare max worker threads and query wait settings between 2016 and 2012 using sp_configure:

  sp_configure 'show advanced options', 1; RECONFIGURE;
  sp_configure 'max worker threads';
  sp_configure 'query wait';
  

• Increase the ODBC timeout in KDB to match the 1-hour runtime you see in SSMS:

  odbc.set[conn; "QueryTimeout"; 3600] // Set timeout to 1 hour
  

5. Capture Crash Details for Deep Diagnosis #

If the above steps don’t resolve the issue, gather more context:

• Check KDB’s crash log (generated in the working directory on crash) for stack traces or error messages.
• Review SQL Server 2016’s error log (under Management > SQL Server Logs in SSMS) for connection failures or query errors around the crash time.
• Temporarily enable ODBC tracing (via ODBC Data Source Administrator) to capture the exact communication between KDB and SQL Server 2016—this can pinpoint where the connection breaks.

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