Solved

ADO Connection to SQL7 timeout

Posted on 2001-09-02
15
431 Views
Last Modified: 2009-07-29
Can anyone tell me how to get VB/ADO/OLEDB to maintain a connection to SQL 7 without timing-out?

I'm using this connection string:
"PROVIDER=SQLOLEDB;DATABASE=mydatabase;DATA SOURCE=myserver;UID=sa;PWD=mypassword;ConnectionTimeout=0"

MSDN says that setting the ConnectionTimeout=0 should fix the problem, but it doesn't :(

I'm running quite a lengthy query btw :)

0
Comment
Question by:AddamB
  • 5
  • 5
  • 2
  • +2
15 Comments
 

Expert Comment

by:cetrefli
ID: 6450067
I think MSDN says,
Set the connection-timeout parameter to zero in a web server configuration page. Not in connection string section..
0
 
LVL 3

Expert Comment

by:krispols
ID: 6450160
If you have a problem with a timeout from an asp page and you allready set ConnectionTimeout to 0, then you must also change Server.ScriptTimeout that avoid to have a timeout with IIS.
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6450300
also try to set the query in a stored procedure..

increase the scripttimout

server.ScriptTimeout = 1800 ' 20 minutes


also you need to increase the command timeout.. not the connection timeout

Set cn = Server.createObject("Adodb.connection")
cn.CommandTimeout = 1200
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Expert Comment

by:Silvers5
ID: 6450304
it's optional to call a stored procedure since it will make leter requests faster..
0
 

Expert Comment

by:cetrefli
ID: 6450327
if you run a quite lengthy query,You can make a stored procedure in SQL onyly turns a need result set, better than normal  " select" query. Query runs on the server.

Change two timeouts
 1. Script.Timeout=2400 (This changes from web server too.)
 2. Command.Timeout=1200


0
 
LVL 5

Expert Comment

by:russellshome
ID: 6450442
Just a thought: Have you looked at the efficiency of the query? Sub-Queries and joins are often the place to look for efficiency gains.
0
 

Author Comment

by:AddamB
ID: 6451777
Ok, I've got it working just about all the time now.

BTW the query was of the form:

DELETE FROM xtable WHERE col1 IN (SELECT cola FROM ytable);

... where ytable is about 3.5 million rows, and xtable has about 250,000 rows.

The Connection.CommandTimeout property seemed to make a difference.

However, the Server.ScriptTimeout property made me re-think the way SQL7 is set up. Strangely, in the Server->Connection Properties dialog, it says that setting "Query time-out" to 0 means that it has unlimited time for the query to execute.

** Not True!**

I set this to 10,000 and the query started working correctly.

Just goes to show, never take Microsoft at face value ;-)

If anyone has further constructive comments to make, I'd like to hear them... but at this stage Silvers5 looks like taking the points, because he put me on the right track first.

Cheers :)
0
 

Author Comment

by:AddamB
ID: 6451810
OK, I was wrong.

Just tried it on a "xtable" containing 500,000 rows this time, with all 3 timeouts set to 60,000 : and it STILL times-out!

Seems to me that the timeout value is not really in seconds!

(because it only took about 60 seconds to time out, not 1000 minutes).

Any ideas why?

0
 
LVL 3

Expert Comment

by:krispols
ID: 6452395
normaly the query must go faster in this way, depend of your index.
DELETE FROM xtable inner join ytable
on xtable.col1 = ytable.cola

0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6487311
hew.. you should increase the script timeout too..
0
 

Author Comment

by:AddamB
ID: 6489027
I guess Script.Timeout is an ASP object?

How would I achieve something similar in VB (ie. compiled code)?

:)
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6489477
is it the ado timing out? the command object should fix that.. can you post parts of the code?
0
 

Author Comment

by:AddamB
ID: 6489493
The function is intended to remove duplicate name entries from the table....

It times out on a 6.5 million row table.


Private Sub Form_Load()
frmMain.strConnection = "PROVIDER=SQLOLEDB;DATABASE=database;DATA SOURCE=server;UID=uid;PWD=pwd;ConnectionTimeout=60000;CommandTimeout=60000"frmMain.conX.Open (frmMain.strConnection)
End Sub

Private Sub btnDeDupe_Click()
frmMain.conX.Close
frmMain.strConnection = "PROVIDER=SQLOLEDB;DATABASE=database;DATA SOURCE=server;UID=uid;PWD=pwd;ConnectionTimeout=60000;CommandTimeout=60000"
    frmMain.conX.Open (frmMain.strConnection)
intRcount = 0

strExecute = "SELECT count(*) AS counted from " & strDatabaseTableName & ""
Set rsc = conX.Execute(strExecute)
intFirstcount = rsc("counted")

frmProgress.lblProgress.Caption = "Deduping..."
frmProgress.ProgressBar1.Value = 0
frmProgress.lblPercent.Caption = "0% complete"
frmProgress.Show
frmProgress.Refresh

Screen.MousePointer = vbHourglass

strExecute = "SELECT DISTINCT name INTO Deduped FROM " & strDatabaseTableName
conX.Execute (strExecute)
' This is where it times out!


strExecute = "ALTER TABLE Deduped ADD [id] [int] IDENTITY (1, 1) NOT NULL"
conX.Execute (strExecute)

strExecute = "DROP TABLE " & strDatabaseTableName
conX.Execute (strExecute)
strExecute = "SELECT * INTO " & strDatabaseTableName & " FROM Deduped;"
conX.Execute (strExecute)
strExecute = "DROP TABLE Deduped"
conX.Execute (strExecute)
End Sub
0
 
LVL 20

Accepted Solution

by:
Silvers5 earned 50 total points
ID: 6489520
hmm.. try to make a stored procedure and call it from the script..

connection timeout is not the point.. the point is the commandtimeout.. it's in seconds.

remove the commandtimeout and connectiontimeout from the connection string.. try to insert this line after you open the connection and before executing:

frmMain.conX.CommandTimeout = 1800

rgrds

0
 

Author Comment

by:AddamB
ID: 6489677
Thanks Silvers5... setting the property AFTER making the connection was the key :)

I really appreciate the help!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question