ADO connection state

Quote:

---

Originally Posted by yrwyddfa

OMG! You let a hardware guy near some hardware? I thought that they were just office ornaments . . . . ;)

---

Not my site - I'm just software :bigyello:

Funny thing is the guy was replacing the "backup" tape drive because a tape was stuck in it for several days.

So you got a machine that hasn't had a successful backup in a couple of days - pulled out of the rack - top off - power on (users still in the application performing daily work!).

I happened to be on-site that day in a meeting - walked in a couple of minutes after the lightning stuck - you could see he adrenalin pumping through this guy!

Glad it wasn't me!

Quote:

---

Originally Posted by Dave Sell

That takes care of the easy part - the backup. The restore is usu. a different animal. Do you have a corresponding restore mechanism?

Thanks!

Dave

---

This does a backup and restore (to a different DB name - but that's easy to change).

Code:

---

BACKUP DATABASE Funds
  TO DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE FILELISTONLY
  FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE DATABASE Funds_Copy
  FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
  WITH MOVE 'Funds_data' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.mdf',
  MOVE 'Funds_log' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.ldf'
GO

---

We do stuff like this all the time to make copies of production DB's - sometimes bringing them to laptops and off-site to our development shop.

The hardest part about restoring a production DB is that the security falls apart when you delete the original DB and restore. We use database-roles and Windows-groups for authentication - so it's easier for us. But some of my associates use SQL authentication and have hundreds of username/pw problems if a restore is done.

Hey, I'm the original poster.

I didn't know there was so much activity here on this thread :)

I agree with szlamany that closing the connection is not a hard-and-fast rule. My application doesn't have that many users, and pretty much all users will be doing constant data operations such as SELECTs at least, so closing and reopening after each command would be total overkill, not to mention stupid. Users' connections only idle occasionally, or on lunch.

In my understanding, OLEDB (the SQLOLEDB provider), also uses connection pooling, not only ODBC. What I have been unable to figure out is exactly when pooling is used, but I suspect the OS of the box SQL Server is installed might have something to do with it. To all the people asking what's wrong with the network or database, nothing. Read up on Connection Pooling and see why database connections can be disconnected on a fully functional system and network. We used to have a peer-to-peer network, and pooling was not on then, when we upgraded to a client-server with Windows Server 2003, we began having the disconnection problems, so maybe it's only on the OS, because the version of SQL Server is the same.

The workaround (pathetic, I know) I did is in each sub that does database access, the first line in the handler is

VB Code:

If ReOpenConn() = True then Resume

And in a module,

VB Code:

Public Function ReOpenConn() As Boolean
Dim iProp As Integer
'If ADO/ODBC is using connection pooling, any client idling for X minutes gets disconnected.  Must reconnect
For iProp = 0 To modCn.Properties.Count - 1
    If modCn.Properties(iProp).Name = "Connection Status" Then Exit For
Next
If iProp >= modCn.Properties.Count Then ReOpenConn = True: Exit Function
If modCn.Properties("Connection Status").Value = 1 Then ReOpenConn = True: Exit Function
 
Screen.MousePointer = vbHourglass
modCn.Close
modCn.Open
Screen.MousePointer = vbDefault
ReOpenConn = True
End Function

Connection Status is a dynamic property on the connection object that Surprise! accurately reflects the connection's state. The code runs in a loop to find the property because it is not guaranteed to be there; dynamic properties vary between versions of MDAC and/or providers.

So far the code seems alright. There is a miniscule delay, and the connection reopens.