Solved

ADO Connection to SQL7 timeout

Posted on 2001-09-02
15
426 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

22 Experts available now in Live!

Get 1:1 Help Now