Monthly Archives: March 2011

Invoke parameterized stored procedure with PowerShell

Posted by timvw on March 21, 2011 None comments

Here is a quick snippet that demonstrates how you can invoke a parametrized stored procedure with PowerShell:

$CreateTraceCommand = $SqlConnection.CreateCommand();
$CreateTraceCommand.CommandType = [System.Data.CommandType] "StoredProcedure";
$CreateTraceCommand.CommandText = "sp_trace_create";
$TraceIdParameter = $CreateTraceCommand.Parameters.Add("@traceid", [System.Data.SqlDbType] "Int");
$TraceIdParameter.Direction = [System.Data.ParameterDirection] "Output";
$OptionParameter = $CreateTraceCommand.Parameters.Add("@options", [System.Data.SqlDbType] "Int");
$OptionParameter.Value = [int] 2;
$TraceFileParameter = $CreateTraceCommand.Parameters.Add("@tracefile", [System.Data.SqlDbType] "NVarChar");
$TraceFileParameter.Value = [string] "c:\temp\test";
[void] $CreateTraceCommand.ExecuteNonQuery();
$TraceId = $TraceIdParameter.Value;
Write-Host "You created a trace with id: $TraceId";

Global hotkeys for iTunes on windows

Posted by timvw on March 16, 2011 1 comments

These days i use iTunes as media player. Unlike winamp this program does not seem to support system-wide hotkeys to control playback. This is the place where a productivity tool like AutoHotkey comes into the rescue ;) .

I have configured the following keys:

  • Move to next song: [Ctrl] + [Alt] + [Right]
  • Move to previous song: [Ctrl] + [Alt] + [Left]
  • Toggle play/pause: [Ctrl] + [Alt] + p
  • Turn volume up: [Ctrl] + [Alt] + [Up]
  • Turn volume down: [Ctrl] + [Alt] + [Down]
^!right::
DetectHiddenWindows , On
ControlSend , ahk_parent, ^{right}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

^!left::
DetectHiddenWindows , On
ControlSend , ahk_parent, ^{left}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

^!p::
DetectHiddenWindows , On
ControlSend , ahk_parent, {space}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

^!up::
DetectHiddenWindows , On
ControlSend, ahk_parent, ^{UP}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

^!down::
DetectHiddenWindows , On
ControlSend, ahk_parent, ^{DOWN}, iTunes ahk_class iTunes
DetectHiddenWindows , Off
return

Navigation to the webpage was canceled

Posted by timvw on March 16, 2011 1 comments

Earlier today i ran into the ‘Navigation to the webpage was canceled’ when i tried to open a CHM file:

Navigating to the file properties allows me to unblock the file:

And now i can happily use the CHM file:

Consume locally build software without overloading your $PATH

Posted by timvw on March 12, 2011 None comments

How do you consume locally build software? For a while now i have used the following approach:

  • Create a ~/bin folder
  • Add that ~/bin folder to my $PATH
  • Add symlinks from binary to ~/bin (ln -s ~/src/git-tfs/GitTfs.Vs2010/bin/debug/git-tfs.exe git-tfs)

Please let me know about your strategy..

Some PowerShell functions to work with SSIS packages

Posted by timvw on March 12, 2011 1 comments

Here are some powershell functions (using XPath) that come in handy when working with SSIS packages:

function FindConnectionManagerNames {
	param($fileName)
	$xml = 1 (Get-Content $fileName);
	$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
	$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
	$xml.SelectNodes("//DTS:ConnectionManager/DTS:Property[@DTS:Name='ObjectName']", $ns) | Foreach { $_."#text"; }
}

function GetConnectionManagerConnectionString {
	param($fileName, $connectionManagerName)
	$xml = 1 (Get-Content $fileName);
	$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
	$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
	$xml.SelectSingleNode("//DTS:ConnectionManager[DTS:Property='$connectionManagerName']/DTS:ObjectData/DTS:ConnectionManager/DTS:Property[@DTS:Name='ConnectionString']", $ns)."#text";
}

function FindVariables {
	param($fileName)
	$xml = 1 (Get-Content $fileName);
	$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
	$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
	$xml.SelectNodes("//DTS:Variable/DTS:Property[@DTS:Name='ObjectName']", $ns) | Foreach { $_."#text"; }
}

function GetVariable {
	param($fileName, $variableName)
	$xml = 1 (Get-Content $fileName);
	$ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
	$ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
	$xml.SelectSingleNode("//DTS:Variable[DTS:Property='$variableName']/DTS:VariableValue", $ns)."#text";
}

Dynamic scriptblock with PowerShell

Posted by timvw on March 12, 2011 1 comments

Earlier this week i tried to run a command on a remote computer but it did not seem to work:

$name = 'tim';
$computer = 'localhost';
Invoke-Command -ComputerName $computer -ScriptBlock { Write-Host "Hello $name" }

Because powershell serialises the { Write-Host “Hello $name” } as a string this ends up at the remote computer as { Write-Host “Hello $null” }. In order to send our ‘dynamic’ command string over the wire we have to make sure it is serialised correctly:

$scriptBlock = $executioncontext.InvokeCommand.NewScriptBlock("Write-Host `"Hello $name`"");
Invoke-Command -ComputerName $computer -ScriptBlock $scriptBlock;

Some tips for .Net developers using git on cygwin

Posted by timvw on March 12, 2011 1 comments

Here are some tips that i want to share with fellow .Net developers that use git on cygwin.

First of all i defined some aliases in my ~/.bashrc:

# open explorer in the current working directory
alias explorer='explorer.exe "`cygpath -aw \"$PWD\"`"'

# invoke MSBuild
alias msbuild='/cygdrive/c/Windows/Microsoft.NET/Framework/v4.0.30319/MSBuild.exe'

Because i don’t like the TFS source control story i use git-tfs. As a .Net developer you want to add the following to your .git/info/exclude file:

#OS junk files
[Tt]humbs.db
*.DS_Store

#Visual Studio files
*.[Oo]bj
*.exe
*.pdb
*.user
*.aps
*.pch
*.vspscc
*.vssscc
*_i.c
*_p.c
*.ncb
*.suo
*.tlb
*.tlh
*.bak
*.[Cc]ache
*.ilk
*.log
*.lib
*.sbr
*.sdf
ipch/
obj/
[Bb]in
[Dd]ebug*/
[Rr]elease*/
Ankh.NoLoad

#Tooling
_ReSharper*/
*.resharper
[Tt]est[Rr]esult*

#Subversion files
.svn

Whenever i work online i usually run these two commands consecutively: git -a -m “commit message” and git-tfs checkin -m “commit message”. Here is a small ~/bin/commit script that combines these:

#!/bin/bash
git commit -a -m "$1";
git-tfs checkin -m "$1";

Using WinMerge as difftool on cygwin/git

Posted by timvw on March 12, 2011 None comments

Last couple of weeks i have been using Git on cygwin and i got very satisfying results out of it. One thing that i wanted to tweak was the ability to use WinMerge to compare files. Here is how i do it:

Here is my ~/.gitconfig:

[user]
	name = Tim Van Wassenhove
	email = git@timvw.be
	
1	
	external = "~/bin/git-diff-wrapper.sh"

Here is my ~/bin/git-diff-wrapper.sh (it uses cygpath to translate the paths):

#!/bin/sh
"/cygdrive/c/Program Files/WinMerge/WinMergeU.EXE" /e /ub /dl other /dr local "`cygpath -aw $1`" "`cygpath -aw $2`" "`cygpath -aw $5`"

And now we’re good to go ;)

Get/SetVariable value from SSIS VariableDispenser

Posted by timvw on March 8, 2011 1 comments

Here is some code that allows you to Get/Set a variable (object) value from/on the VariableDispenser in an SSIS package:

public static T GetVariable<T>(this VariableDispenser variableDispenser, string scopedVariableName)
{
 Variables variables = null;

 try
 {
  variableDispenser.LockForRead(scopedVariableName);
  variableDispenser.GetVariables(ref variables);
  return (T)variables[0].Value;
 }
 finally
 {
  if (variables != null) variables.Unlock();
 }
}

public static void SetVariable<T>(this VariableDispenser variableDispenser, string scopedVariableName, T value)
{
 Variables variables = null;

 try
 {
  variableDispenser.LockForWrite(scopedVariableName);
  variableDispenser.GetVariables(ref variables);
  variables[0].Value = value;
 }
 finally
 {
  if (variables != null) variables.Unlock();
 }
}

Consume custom SSIS tasks without GAC installation

Posted by timvw on March 5, 2011 None comments

For a while i thought that in order to consume a custom SSIS task you had to install the assembly in the GAC. Now i know better ;)

For the designer (BIDS) you have to copy the files to:

C:\Program Files\Microsoft SQL Server\100\DTS\Tasks
C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents

For the runtime (BIDS) you have to copy the files to:

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

And for dtexec you have to copy the files to:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn

Here is a small powershell script that gives you the paths where you want to copy your assembly to:

# Lookup Tasks, PipelineComponents and DtExec paths
$DtsPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup').SQLPath;
$DtsTasksPath = (Resolve-Path "$DtsPath\Tasks");
$DtsPipelineComponentsPath = (Resolve-Path "$DtsPath\Pipelinecomponents");
$DtExecPath = (Resolve-Path "$DtsPath\Binn");

# Lookup VS2008/Bids path
$Vs2008Path = (Get-ItemProperty 'HKLM:\Software\Microsoft\VisualStudio\9.0').InstallDir;
$Vs2008PrivateAssembliesPath = (Resolve-Path "$Vs2008Path\PrivateAssemblies");