Connection time out error while connecting VB6 to SQL server 2008

[resumes123]

I am using Visual Basic 6 as the front end
I am using SQL server 2008 express as the back end
I am trying to connect to a table in SQL server in MODEL database which has about 140 million records. Table name is dbo.tickdata_master
I am using ADO
Whenever I am trying to fetch records from that table, I get a
"Timeout expired" -2147217871 error

How do I resolve this and see that I do not get this error
When I am trying to connect to other tables using the same connection, which have just 100 rows, I am not getting this error and I am able to fetch all the data with no problem
The code is attached
I tried using Connectiontimeout = 0, but it did not work for the large table

Please suggest me a remedy, so that I can fetch rows even from table of about 140 million row



Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
dim str as string
str = "select count(*) from dbo.tickdata_master"

cn.ConnectionTimeout = 0
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=JACKIE\SQLEXPRESS;Use Procedure for Prepare= 1;Auto Translate=True;Packet Size=4096;Workstation ID=JACKIE;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=model"

rs.Open str, cn

 

[JoeAtWork]

Before seeing your code, I was going to ask why you needed all 140 million records at once (because it would be highly unlikely that you need anything other than a small subset).

However, it seems you just want to get the record count?

Possibly changing:

Count(*)

to

Count(Indexed_Field)

would help, where Indexed_Field is the name of a field that is indexed and does not allow Nulls (if you picked a field where some values are Null, those records would not get counted).

You might also try writing a Stored Procedure in SQL Server that gets the count, then change your VB code to use an ADO Command object to execute the stored procedure.

 

[ArtieChoke]

How long does it take for that statement to return in query analyzer (or whatever the 2008 version is called)?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[JoeAtWork]

I think he got his answer in the SQL Server forum where he cross-posted to.

resumes123 - it is considered good etiquette to post any resolution you found to your problem back to your thread for the benefit of others who have the same issue.