Detecting and closing opened ODBC connections

Question

Hi,

I'd like to know if it is possible to detect and close an opened ODBC connection: a my connection doesn't function more and I want to detect possible pending connections.

Thanks

Answer 1

Where do you want to detect it? In SQL Server or elsewhere?

To find sessions that are connected with ODBC, you can use this query:

SELECT session_id 
FROM   sys.dm_exec_sessions 
WHERE  client_interface_name = 'ODBC'
  AND  program_name NOT LIKE 'SQLAgent%'

I've added an extra filter, since I don't think you want to kill Agent jobs.

I suspect that you will need further refinement, since killing sessions only based their client API sounds dubious.

Answer 2

Hi @pmscorca

You can use the system views like sys.dm_exec_sessions and sys.dm_exec_connections to query information about active sessions and connections. Sample like this:

SELECT session_id 
FROM sys.dm_exec_sessions 
WHERE client_interface_name LIKE '%ODBC%' 
  AND is_user_process = 1 
  AND session_id <> @@SPID;

If you need to auto detect and close these ODBC connections, you can write a script and create SQL Server Agent job to periodically check idle sessions.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".