Solved

ADO Connection to SQL7 timeout

Posted on 2001-09-02
15
427 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Converting Website Application to new Hardware - Need to Test 6 50
Age between date range query (SP) 13 52
SP to delete duplicates 15 60
Want the count number from this QUery 2 38
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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now