?
Solved

ADO Connection to SQL7 timeout

Posted on 2001-09-02
15
Medium Priority
?
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

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!

Question has a verified solution.

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

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…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

770 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