JDBC Data Source for Format SQLServer Returns "Did not get a response from the MID server" and Mid Server Log Shows "Socket closed" - Support and Troubleshooting

JDBC Data Source for Format SQLServer Returns "Did not get a response from the MID server" and Mid Server Log Shows "Socket closed" Issue You have a JDBC Data Source with Format set to SQLServer. When a "Load All Records" or "TEST Load 20 Records" is attempted it fails with:

"Did not get a response from the MID server"

The mid server agent log will also show the error "Socket closed" as in this example:

06/24/21 10:41:15 (529) Worker-Standard:JDBCProbe-873f48801bc57454864d2173604bcb87 Worker starting: JDBCProbe source: 4e730ffedbfe83009fe1110b0b96194b 06/24/21 10:41:15 (732) Worker-Standard:JDBCProbe-873f48801bc57454864d2173604bcb87 Waiting for connection semaphore 06/24/21 10:41:15 (732) Worker-Standard:JDBCProbe-873f48801bc57454864d2173604bcb87 Obtained connection semaphore 06/24/21 10:41:15 (764) Worker-Standard:JDBCProbe-873f48801bc57454864d2173604bcb87 SELECT * FROM myTable

06/24/21 10:46:21 (295) Worker-Standard:JDBCProbe-873f48801bc57454864d2173604bcb87 SEVERE *** ERROR *** Socket closed com.microsoft.sqlserver.jdbc.SQLServerException: Socket closed at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2924) at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2031) at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6418) at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7581) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$CursorFetchCommand.doExecute(SQLServerResultSet.java:5459) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.doServerFetch(SQLServerResultSet.java:5496) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1038) at com.service_now.monitor.jdbc.JDBCRowSet.next(JDBCRowSet.java:158) at com.service_now.monitor.jdbc.JDBCMultiRowSet.next(JDBCMultiRowSet.java:19) at com.service_now.mid.probe.JDBCProbe.doSelect(JDBCProbe.java:335) at com.service_now.mid.probe.JDBCProbe.doQuery(JDBCProbe.java:199) at com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:126) at com.service_now.mid.probe.AProbe.process(AProbe.java:102) at com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:122) at com.service_now.mid.queue_worker.AWorkerThread.run(AWorkerThread.java:20) at com.service_now.mid.threadpool.ResourceUserQueue$RunnableProxy.run(ResourceUserQueue.java:647) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: java.net.SocketException: Socket closed at java.base/java.net.SocketInputStream.socketRead0(Native Method) at java.base/java.net.SocketInputStream.socketRead(SocketInputStream.java:115) at java.base/java.net.SocketInputStream.read(SocketInputStream.java:168) at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140) at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2023) ... 20 more

06/24/21 10:46:21 (295) Worker-Standard:JDBCProbe-873f48801bc57454864d2173604bcb87 Enqueuing: C:\SN\SNI\agent\work\monitors\ECCSender\output_s\ecc_queue.17a3f213eef0000001.xml 06/24/21 10:46:21 (327) Worker-Standard:JDBCProbe-873f48801bc57454864d2173604bcb87 Worker completed: JDBCProbe source: 4e730ffedbfe83009fe1110b0b96194b time: 0:05:05.766

Release Applies to any release.

Cause The sys_data_source table's Business Rule "Build Connection URL" sets the data source's "Connection URL" by calling script include "ConnectionURL". That script include adds the following to the data source's Connection URL for SQLServer Formats:

this.connection_url += ";selectMethod=cursor;databaseName=" + databaseName;

In this particular version of SQLServer the parameter "selectMethod=cursor" caused the database to not return any data and resulted in the errors above.

Resolution Modify the data source's "Connection URL" by removing:

selectMethod=cursor

For example changing it from this:

jdbc:sqlserver://MyServer:1433;selectMethod=cursor;databaseName=MyDatabase

To this:

jdbc:sqlserver://MyServer:1433;databaseName=MyDatabase

And try the load again.

Note: You may consider changing the script include "ConnectionURL" so that the selectMethod=cursor is not added back to the "Connection URL" when the data source is updated again in the future as the Business Rule "Build Connection URL" is triggered for Updates.