Request Timeout when issuing SQL statement

[redshadow]

Hi All,

I am using SQL Sever 2000 and Visual Basic 6.
When I tried to query a database using about 6 tables, I stablished inner join to 4 of them, outer join for the remaining.

This query runs for about 2 minutes and outputs about 12000 records using SQL Query Analyzer.

Upon pasting it to visual basic it generates a message connection timeout. I tried setting the property ConnectionTimeout to 800 but to no avail.

Is anyone has an idea how to do this long query with visual basic ado, what I am worried is what if it grows to about 5-10 minutes extraction? I think it is not wise to set the timeout indefinitely.

 

[redshadow]

Sorry, that was 120,000 records, not 12000.

 

[CasperTFG]

What I would do is Create your select as a stored procedure, then execute the SP. Runs much faster. Also are you sure the timeout got set to 800? Try putting a watch on your RS and Connection object to see what value it is.

Also are you runing this through COM? There could be another time out value you are missing. COM objects have a timeout value of 60 seconds if the request doesn't return it will destroy the object.

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[redshadow]

Hi CasperTFG, you are right. I should try to put it in a SP.

I am using this database for payroll and employee numbers are growing by 10 to 20 percent every month as the company has just started its operations and I expect that it will reach thousands and that is what I worry much.

But yes, SP can help me or worst I'll have to set an infinite timeout if there will be no other way.

This program is just a simple setup and does not make use of COM.

Thanks for the reply I appreciate it.