Building MySQLActivity Windows solution…

Here’s another proof-of-concept using what it’s available on your SQL Server and building a windows solution.  In my previous blog, I create a PowerShell script that would allow me to query my SQL Servers with my customized version of the SP_Who2n but just to realized later that it won’t work on SQL Servers 2000.  So, I decided to evolved my script to a Windows solution using SAPIEN technologies “Visual PowerShell 2011”.

This solution will accomplished the following:

1. Able to access all instances names of SQL Server without the need of SMO using .NET Framework.

2. Will display the SQL Server version information.

3. You have the ability to sort any of the columns.

4. Solution will provide instant ability to query for quick viewing.

5. Future feature could be added later to manage SPID’s.

6. This solution assume you have Windows Trusted Authentication.

This is just a starting SQL Activity solution:

Now, in the script tab you will add your code.  In your ‘Designer’ tab, when you double-click on the ‘Button’ control, it will take you to the script tab section and into the $button_Click={… } Script-block function.  This way you just all the code:

On my button code I accomplished two things:

1. Identify SQL Server 2000 so in can pick the correct version of my customized version of SP_Who2.

2. Then, after finding the SQL Server SP-Who2 version run the SQL script, and display the results on the datagrid.

All the .NET code to execute the T-SQL script against SQL Server are non_SMO.  So, there’s no need to install the SQL Server Management Objects.  Here’s a sample snippet of the code use to help build the combo-box with the SQL Server name and their instances:

# – Non_SMO to build the “SQLServername\InstanceName” and convert them to a strings arrays

$SQLInst = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | select @{Expression={if(($_.InstanceName).length -gt 0){$_.ServerName+"\"+$_.InstanceName}else{$_.ServerName}}; Name="SQLName"}

[Array] $Global:s
$Global:s = foreach($i in $SQLInst) {$i.SQLname}


And, here’s a code snippet to run a T-SQL query and save the results to a variable:

# – Non_SMO to run a T-SQL query and saving the results to later use.

$SQLqry = "Select @@Version as SQLversion"
$con = "server=$getSvr;database=Master;Integrated Security=sspi"
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($SQLqry, $con)
$sdt = New-Object System.Data.DataTable
$sda.fill($sdt) | Out-Null   
$tdata = $sdt


Now, when using “Visual PowerShell 2011”, you take advantage of intelli-sense to speed your development.  I like the fact that, to populate my datagrid component with the result of my SP_Who2 query, I just needed to add one line of code:

$datagrid1.DataSource = $tdata

I’m ready to build a “MySQLActivity.exe’ solution.

So, as you can see, the possibility to keep improving your PowerShell scripting to a Windows solution gives the ability to be more productive.  I will be adding more feature to this solution.  It you want to test drive it as an executable, contact me on twitter.

Keep PowerShelling!!


About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s