SQL Server Performance

SQL SP time out through VB6 appliaction

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by djmoloney, Jul 13, 2005.

  1. djmoloney New Member


    I am supporting a VB6 developed system currently and i have an issue with one of my stored procedures which every so often timeouts through the application. the query its self is a very simple but does have a 3 unions, When i run the sp through query analyser it executes in .1 of second even when it is timing out thru the application.i initially thought it was the appliaction code but the ado connection code i use is standard across the application and this is the only sp that timeouts and also when i perform a sprecompile on the the sp, it runs fine through the application again, the frequency of of this happening is between every 2 - 5 weeks. there is no pattern to the time it occurs. i have tried droping and recreating the sp and also have created the sp under a new name and still this problem arises.
    this is the query that in the sp, Its just a list of all the products + 3 extra products that cannot be added to the products table.

    Select product from products
    union
    select 'PARTA'
    union
    select 'PARTB'
    union
    select 'PARTB'

    Any Suggestions???


  2. surendrakalekar New Member

    If your sp works fine in Query Analyzer then the problem may be in you VB6 code or the number of processes running when time out occurs. You can create trace to track this... also check the current activity of your sql server.


    Surendra Kalekar

  3. ranjitjain New Member

    Hi,
    In VB set the query timeout property of connection object and/or recordset object to 0 i.e.
    infinite.
    Else make sure you have installed latest MDAC 2.8
  4. satya Moderator

    Thats a good point to check on MDAC version and compatibility.
    The Default setting is 90 seconds. But if you problem is a poorly written SQL request or poorly indexed database, this may not solve you problem

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page