set-sqldata spawning so many spids

May 5, 2010 at 12:56 AM

Am I doing some wrong here?  When i run the following

For ($servername In $serverlist) {
Set-SqlData -sqlserver "$servername" -dbname "dbname" -qry $SqlQuery

This is an example of my script, it spawns multiple spids on my sql server and the only way I can get rid of it is to close out of PowerGui and so it flushes and disconnects everything.  I've seen it spawn hundreds of connections.


May 5, 2010 at 1:55 AM

Both Set-SqlData and Get-SqlData use SMO to obtain a connection to SQL Server. If you use SQL Server Management Studio (SSMS), we're probably well aware that connections are persisted until SSMS is closed or the connection is forcibly terminated. SSMS uses SMO. Its possible to override this behavior in SMO, but not in the functions get-sqldata and set-sqldata as written. As a side note Invoke-Sqlcmd (the cmdlet that ships with SQL Server 2008 PowerShell host sqlps.exe) behaves the same way i.e. connections are persisted.

If you'd like to not persist your connection here's a few suggestions:

1. Use ADO.NET. SQLPSX has additional adolib module which has the connection management are built-in. For example using the adolib function invoke-query:

import-module adolib

invoke-query -server "Z002\sql2k8" -database master -sql "Select GetDate()" 

will execute the query and close the connection

2. I have another function called Invoke-SqlCmd2 on poshcode which is very simple and also will close the connection when complete.

3. For performance reasons sometimes you may want to open a connection one time to server and continue to use the same connection, but change the database context, well this is possible using adolib functions. For example:

import-module adolib

$conn = new-connection -server "Z002\sql2k8" -database "pubs"

invoke-query -sql  "Select db_name()" -connection $conn


invoke-query -sql  "Select db_name()" -connection $conn



May 5, 2010 at 7:15 PM

1. ok cool. I've got to try that.

2. I will check it out.

3. Intersting, this is very different than sqlclient .net connection.  When I use a datareader, I have to open and close the connections to change the query.  This seems much nicer and easier to use. Any reason why I shouldn't just use ADO instead? It seems the sqlclient objects all have to have these little issues.


May 5, 2010 at 10:12 PM

None that I can think of. If you're familiar with ADO.NET and look at the code behind invoke-sqlcmd2 or adolib you'll notice its very simple. If I'm building a PowerShell script that doesn't need SMO, I'll often just include a function like invoke-sqlcmd2 directly in my script rather than use these modules.