Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Location
    Athens, Greece
    Posts
    2

    Angry Unanswered: How to change Query Timeout?

    Greetings

    I have encountered the following problem:

    I currently develop an application for my company that actually uses rather long queries, with many records.

    I have a particular query (Written using SQL string inside the .NET application rather than Stored proceedures),that needs to run in 2 databases (both SQL Server):

    The first one is a test database that we use when in developing time quota to test our data

    The second one is the real thing a data base that contains lots of records.

    When criteria are placed in the query, it returs few records in both the databases , but if no criteria are placed (So it fetches all the records..) In the test Database works ok, but in the real one it "jams" till 30 seconds pass and I get a time out message...

    I tried to change the Query time out time from inside the SQL Server from

    Tools/Options/Advanced

    but it doesn't seem to work out... it still times out after 30 secs

    Any Ideas?

    Thanx in advance

  2. #2
    Join Date
    Feb 2005
    Posts
    2
    Could it be that this is not a SQL problem but an ADO-ADO.NET problem, i'm sure the default commandtimeout for command objects in ADO-ADO.NET is 30 seconds if not specified, specify 0 for unlimited timeouts and a value in seconds if you want different from the default.

    I'd go for a 45 seconds property on the commandtimeout property...

    regards,
    J.

    P.S. If i'm off base here I appologise, this is my first post, total newb...

  3. #3
    Join Date
    Feb 2005
    Location
    Athens, Greece
    Posts
    2

    Angry

    Thanks a lot for the advise Dreamweaver, but I need to specify that I tested the queries from inside the SQL Server enterprise Manager creating two new Views (One in each separate Data Base) and pasting in their SQL section the SQL string that I "Pinched off/Copied " from the debugger of the .NET just before it is fed to the adapter...

    So I have side by side two new viewes with the exactly same SQL, but belonging to two different data bases...

    So the problem arises through the SQL server...

    Any more Ideas guys?....

    Thanks!.

  4. #4
    Join Date
    Feb 2005
    Posts
    2
    I've just been looking at the settings on a server I am looking after. Surprisingly the timeout properties in Tools/Options/Advanced differ from right clicking the server and selecting properties and then looking at the bottom part of the connection tab, on this server it has;

    0 in Tools/Options/Advanced

    and

    600 in Server Properties/Connection Tab

    I'd have a look in right click Server/Properties/Connection Tab and see if that says 30,

    regards,
    J.

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    first set sp_configure to display advanced options
    execute reconfigure with override to force a "LIVE" change
    then set remote query timeout to an appropriate value.
    run reconfigure with override again.


    Code:
    USE master
    EXEC sp_configure  'show advanced option', '1'
    /*
    Here is the message:
    Configuration option 'show advanced options' changed from 0 to 1. 
    Run the RECONFIGURE command to install.
    */  
    
    RECONFIGURE with override
    EXEC sp_configure
    
    --XXXX  change to query timeout value  (example '800')
    sp_configure 'remote Query Timeout', 'XXXX'
    I was so tired at the time i wrote this that i lost myself in coloring the code in the message posting area

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •