Monthly Archives: September 2010

Neat Trick – SQL Powershell Provider


This is something rather basic but certainly worth mentioning.

Once you load the powershell SQL Provider (code below) you can access a sql servers DB’s like so,

cd SQLServer:\sql\servername\default\databases\dbname

That is to say, you change to the provider, SQLServer, go to the DB engine side of things, sql, the server, the instance which in this case is the default, then in to databases, and then the database name.

well, who wants to type that out all the time? tab complete works, but i’ve run in to problems doing it right after the server name.

So, if you are like me and often just want to poke around the databases, you can make a shortcut like so.

New-PSDrive –name ServerSQL –root SQLServer:\sql\server\default\databases –psprovider sqlserver

so then, to get to that servers SQL DB’s you can just do

 

cd servername:

Here is the code you should have in your profile to load up the SQL stuff.

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}

#
# Set mandatory variables for the SQL Server provider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location

.NET 4.0 Chart Control Problem – Connection Was Not Closed


So, I’ve go this huge perfmon log file that I’m trying to get some charts out of and excel just isn’t cutting it for me and SSRS just turned out to be a pain and no easier than making my own app (learning curve) so here I am, making a basic charting program in C#. As you may have noticed I spend most of my time in Powershell so I’m stumbling over this a bit and I ran in to a problem using the charting control that is telling me the connection was not closed when I attempt to bind my SQLCommand object to the chart control. Come to find out the connection needs to be closed and the chart needs to open it.

here is some sample code:

         

          string query = "select field1, field2 from table";
          string connstr = "Server=yourserver;Database=yourdb;integrated security";
          SqlConnection conn = new SqlConnection(connstr);
          SqlCommand cmd = new SqlCommand(query, conn);
          
          try
          {
              chart1.DataSource = cmd;
              chart1.Series["Series1"].XValueMember = "field1";
              chart1.Series["Series1"].YValueMembers = "field2";
          }
          catch (Exception Ex)
          {
              MessageBox.Show(Ex.Message);
          }

 

 

As you’ll notice, no where do you open or close the connection, its handled by the chart. Seems a little weird to me, but I guess that’s cool.

Powershell ISE History Window (why no F7 for ISE?!)


So with a classic command prompt and powershell console you can hit F7 to bring up a history window that will allow you to cycle through past commands easily. Sure you can use the Up and Down key there as well as with ISE but there is no history window for ISE. With all the cool things they’ve done with it, I’m kind of surprised.

So, I’ve come up with a simple little function to create this feature..

function Copy-HistoryItem{
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

$fg = $psise.Options.OutputPaneForegroundColor
$bg = $psise.Options.OutputPaneBackgroundColor
$objForm = New-Object System.Windows.Forms.Form
$objForm.Text = "History"
$objForm.Size = New-Object System.Drawing.Size(300,300)
$objForm.StartPosition = "CenterScreen"
$objForm.FormBorderStyle = "SizableToolWindow"

$objForm.KeyPreview = $True
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Enter")
    {$psISE.CurrentPowerShellTab.CommandPane.InsertText($objlb.Selecteditem);$objForm.Close()}})
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Escape")
    {$objForm.Close()}})

$objForm.Add_Resize({$objlb.Size = New-Object System.Drawing.Size(($objForm.width-15),$objForm.height) })

$objlb = New-Object System.Windows.Forms.ListBox
$objlb.Location = New-Object System.Drawing.Size(0,0)
$objlb.Size = New-Object System.Drawing.Size(($objForm.width-15),$objForm.height)
$objlb.backcolor= [system.drawing.color]::fromargb($bg.a, $bg.r, $bg.g, $bg.b)
$objlb.forecolor = [system.drawing.color]::fromargb($fg.a, $fg.r, $fg.g, $fg.b)
$objlb.Add_DoubleClick({$psISE.CurrentPowerShellTab.CommandPane.InsertText($objlb.Selecteditem);$objForm.Close()})
$objForm.Controls.Add($objlb)

get-history | foreach {$objlb.items.add($_.commandline)} | out-null

$objForm.Topmost = $True

$objForm.Add_Shown({$objForm.Activate()})
[void] $objForm.ShowDialog()
}
$psISE.CurrentPowerShellTab.AddOnsMenu.Submenus.Add(‘Show history’,{Copy-HistoryItem},"F7")

 

This even adds the ability to hit the F7 key to bring up the window.

You can resize the window to your liking and just double click on the item you want and it will place the command in the command window for you.

This was inspired by some code i received by a Community member, Kartek Bielawski, over in the MS Forums for Powershell. Most of the code was taken from a Technet Powershell Tip of the Week post on how to use forms.

Powershell ISE Color Customizer


NOTE: PowerShell V3 has its own color editor now!

I created a simple color customizer that can be found over here

http://jrich523.atspace.com/index.htm

This is version 1, and if I find there is enough interest (by posts here) I’ll gladly add to it. Already have some ideas on things I can do to make this way cooler, but have wasted enough time on something I’ll really only use once :)

Let me know what you think and if you think its a useful tool or if you have any idea’s you’d like to see added.

Justin

 

Things I could add:

  • predefined themes (if you come up with a cool color scheme let me see it!)
  • better color picker using something like one of these
  • add other things like font face and type ( I suggest you use Verdana)
  • other nifty things like setting multiple items at once, picking the color by selecting the object.. etc etc

Fast Large File Copy


I work with some fairly large files, one of which is a 1TB+ database backup that I need to move weekly, which as you can imagine, takes a little time. In the past I’ve used ESEUTIL, the Exchange DB tool (/y was a move command) which did an unbuffered copy and used stream reader/writer rather than the windows copy commands. If you care about the inner workings the performance team gives a good overview of this.

http://blogs.technet.com/b/askperf/archive/2007/05/08/slow-large-file-copy-issues.aspx

They have a great blog that I need to spend more time reading! Anyways, I wanted to try this myself so I first started with writing a small C++ application and quickly learned my C is a tad out of practice. I then moved over to .NET thinking that most of this stuff is just a wrapper to what I’d be accessing in C and the overhead for .NET will be minimal when trying to transfer a large file.

What better way to use .NET than to do it with Powershell! As I got in to writing the function I figured I’d need a comparison to a normal copy so I use the normal PowerShell Copy-Item cmdlet and to my surprise its FAST. I typically check performance by just looking at the cards % utilization in task manager, figure its close enough. I just ran a move between two of my servers and was holding above 45% utilization with a fair deal of time spent around or at 50% of a 1gb connect which is a good deal higher than using ESEUTIL which would hold high 20’s. Obviously windows copy is FAR slower and other tools I’ve tried in the past were no better than ESEUTIL.

so the point of this post… use PowerShell more :)