A connection remains open after calling Disconnect

Hi,

I would like to be able to connect to a SQL Server using SMO and then reconnect using a different login username and password.

In the following sample code I call svr.ConnectionContext.Disconnect. After the Disconnect call an attempt to change the timeout or any Login properties gives error:

Connection properties cannot be changed after connection has been made

Regards,

Joginder Nahil
www.starprint2000.com

Dim objServer As Server
objServer = New Server()
With objServer.ConnectionContext
.NonPooledConnection = True
.ConnectTimeout = 30
.LoginSecure = True
.Connect()

MsgBox("Connected. Database : " & objServer.Databases(0).Name)

.Disconnect()

' The following line gives error: Connection properties cannot be changed after connection has been made
.ConnectTimeout = 60
.LoginSecure = False
.Login="me"
.Password="pppp"

.Connect()
End With

Hi,

I have found a solution. You have to set the server object to nothing before the ConnectionContext is released and can be re-used as shown below:

Regards,
Joginder Nahil
www.starprint2000.com

Dim objServer As Server
objServer = New Server()

With objServer.ConnectionContext
.NonPooledConnection = True
.ConnectTimeout = 30
.LoginSecure = True
.Connect()

MsgBox("Connected. Database : " & objServer.Databases(0).Name)
.Disconnect()
End With

' Release the Server object only then the Connection is released
objServer= Nothing
objServer = New Server

With objServer.ConnectionContext
.ConnectTimeout = 60
.LoginSecure = False
.Login="me"
.Password="pppp"
.Connect()
End With