OLE DB for SQL Server: TimeOut Expired

  • day-476284

    SSCommitted

    Points: 1844

    Hi everyone,

    I am using a reporting application which connects to a link server using OLE DB Provider for SQL Server. Link Server is connected to a FoxPro database by using OLE DB for FoxPro.

    When I test my query using "SQL Server 2000 Query Analyzer", I can get the result from the link server without anny problem.

    However, when I try to run the same query from the reporting application, I am getting this message:

    "Microsoft OLE DB Provider for SQL Server: Timeout expired."

    Anyone can help me?

    Thanks

  • Mayank Khatri

    SSCertifiable

    Points: 7934

    Increase the timeout period under tools , Advanced settings and then u try increasing timeout under Rt.clk on server ->properties ->connections->query time out from 600 secs to more.

  • Jack Corbett

    SSC Guru

    Points: 184381

    What are you using for security to connect to the FoxPro database?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • day-476284

    SSCommitted

    Points: 1844

    I changed the timeout settings to umlimited (timeout = 0)

    Still getting the same error for the large tables.

    I don't thing the error is related with the security because when I delete some of the table columns from its view, I can get the results without timeout error.

    Any ideas?

  • day-476284

    SSCommitted

    Points: 1844

    CLIENT SIDE:

    If the query is reading from large table, (100 columns x 20000 rows)

    I have no problem getting results using SQL Query Analyzer on the Client side.

    However, I am getting timeout problem from the client side application.

    The query failed. The message from the database engine was:

    Microsoft OLE DB Provider for SQL Server: Timeout expired.

    SERVER SIDE:

    I tested the same query on the server using the application. I can get the results.

    ENVIRONMENT:

    Server machine:

    The Server : Windows 2003 Server SP2

    Database Server : SQL Server 2000 – (8.00.2039 Standard Edition SP 2) uses linkserver (OLE DB 9.0.0.3504 ) to FoxPro 9.0 SP1 table

    : Timeout Settings: Query time-out (sec, 0=unlimited)

    Client machine:

    Windows XP SP2 : Windows Network Authentication

    SQL Server 2000 client

    For some reason my environment doesn’t like the outside application to connect to the server long time?

    Do you have any idea how to fix this timeout problem? Do I need to configure DCOM or DTC?

  • dev81

    SSC Rookie

    Points: 43

    Hi,

    You are getting the error at the client side and not the server side. It means changing the server configuration will not achieve anything. Instead you have to change the settings at the client side through code. e.g. in VB if we set property

    cnn.CommandTimeout

    This handles the timeout error. See if you can do such settings in your code.

  • day-476284

    SSCommitted

    Points: 1844

    Thank you for the advice, it was helpful.

    The client-side is very thin, it doesn't hold any component except a pointer to the application which is running on the server side.

    I added custom function script to the server side application for the ADO object timeout settings.

    You were right it wasn't the issue related with windows server

    Thanks again

  • misandrew

    SSC Enthusiast

    Points: 144

    try this -- this work magic

    UPDATE STATISTICS Sales.SalesPerson

    WITH FULLSCAN --- not even about timeout or changing the parameter !! [ this apply to not well tuned database table / index ]

    problem : timeout expired

    this solution is magic

    I try to configure the timeout parameter by right clicking on ther SERVER NAME

    and change the report manager file

    it does not work

    ===== but this one work magic (reminder: table has not even been index yet), so this is wonderful ===

    use AdventureWorks

    go

    UPDATE STATISTICS Sales.SalesTerritory

    WITH FULLSCAN

    go

    UPDATE STATISTICS Sales.SalesPerson

    WITH FULLSCAN

    go

    UPDATE STATISTICS HumanResources.Employee

    WITH FULLSCAN

    go

    UPDATE STATISTICS Person.Contact

    WITH FULLSCAN

    go

    UPDATE STATISTICS Sales.SalesOrderHeader

    WITH FULLSCAN

    go

    =========== and then I run this sample report (territory) on AdventureWorks again ===

    use AdventureWorks

    go

    SELECT ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue

    FROM Sales.SalesTerritory ST INNER JOIN

    Sales.SalesPerson SP ON ST.TerritoryID = SP.TerritoryID INNER JOIN

    HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN

    Person.Contact C ON E.ContactID = C.ContactID INNER JOIN

    Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID

    GROUP BY ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue

    ORDER BY ST.Name

    ------ I think this happen again and again in SQL Server, esp new, not performance tuned database, but people always overlook the UPDATE STATISTICS -- thanks

    xx3xxx

  • kanna

    Valued Member

    Points: 69

    We faced a similar problem. The query performance was better when executed through SQL Query Analyzer but it got timed out when executed through the application.

    We tried the update statistics and it worked fine.

    But we are unable to understand, how the execution plans can differ between query analyzer and remote application.

    Any way thanks for the suggestion.

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

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