Solved

MSDE Connection or Command Timeout not changing

Posted on 2004-10-07
8
897 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
  • 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

15 Experts available now in Live!

Get 1:1 Help Now