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

64 bit Server Aliases SQL 2005

Does anyone know how to create SQL Server aliases for Itanium servers?

Alternatively does anyone know how to use SMO to create a SQLAliasCollection for a specific server? I can't seem to find how to get that collection.

I've added aliases that work for 32 bit applications but they don't seem to be used for 64 bit applications.

Please check my blog for alias. http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx

You need to use 64-bit cliconfg.exe or SQL Server Connection Manager to create the alias.

|||

Thanks. It turns out alias are working fine for 64 bit and we need to add the 32 bit. The problem turned out to be that I can't connect from an Itanium Back to the itanium using a different name. When we do, NT credentials are getting blocked. We can connect to other machines using aliases, just not back to the same machine. We're investigating how to get around that at the moment :)