Newbie problems with SMO and SQLPSX: unclosed connections

I have little experience with the SQL Server Management Objects (SMO) library and am looking at SqlServer Powershell Extensions (sqlpsx) for the first time.

It’s a lot to learn…

My first real problem was a steady growth of open SQL Server connections. In my script I would use a Server object, which opened a database connection.  When my script ended, the connection remained open, so next time I ran it a new connection would open.  You can’t just let something like this continue—you’d eventually run out of connections!

After banging my head, experimenting, searching on Google, etc., for a few hours (I’m sure you’ve been there), I finally came across a link that showed me how to close the connection:

Call the Disconnect() method on the ConnectionContext property of the Server object.

   1: $smoServer = Get-SqlServer $instanceName

   2:  

   3: # ... do whatever you need here...

   4:  

   5: # close the connection or you end up with more connections every time you run this!

   6: $smoServer.ConnectionContext.Disconnect()

Many thanks to the folks out there in the ether!

Advertisements