SQL Server Performance Forum – Threads Archive

SQL SP time out through VB6 appliaction

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???

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

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

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.

]]>