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

a bug in Enterprise Manager ?

if I use enterprise manager to change Login permissions, I
am prompted to confirm the password even if the password
is not changed. Worse thing is that most of the time I
don't know the login password , so I am not able use EM to
assign permissions. I have access to other computers with
SQL EM working, and if I need to I can still use QA. I use
my pc for most of the sql admin work and would like if I
can resolve this.
What should I do ?
Thanks in advanceSee if this helps:
FIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/default.aspx?scid=kb;en-us;826161&Product=sql2k
Rand
This posting is provided "as is" with no warranties and confers no rights.

a bug in Enterprise Manager ?

if I use enterprise manager to change Login permissions, I
am prompted to confirm the password even if the password
is not changed. Worse thing is that most of the time I
don't know the login password , so I am not able use EM to
assign permissions. I have access to other computers with
SQL EM working, and if I need to I can still use QA. I use
my pc for most of the sql admin work and would like if I
can resolve this.
What should I do ?
Thanks in advance
See if this helps:
FIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/default...&Product=sql2k
Rand
This posting is provided "as is" with no warranties and confers no rights.

a bug in Enterprise Manager ?

if I use enterprise manager to change Login permissions, I
am prompted to confirm the password even if the password
is not changed. Worse thing is that most of the time I
don't know the login password , so I am not able use EM to
assign permissions. I have access to other computers with
SQL EM working, and if I need to I can still use QA. I use
my pc for most of the sql admin work and would like if I
can resolve this.
What should I do ?
Thanks in advanceSee if this helps:
FIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/defaul...1&Product=sql2k
Rand
This posting is provided "as is" with no warranties and confers no rights.