Command timeout from .net application

  • I get a timeout exception when running a query that takes almost 33 seconds on management studio. Even if I set command timeout to zero or a larger number. Tried executiontimeout on the config file. Set Connect timeout on the connection string to a larger number. I used to get results from my development machine but it fails when deployed. Now it fails on my development machine as well.

    It looks like the commandtimeout settings have no effect and are overpowered by the 30 seconds default.

    Please help!!!:-)

  • This .net application, is it making a Web Service call or is it a direct programmed connection string?

    Is there a firewall between the PC this app runs on and the server?

    Have you put a network sniffer on the app / PC to see what the problem is?

    Have you run PING and TraceRT from the PC the app runs on to see if it can connect to the SQL Server?

    EDIT: Have you checked the SQL Server remote query connection timeout setting?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • I have tried to optimize the underlying tables of my view as my stored proc is reading from the vie. One part of the sproc that retrieves few columns does respond correctly now. The other part does not.

    The connection is fine, just that the sproc takes about 32 seconds. I thought I would change the commandtimeout to be just more than this. The commandtimeout seems not to be working.

    Thanks for the prompt response.

  • You probably didn't see my edited comment.

    Check the Server / Instance's "remote query timeout" property. This can be found by right-clicking the server\instance name, navigating to Properties, the clicking on the Connections page. About 2/3rds of the way down is the "Allow remote connections" check box, and right underneath it is the query timeout value.

    This setting could be set very low and you not notice it in SSMS, but only in the .Net application. Make sure it's set properly (0 is not a good value to have, even though it means "no timeout", because it allows infinite loop-type queries endless connection to the server). Adjust it in increments to see if it helps your application.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks,

    It's 600, big enough for my query.

  • don't you have to set both the connection timeout and your command object's command timeout?

    this works fo rme for example, waitng 45 seconds to make sure it lasts longer thna the default 30 second timeout:

    'form a command that waits longer than 30 seconds for testing

    Dim sqlcmd As String = "WaitFor Delay '00:00:45';SELECT name FROM sys.tables;"

    Dim mySqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Connect Timeout=600;Application Name=GhostInTheMachine;"

    Dim MyConn As New SqlConnection

    MyConn.ConnectionString = String.Format(mySqlConnectionFormat, ".", "SandBox", "Noobie", "NotARealPassword")

    MyConn.Open()

    'now lets get a commadn object

    Dim mySqlCommand As New SqlCommand

    mySqlCommand.Connection = MyConn

    mySqlCommand.CommandTimeout = 600

    mySqlCommand.CommandType = CommandType.Text

    mySqlCommand.CommandText = sqlcmd

    Dim myDataReader As SqlDataReader

    myDataReader = mySqlCommand.ExecuteReader

    Dim MyDataTable As New DataTable

    MyDataTable.Load(myDataReader)

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks again for prompt responses.

    To Brandie: I think the remote connection timeout is used by the SSMS when connecting to other servers not the application. Please verify.

    I had all the settings correct, connect timeout, commandtimeout, and executiontimeout and debug set to false. I have it working now. The only thing I didn't mention to you all is that I was not using SQL Client. I was using Corelab Unidirect. This might be a bug or I'm using an old version.

    Tried SQL client and it's working like a charm.

    Thanks again for your contribution as it gave me direction. I will throw the Unidirect Library far away.:-P

  • thulani.moyana 79207 (4/12/2011)


    To Brandie: I think the remote connection timeout is used by the SSMS when connecting to other servers not the application. Please verify.

    So far as I know, a remote connection is a remote connection, no matter if it's coming from a server or an application.

    I'm glad you resolved your problem.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply