Solved

ADO Connection to SQL7 timeout

Posted on 2001-09-02
15
429 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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