Timeout while running SQL Stored Procedure

Posted on 2007-10-05
Last Modified: 2012-08-13
I am running SQL Server 2005.  As far as I can tell the remote query timout for the server (all databases) is 600 seconds.

I am running an Access front-end with the following connection string:
Provider=sqloledb;Data Source=n.n.n.n;Initial Catalog=DBName;Connection Timeout=600;UID=myuserid;PWD=mypwd

When I run a stored procedure that will take at least 1 minute, it always times out after 30 seconds.  What am I missing?
Question by:wsturdev
    LVL 27

    Accepted Solution

    Time out is buried in SQL Server Management Studio

    Management Studio > Tools > Options > Designers

    Change the default transaction time out there.
    LVL 1

    Author Comment

    I went there and changed it to 600 seconds, and the timeout still happens after 30 seconds.

    Also, are you saying there would be no way to set the timeout to "normal" and override it when needed?
    LVL 27

    Expert Comment

    What is the exact timeout message?
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    >Provider=sqloledb;Data Source=n.n.n.n;Initial Catalog=DBName;Connection Timeout=600;UID=myuserid;PWD=mypwd
    you set the connection timeout, but what you need is the command timeout, which is also 30 seconds by default.

    using the adodb.command object, set the commandtimeout property of that object to 600 before executing the command (procedure)

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now