cannot get commandtimeout or connecttimeout to fire timeout exception

[vegeta4ss]

Both of these properties have a default value of 30 seconds but I am setting them to something much shorter (8) and my application does not seem to recognize the timeout. Even if I allow the default value of 30 seconds to elapse I still am waiting on the timeout.

I am trying to use the connecttimeout to notify my client application that the remote sql server is not available before I try and execute a sql transaction.

I am not sure why this is not working though, the sqlexception for timing out has worked for me before and all I did was set a non-default property. The only change here is that this is a MDI type of project, which shouldn't matter.

Here's my code:

Public Function sqlSelect(ByVal query As String, ByVal outputtype As SelectType, Optional ByVal resultsset_name As String = "results", Optional ByVal commandtype As CommandType = CommandType.Text, Optional ByVal parameters As List(Of SqlParameter) = Nothing) As Object
        Dim cmd As SqlCommand = Nothing

        Try
            cmd = GetSQLCommand(query, commandtype, parameters) 
'command will already have parameters attached and timeout set to 8 here
        Catch sql As SqlException
            MessageBox.Show("A sql error was caught during execution")
        Catch ex As Exception
            exHandler(ex)
        End Try

        Debug.Assert(cmd.CommandTimeout = 8)

        Dim outputdata As Object = Nothing

        Select Case outputtype
            Case SelectType.Sqldatareader
                outputdata = CType(outputdata, SqlDataReader)
                outputdata = FillDataReader(cmd)
            Case SelectType.datatable
                outputdata = CType(outputdata, DataTable)
                outputdata = FillDataTable(cmd)
            Case SelectType.dataset
                outputdata = CType(outputdata, DataSet)
                outputdata = FillDataSet(cmd)
            Case SelectType.xml
                'todo: future expansion
        End Select
        Return outputdata
    End Function

 Private Function FillDataTable(ByVal objcmd As SqlCommand, Optional ByVal strtable As String = "results") As DataTable
        Try
            Dim da As New SqlDataAdapter(objcmd)
            Dim dt As New DataTable(strtable)
            da.Fill(dt)  '<-- this line causes application deadlock
            Return dt
        Catch sql_ex As SqlException
            Throw sql_ex
        Catch ex As Exception
            Throw ex
        End Try
    End Function

Private Function GetSQLCommand(ByVal qrytext As String, Optional ByVal commandtype As CommandType = CommandType.Text, Optional ByVal paramscollection As List(Of SqlParameter) = Nothing) As SqlCommand
        Try
            Dim objcmd As SqlCommand = New SqlCommand(qrytext, Me.Connection())
            objcmd.CommandType = commandtype
            objcmd.CommandTimeout = 8
            If paramscollection Is Nothing Then
                Return objcmd
            Else
                If paramscollection.Count > 0 Then
                    For Each param As SqlParameter In paramscollection
                        objcmd.Parameters.Add(param)
                    Next
                End If
                Return objcmd
            End If
        Catch sqlex As SqlClient.SqlException
            'exHandler(sqlex)
            Throw sqlex
        Catch ex As Exception
            exHandler(ex)
            Throw ex
        End Try
    End Function

[vegeta4ss]

My Test Scenario:
1. unplug remote sql machine from network
2. start client app
3. initialize sql class in client app main form_load sub
4. make a simple sql call and expect a timeout because remote machine unhooked....but I never get one

However,

I can get a sqlexception IF I start my app with the remote sql server still up on the network and use a sleep timer to halt my command execution.

I'm not sure what I'm doing wrong here, is this the incorrect way to use the timeout stuff? It doesn't make sense that I would have to make some type of remote endpoint socket to determine if my sql box is up.

This application is client based, but it uses a ton of server side calls because there is no "client database". Multiple users need to be able to update the same database. I have already figured out that from a "client perspective" I can handle network connectivity issues by using the applicationevents class and the networkavailabilitychanged handler. But, from the server perspective I can't seem to figure this one out. If the timeout is not the way to go about this, what else can I do?

[vegeta4ss]

I adjusted the "remote connection timeout" down to 5 seconds on the Sql Server box and same deadlock happening in application.

No clue why I am getting behavior like this? It's as if when it doesn't detect an endpoint server it holds all timeout notifications.

[techgnome]

"I am trying to use the connecttimeout to notify my client application that the remote sql server is not available before I try and execute a sql transaction." << ummm..... okaaay.... and just how are you doing that? Well, first let me ask this: You know there is a ConnectionTimeOut AND A CommandTimeOut and they are TWO DIFFERENT things, in two different objects? And they are not interchangable.

I don't see a ConnectionTimeout any where in the code, only the CommandTimeOut. As it is, you've given SQL Server 8 seconds to execute the command... at 9 seconds, it should result in a timeout error... but you have to execute the command...

As for the deadlock.... you'll need to start looking at profiler and see what's going on on the server end.... sounds like some one has a record locked that you are trying to access... sometimes a thread will get lose and not release it's lock on something, even though the client disconnected, doesn't mean the server end of the connection stopped...

-tg

[vegeta4ss]

"I am trying to use the connecttimeout to notify my client application that the remote sql server is not available before I try and execute a sql transaction." << ummm..... okaaay.... and just how are you doing that? Well, first let me ask this: You know there is a ConnectionTimeOut AND A CommandTimeOut and they are TWO DIFFERENT things, in two different objects? And they are not interchangable.
Yes I realize they are different. The connecttimeout is attached to my class level sqlconnection (me.connection) which is passed in to the sqlcommand upon construction in the getsqlcommand sub. The value for connecttimeout is set when I build the connectionstring at startup and then passed into me.connection when it is constructed.
I don't see a ConnectionTimeout any where in the code, only the CommandTimeOut. As it is, you've given SQL Server 8 seconds to execute the command... at 9 seconds, it should result in a timeout error... but you have to execute the command...
it never throws the error, just deadlocks the application. The command gets attached to the dataadapter in my example here, I have no idea if the command actually gets executed.
As for the deadlock.... you'll need to start looking at profiler and see what's going on on the server end.... sounds like some one has a record locked that you are trying to access... sometimes a thread will get lose and not release it's lock on something, even though the client disconnected, doesn't mean the server end of the connection stopped...
i'm the only one using the other machine so if anyone locks it, it's me. The deadlock result is the same despite what type of sql commands I try. Even sql system commands like sp_helpdb get the same error.
-tg

thanks for the help. this issue is about to drive me nuts.

[vegeta4ss]

ok well I have written a ping routine with a 1 second timeout. I can put that on another thread and have it set some global sqlstatus variable as a workaround.

This seems EXTRA unweildy but if the timeout stuff does not work like I thought it did then this is my only chance to achieve the functionality I need.

[techgnome]

well, typically, you open a connection, perform your action, close the connection and move on.... it sounds like you are trying to open the connection once and hold onto it for the life of the app.... which might be why you are getting the locks on the database too.... There's nothing wrong with creating a connection object and re-using it, but before each time you use, make sure it hasn't been closed on you (check the .state property) ... if it has, then you'll need to re-open it, and when you are done, don't forget to close it.

-tg

[vegeta4ss]

well, typically, you open a connection, perform your action, close the connection and move on.... it sounds like you are trying to open the connection once and hold onto it for the life of the app.... which might be why you are getting the locks on the database too.... There's nothing wrong with creating a connection object and re-using it, but before each time you use, make sure it hasn't been closed on you (check the .state property) ... if it has, then you'll need to re-open it, and when you are done, don't forget to close it.

-tg

maybe I should post my whole source for this so you can understand what I am trying to do.

I share the connection, yes this is true. However I manage the opening and closing with the Using constructs. Since this is a common DAL class all connections go through the same class level connection, each of them uses that to build their own sql command, then that sql command is executed. Then the command object is disposed.

The database is not getting locks. It's simply taking forever to time out. I just sat here and waited, it took 7-8 minutes to FINALLY throw a sqlException at me.

Yesterday I set the remote sql server's timeout value to something low and did not see this behavior so I set it back to 600 seconds. I need to try again and see if this is what is affecting my program.

[vegeta4ss]

ok, well I adjusted the remote sql server's remote connection query timeout value and it did nothing different for me.

Since that machine if off of the network there is no way for it to know the value for the timeout, so that has me looking at the client side of things again. But what has me confused is that the default values for those timeouts are 30 seconds, so where is this 7 minutes coming from?

I disabled my firewall to make sure that was not a part of the issue.

The specific sql error I get is error #53. "A network-related or instance-specific issue occured while establishing a connection to sql server...."

here's the stack trace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at SplashScreen.sqlstuff.FillDataTable(SqlCommand objcmd, String strtable)

[vegeta4ss]

I might have stumbled upon something to explain some of this behavior.

The waiting game that my code is trying to play is also happening if I use SSMS directly to try to log into the server while it is unhooked from the LAN.

The same behavior is verified by another machine able to access this sql server.

Where do I adjust this?

[techgnome]

hmmm.... I don't think the timeout is the problem.... looks like there might be latency issues with the network itself. ....

"that machine [is] off of the network " << - does that mean you are trying to connect to a server via TCP/IP out there on the internet? If so, that may be the source of your "extra" time... I don't think the timeout clock starts until the server actually gets the command and starts to execute it.... but I'm not sure... never had this much of a problem.

-tg

[techgnome]

you replied while I was doing same.... try right-clicking the server in the object explorer and selecting properties.... poke around in there....


-tg

[vegeta4ss]

so it seems that no matter what value I put into the connection timeout textbox on my "connect to server" form in Management Studio the behavior stays the same. This is really frustrating and I have no idea how to track down why it is not working correctly.

There is too many different places to set a timeout value, I don't even think sql server knows which number to use . I've changed every single one of them, restarted the MSSQLSERVER service and tested.

It should not take multiple minutes to return to you an error 53. This is something I guess I will have to post to a sql server forum. If I make some progress I will update this thread with my findings.

[techgnome]

sorry I couldn't be of more help...
You may want to try your luck in the MSDN forums, or at www.dbfourms.com
I think there is also a dedicated sqlserver forums somewhere too.

-tg

[vegeta4ss]

sorry I couldn't be of more help...
You may want to try your luck in the MSDN forums, or at www.dbfourms.com
I think there is also a dedicated sqlserver forums somewhere too.

-tg

even though we didn't resolve the problem, you still helped a lot . I added to your rep.

I posted my question to http://social.msdn.microsoft.com/For...access/threads, i'll cross post to dbforums too, never been there but it looks fairly active which is a good sign.

I read on sql protocols' blog a post by Ming Xu that said something along the lines of "remote connections rely on you having a solid network connection". So I would like to know whether I am responsible to keeping an eye on those ntwk resources myself, or if the functionality to detect network level failures in connecting exists out of the box within some class/library? Having a definitive answer there would help me decide whether i'm chasing a wild goose or not.

Eventually I gotta figure this one out because we severely need a shared Data Access class. If I bring a "hacky" one back for the other guys in my dept they'll never use it.