Connection timeout in ODBC

Question

I know that I can set the connection timeout for MS SQL Server when using ADO or OLE DB, by adding Connect Timeout=somevalue to the connection string.

This doesn't seem to work for ODBC connections though. Specifically, we made the decision to use the standard SQL Server ODBC driver that ships with Windows XP (SQLSRV32.DLL version 2000.85.1132.00) instead of the newer SQL Native Client and it is with this driver that the connection always times out on 15 seconds, irrespective of the Connect Timeout setting.

Also, when it times out, I get two errors. The first is the timeout and the second is "Invalid connection string attribute" so I guess Connect Timeout is not what I need. The final clue that I'm missing the plot, came from this somewhat dated article: http://msdn.microsoft.com/en-us/library/ms811006.aspx

Does anyone know if there is a way to specifiy the connection timeout in the connection string for the standard SQL Server ODBC driver?

Solution 1

You can set the connection timeout by setting the SQL_ATTR_CONNECTION_TIMEOUT connection attribute with SQLSetConnectAttr.

You can set the statement query timeout by setting the SQL_ATTR_QUERY_TIMEOUT statement attribute with SQLSetStmtAttr().

Solution 2

I had the same question. Actually, just the moment I'm writing this, I checked the SQLSetConnectAttr and was surprisedly reading the first sentence

"The SQL Server Native Client ODBC driver ignores the setting of SQL_ATTR_CONNECTION_TIMEOUT."