+ Reply to Thread
Results 1 to 12 of 12

Connection with SQL Server generate "Timeout expired"

  1. #1
    Registered User
    Join Date
    06-14-2005
    Posts
    8

    Connection with SQL Server generate "Timeout expired"

    Hi All,

    When I execute the following query

    "DELETE FROM Cst_Acessorios WHERE (CodInterno_IV_Acessorio IN ('0','N') AND Qtd_Acessorio = 0) OR (CodZFoccus_IV = '0' AND CodInterno_IV = '0')"

    in a SQL Server database a receive the error message "Timeout Expired".

    I already changed the parameters to the OLEDB connection:
    - Connect Timeout = 3000
    - Connection Lifetime = 600

    But I continue receiving the same error message.

    Anybody can help me?

    Best regards,
    JV.

  2. #2
    Jake Marx
    Guest

    Re: Connection with SQL Server generate "Timeout expired"

    Hi JV,

    joaovtt wrote:
    > When I execute the following query
    >
    > "DELETE FROM Cst_Acessorios WHERE (CodInterno_IV_Acessorio IN
    > ('0','N') AND Qtd_Acessorio = 0) OR (CodZFoccus_IV = '0' AND
    > CodInterno_IV = '0')"
    >
    > in a SQL Server database a receive the error message "Timeout
    > Expired".
    >
    > I already changed the parameters to the OLEDB connection:
    > - Connect Timeout = 3000
    > - Connection Lifetime = 600
    >
    > But I continue receiving the same error message.


    If you execute this statement using Query Analyzer or a similar tool, how
    long does it take to execute?

    How are you executing the statement in VBA? Using the Connection object or
    the Command object?

    --
    Regards,

    Jake Marx
    MS MVP - Excel
    www.longhead.com

    [please keep replies in the newsgroup - email address unmonitored]


  3. #3
    Registered User
    Join Date
    06-14-2005
    Posts
    8

    Re: Connection with SQL Server generate "Timeout expired"

    hi Jake,

    if I use Query Analyzer, this query take around 3 or 4 minutes.


    I'm using the following code:

    Dim con As Object
    Set con = CreateObject("ADODB.Connection")
    con.Open "Provider=SQLOLEDB.1;Connect Timeout=0;Command Timeout=0;Data Source=" & server & ";Initial Catalog=" & dataBase & ";User Id=" & userName & ";password=" & uPassword & ""
    con.Execute "DELETE FROM Cst_Acessorios WHERE (CodInterno_IV_Acessorio IN ('0','N') AND Qtd_Acessorio = 0) OR (CodZFoccus_IV = '0' AND CodInterno_IV = '0');"


    best regards,
    JV

  4. #4
    Jake Marx
    Guest

    Re: Connection with SQL Server generate "Timeout expired"

    Hi JV,

    I have never attempted to use "Command Timeout" in the connection string
    itself. Maybe it's not supported? Either way, you can add this code before
    invoking the Execute method:

    con.CommandTimeout = 0

    That should allow the query to take as long as it needs to.

    That said, 3 to 4 minutes for a query as simple as this seems like a long
    time. How many records are in your table? What types of fields are those
    that are referenced in your WHERE clause? Are there indexes on those
    fields?

    --
    Regards,

    Jake Marx
    MS MVP - Excel
    www.longhead.com

    [please keep replies in the newsgroup - email address unmonitored]


    joaovtt wrote:
    > hi Jake,
    >
    > if I use Query Analyzer, this query take around 3 or 4 minutes.
    >
    >
    > I'm using the following code:
    >
    > Dim con As Object
    > Set con = CreateObject("ADODB.Connection")
    > con.Open "Provider=SQLOLEDB.1;Connect Timeout=0;Command Timeout=0;Data
    > Source=" & server & ";Initial Catalog=" & dataBase & ";User Id=" &
    > userName & ";password=" & uPassword & ""
    > con.Execute "DELETE FROM Cst_Acessorios WHERE (CodInterno_IV_Acessorio
    > IN ('0','N') AND Qtd_Acessorio = 0) OR (CodZFoccus_IV = '0' AND
    > CodInterno_IV = '0');"
    >
    >
    > best regards,
    > JV



  5. #5
    DM Unseen
    Guest

    Re: Connection with SQL Server generate "Timeout expired"

    Maybe start using a T-SQL stored proc for this?

    Also in Query Analyzer run the optimizer to see if you missed any
    indexes. Either your SQL server is underperforming or not fully
    optimized.

    DM Unseen


  6. #6
    Registered User
    Join Date
    06-14-2005
    Posts
    8

    Re: Connection with SQL Server generate "Timeout expired" Reply to Thread

    I'm sorry, but how can I call a stored procedure from a excel macro?


    My table has up to 4 million records... At the moment, I'm not using any indexes because it's a table for staging in a ETL process. If I use indexes any time I truncate and insert in this table, these indexes will be update?

  7. #7
    Robin Hammond
    Guest

    Re: Connection with SQL Server generate "Timeout expired"

    this shows a previous post where I describe running a stored proc.

    http://tinyurl.com/b4ewn

    4 million rows is not a lot. One way or another you should have a primary
    key set on the table that will give you a start towards indexing it. You
    could also try creating a trace file using Profiler testing your typical
    activities then use the index tuning wizard in enterprise manager with the
    trace.

    Robin Hammond
    www.enhanceddatasystems.com

    "joaovtt" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I'm sorry, but how can I call a stored procedure from a excel macro?
    >
    >
    > My table has up to 4 million records... At the moment, I'm not using
    > any indexes because it's a table for staging in a ETL process. If I use
    > indexes any time I truncate and insert in this table, these indexes will
    > be update?
    >
    >
    > --
    > joaovtt
    > ------------------------------------------------------------------------
    > joaovtt's Profile:
    > http://www.excelforum.com/member.php...o&userid=24286
    > View this thread: http://www.excelforum.com/showthread...hreadid=381734
    >




  8. #8
    DM Unseen
    Guest

    Re: Connection with SQL Server generate "Timeout expired"

    Indexes get updated after truncation and insertion, Only dropping the
    table destroys the the index.

    there are a lot of ways to run an sp from Excel, but your main concern
    here is how much "raw" data is goin over from Excel to SQLserver. just
    a few values or whole sheets of data.

    - Use ADO code to connect to SQlserver with VBA and run an sp with
    parametes
    - Use a Querytable with ODBC and use the Call syntax. this could be run
    without code (with a little trickery)


    Pls elaborate on the data, then i can tell which method is best.

    DM Unseen


  9. #9
    Registered User
    Join Date
    07-17-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Connection with SQL Server generate "Timeout expired"

    I know this is an old thread, but I just stumbled upon it and have also had this problem in the past.

    You can make the timeout work by setting it immediately before opening the connection:

    Dim con As Object
    Set con = CreateObject("ADODB.Connection")

    con.ConnectionTimeout=600

    con.Open "Provider=SQLOLEDB.1;Connect Timeout=0;Command Timeout=0;Data Source=" & server & ";Initial Catalog=" & dataBase & ";User Id=" & userName & ";password=" & uPassword & ""
    con.Execute "DELETE FROM Cst_Acessorios WHERE (CodInterno_IV_Acessorio IN ('0','N') AND Qtd_Acessorio = 0) OR (CodZFoccus_IV = '0' AND CodInterno_IV = '0');"

  10. #10
    Registered User
    Join Date
    08-15-2013
    Location
    New Haven, Connecticut USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Connection with SQL Server generate "Timeout expired"

    I needed to set the command timeout not the connection timeout to get my recordset timeout to not timeout at 30 seconds

    Please Login or Register  to view this content.
    Last edited by arlu1201; 08-16-2013 at 01:34 AM.

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Connection with SQL Server generate "Timeout expired"

    greggib,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Connection with SQL Server generate "Timeout expired"

    greggib,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1