Powershell and "sp_who"

Mon, 15 Jun 2009 03:50:49 Z

Is it possible to use PowerShell to simply invoke the t-sql "sp_who" and have it return the result as an object? (sp_who and sp_who2 are OK but the DBNames are not displayed in the order I want. I can tweak Microsoft's code but was hoping that PowerShell could help out in some manner.) TIA, barkingdog

Powershell and "sp_who"

Mon, 15 Jun 2009 08:13:52 Z

cd SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQL\<instance name>
Invoke-Sqlcmd 'sp_who'

PowerShell Help Reader is an extensible viewer for Windows PowerShell Help System.

Powershell and "sp_who"

Mon, 15 Jun 2009 18:28:18 Z

Alexey,

I believe your script will run "sp_who2" but not present the output as an object that I can pass through the PowerShell pipeline and apply cmdlets to.

I was hoping there is a way of taking sp_who2 output, from PowerShell, presented as an object.

Barkingdog

Powershell and "sp_who"

Mon, 15 Jun 2009 19:14:51 Z

*Everything* that PowerShell emits is an object. I may try to bring up a SQL 2008 VM tomorrow to "see" what the issue might be.

Powershell and "sp_who"

Wed, 17 Jun 2009 15:41:14 Z

Alexey,

I believe your script will run "sp_who2" but not present the output as an object that I can pass through the PowerShell pipeline and apply cmdlets to.

I was hoping there is a way of taking sp_who2 output, from PowerShell, presented as an object.

Barkingdog

OK, I ran invoke-sqlcmd 'sp_who' and see objects of type system.data.datarow being returned.

Can you be more specific about what you need to do from here?

Now, I may see what you're trying to get at...

I tried this:
SQLPS>invoke-sqlcmd 'sp_who'|where{$_.blk -eq "0"}

But that doesn't work...

Powershell and "sp_who"

Wed, 17 Jun 2009 15:50:13 Z

Actually, this works though:

SQLPS>invoke-sqlcmd 'sp_who'|wehre{$_.dbname -eq "my_name"}

So there's something odd with some of the properties...

Powershell and "sp_who"

Tue, 23 Jun 2009 15:05:19 Z

A little typo there. Assuming you just needed to sort by dbname...

This works for me:

PS>invoke-sqlcmd 'sp_who'|sort dbname

Powershell and "sp_who"

Wed, 24 Jun 2009 03:04:50 Z

Marco,

Thanks for the great answers! You discerned exactly what I wanted to do but had no idea how to do iit!

Barkingdog