Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSDE Connection or Command Timeout not changing

Posted on 2004-10-07
8
Medium Priority
?
913 Views
Last Modified: 2008-01-09
Hello,

Working with VB6 and MSDE on 2 computers. I am trying to get the timeout to be shorter than the 60 seconds that it currently takes. I open a connection with Pessimestic locking and begin a transaction. I then start another instance of the same program which tries to begin the same transaction. The second program just hangs for 60 seconds exactly. Everything that I read tells me that the connection timeout is 15 seconds and the command timeout is 30 seconds. Changing these values has no effect on the 60 second delay until I get the 80004005 timeout error.

Is there some other timeout that I should be setting that will cut down on the delay? From a users view it looks like the system has locked up and they will probably hit the power switch before the system reports the timeout error. I just want some feedback from the system that the database is busy and please wait.

Thanks!
0
Comment
Question by:Frogers
[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
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12251344
What timeouts are you  setting?
Could it be that you have a retry count set somewhere so that perhaps after the first command timeout it retries once automatically?
0
 

Author Comment

by:Frogers
ID: 12251532
This is the entire code. I am trying to get a handle on how locking and contention works.

Private Sub Form_Load()
  Dim adoConnect As ADODB.Connection
  Dim rsCustomers As ADODB.Recordset
  Dim strPrompt As String
  Dim Temp As Long
 
  Set adoConnect = New ADODB.Connection
 
  Temp = adoConnect.ConnectionTimeout
  adoConnect.ConnectionTimeout = 2
  Temp = adoConnect.ConnectionTimeout
 
  adoConnect.Open _
    "Provider=SQLOLEDB.1;" & _
    "Persist Security Info=False;" & _
    "User ID=sa;" & _
    "Password=AStrongPassword;" & _
    "Initial Catalog=Northwind;" & _
    "Data Source=DBServer\NetSDK;" & _
    "Connection Timeout=2;"
  Set rsCustomers = New ADODB.Recordset
  rsCustomers.CursorType = adOpenDynamic
  rsCustomers.LockType = adLockPessimistic
  rsCustomers.Open "Customers", adoConnect, , , adCmdTable
 
  Temp = adoConnect.CommandTimeout
  adoConnect.CommandTimeout = 2
  Temp = adoConnect.CommandTimeout
 
  rsCustomers.MoveFirst
 
  adoConnect.BeginTrans
 
  Do Until rsCustomers.EOF
    strPrompt = "Current contact name = " & _
      rsCustomers!ContactName & _
      ". Change to undefined?"
    If MsgBox(strPrompt, vbYesNo) = vbYes Then
      rsCustomers!ContactName = "undefined"
      rsCustomers.Update
    End If
    rsCustomers.MoveNext
  Loop
 
  If MsgBox("Save your changes?", vbYesNo) = vbYes Then
    adoConnect.CommitTrans
  Else
    adoConnect.RollbackTrans
  End If
 
  rsCustomers.Close
  adoConnect.Close
End Sub

This is the sample code from "ADO Programming in Visual Basic 6" By Steven Holzner Listing 5.1. The Temp variable I added to make sure the timeout was being changed in both the connection and command. I even added a timeout to the connect string. If the code is retrying it is doing it because of some default that I have yet to play with.

If you compile this code to a exe and run it, then run the code in the environment also it will hang at the MoveFirst command for 60 seconds and then timeout.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12252251
clutching at straws here:

I think for SQLOLEDB the timeout property is specified by "Connect Timeout" and not "Connection Timeout" so try:
 adoConnect.Open _
    "Provider=SQLOLEDB.1;" & _
    "Persist Security Info=False;" & _
    "User ID=sa;" & _
    "Password=AStrongPassword;" & _
    "Initial Catalog=Northwind;" & _
    "Data Source=DBServer\NetSDK;" & _
    "Connect Timeout=2;"

If that fails:

Try adoConnect.ConnectionTimeout = 2 after the Open call.

If that fails, try tracing what's going on at the driver level. That can be accessed via Admin Tool / Data Sources , There si a Tracing tab there.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:Frogers
ID: 12253180
You are correct. It is 'Connect Timeout' and not 'Connection Timeout'.... but it still hangs for 60 seconds anyway. Also placing the adoConnect.ConnectionTimeout=2 after the .Open throws the 'Operation not allowed when the object is Open' error. The timeout is read only when the connection is open and read/write when closed.

As far as tracing... is this a Server Manager thing? I only have MSDE. All the fun toys MicroSoft kept for the big boys.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12254049
The tracing is an ODBC thing. Start Menu/Administrative tools/  Data Sources.

MSDE has some artificial limitations to limit its performance. It is intended as a single-user desktop db, not a multi-user system . Maybe one of those limitations is to fix connection timeouts somehow. More info: http://msdn.microsoft.com/library/?url=/library/en-us/architec/8_ar_sa2_0ciq.asp

Some often effective straws to clutch:
-service pack sql server
-update mdac

Beyond that I'm stuck.

> All the fun toys MicroSoft kept for the big boys.
BTW, you can get a 120-day evaluation of SQL server. The tools can talk to MSDE.
0
 

Author Comment

by:Frogers
ID: 12254364
The limitations I read awhile ago before I started using MSDE. They seemed more than enough for my small company. I didn't want a application written only later to find out that it wouldn't run when you add a few users. I did d/l the 120 day evaluation software just so I could have the help files for SQL Server.

It looks like I have to learn to use Optimistic Locking and then check for problems after the fact. It seems wrong to me but if that is the way the world of db is going then I have to convert. I'm used to locking the file, making the changes and then unlocking the file. Anyone else just gets a 'Busy' message till the first person is done. But it seems like there is no way to detect 'Busy' anymore.

I'll leave this open for awhile longer to see if anyone else offers a suggestion. Thanks for your help.
0
 
LVL 12

Accepted Solution

by:
monosodiumg earned 500 total points
ID: 12254460
Optimistic locking means the records doesn't mean you get inconsistencies. You are still guaranteed the ACID properties. You will never get a dirty read.
The worst that can happen is that two users load the same record, update their local copy and the second one to submit his update to the DB gets it rejected.

If the number of users is so small that MSDE will cover your needs, then such conflicts are a priori unlikely, unless the transactions your users are performing take a very long time to execute. Also, in my experience, real world factors usually make such conflicts unlikely. Imagine acall centre where 5000 operators are continuously updating customer records. Since a given customer is only going to be talking to one operator, there is no chance of two operators trying to update the same record at the same time.

Sounds like you are coming from a file-based background. In relational databases, you can expect record or page level locking so you can have multiple different records  being updated at he same time. There is absolutely no need to lock an entire table. With realtional database, you can forget about locking and consistency: the DB engine will take care of it. You do not need to do any checking. That's one of the big wins of relational DBs.

In some circumstances you can get performance or contention problems with locking but that is very very unlikely to be an issue for you.

>But it seems like there is no way to detect 'Busy' anymore.
Correct. There is no need to either. When you a user updates a record, they are updating a local copy. You then UPDATE (sql) the record in the DB. That's UPDATE is likely to take less than a millisecond. The odds of a 'busy' situation this side of the next century are small!
0
 

Author Comment

by:Frogers
ID: 12259857
Yes, our 20+ year old system is flat-file. It started back with MS QBasic 3 and progressed to MS Basic PDS 7.1. When it changed to VB 1 we stayed with DOS till now. I didn't mean to infer that data is lost with Optimistic locking it is just that it is backwards to what I am used to. All my users are used to waiting once in a while to get access to a shared resource. Now they have to get used to making their changes and once in a while they will get a message that their changes were not saved because someone else changed them quicker.

My original question was about a timeout setting not working. I'm surprised that no one has made a comment on that but it's time to move on and embrace the future. Thanks for the nudge in the new direction.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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