Sql-server – How to prevent or notice automatic connection retry in SQL Server and OLE DB

oledbsql server

I have a c++ application that connects to SQL Server 2005 using OLE DB / Native client. If the connection gets broken or for example the session is killed from the database something causes the connection to do an automatic reconnect. The problem is that in that case for example all temporary tables created previous connection are lost.

Is there any way to prevent this from happening, or somehow notice that this has happened? That way I could do the same steps as in the original connection.

I tried to add "ConnectRetryCount=0" and "Connect Retry Count=0" to connect string, which I found from here: https://msdn.microsoft.com/en-us/library/jj730312%28v=sql.110%29.aspx but that made no difference, even when I tested the same application native client 11 and SQL Server 2014.

Best Answer

You can check if the temporary object exists using this:

IF OBJECT_ID('tempdb..#sometablename') IS NULL
    CREATE TABLE #sometablename ...

Another thing you could do is check the connection time for the current connection:

SELECT connect_time
FROM sys.dm_exec_connections
WHERE session_id = @@SPID

However, I'm having a hard time believing that the client disconnects without throwing errors. I would try to catch those errors first.

Related Question