Exploring window functions

Posted by timvw on December 10, 2010 No comment

Here is a sample query that allows you to explore the behavior of various aggregate window functions:

WITH
    [Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
  , [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
  , [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
  , [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
  -- Build numbers from 1 to 1000
  , [Numbers] AS ( SELECT TOP(1000) ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
SELECT
    [Value]
  , ROW_NUMBER() OVER(PARTITION BY [Value] / 100 ORDER BY [Value] % 10) AS [RowNumber]
  , RANK() OVER(PARTITION BY [Value] / 100 ORDER BY [Value] % 10) AS [Rank]
  , DENSE_RANK() OVER (PARTITION BY [Value] / 100 ORDER BY [Value] % 10) AS [DenseRank]
  , NTILE(4) OVER (PARTITION BY [Value] / 100 ORDER BY [Value] % 10) AS [Tile100]
FROM
    [Numbers]
ORDER BY
    [Value];

Convert AAC/M4A files to MP3 with VLC and PowerShell

Posted by timvw on November 20, 2010 No comment

Here is a way to convert your AAC/M4A files to MP3 using VLC media player:

vlc.exe -I dummy old.m4a :sout=#transcode{acodec=$codec,vcodec=dummy}:standard{access=file,mux=raw,dst=new.mp3} vlc://quit

Let’s wrap this command in a bit of PowerShell:

function ConvertToMp3([switch] $inputObject, [string] $vlc = 'C:\Program Files\VideoLAN\VLC\vlc.exe') {
    PROCESS {
        $codec = 'mp3';
        $oldFile = $_;
        $newFile = $oldFile.FullName.Replace($oldFile.Extension, ".$codec");
        &"$vlc" -I dummy "$oldFile" ":sout=#transcode{acodec=$codec,vcodec=dummy}:standard{access=file,mux=raw,dst=`'$newFile`'}" vlc://quit | out-null;
        #Only remove source files when you are sure that the conversion works as you want
        #Remove-Item $oldFile;
    }
}

And now we can use this function for *all* m4a files in a given folder:

function ConvertAllToMp3([string] $sourcePath) {
    Get-ChildItem "$sourcePath\*" -recurse -include *.m4a | ConvertToMp3;
}

Using the function is as easy as:

ConvertAllToMp3 'C:\Users\timvw\Music';

Load all script files at PowerShell startup

Posted by timvw on November 17, 2010 No comment

These days i have quite some scripts files that i want to be loaded each time i launch PowerShell. Windows PowerShell Profiles teaches me where i should store my $profile. Here is what it looks like:

# Lookup powershell scripts location
$UserProfile = (Get-ChildItem Env:UserProfile).Value;
$ScriptFolder = "$UserProfile\My documents\WindowsPowerShell";

# Source all .ps1 files in PowerShell profile folder
Get-ChildItem $ScriptFolder -name -include '*.ps1' -exclude 'profile.ps1'
 | foreach {  (. "$ScriptFolder\$_") };

# Configure environment for VS2010
SetVS2010;

Configure Visual Studio 2010 environment in PowerShell

Posted by timvw on November 17, 2010 No comment

Instead of using the “Visual Studo Command Prompt (2010)” i wanted to use PowerShell instead. I found this post which does it for VS2008. Extending it for VS2010 was pretty easy:

function SetVS2008()
{
    $vs90comntools = (Get-ChildItem env:VS90COMNTOOLS).Value
    $batchFile = [System.IO.Path]::Combine($vs90comntools, "vsvars32.bat")
    Get-Batchfile $BatchFile
    [System.Console]::Title = "Visual Studio 2008 Windows PowerShell"
}

function SetVS2010()
{
    $vs100comntools = (Get-ChildItem env:VS100COMNTOOLS).Value
    $batchFile = [System.IO.Path]::Combine($vs100comntools, "vsvars32.bat")
    Get-Batchfile $BatchFile
    [System.Console]::Title = "Visual Studio 2010 Windows PowerShell"
}

function Get-Batchfile($file)
{
    $cmd = "`"$file`" & set"
    cmd /c $cmd | Foreach-Object {
        $p, $v = $_.split('=')
        Set-Item -path env:$p -value $v
    }
}

Update ConfigurationStrings in SSIS package with PowerShell

Posted by timvw on November 11, 2010 2 comments

Here are some functions that allow you to update ConfigurationStrings in a SSIS package (dtsx) using PowerShell:

function UpdateConfigurationStrings($file)
{
 $xml = 1 (Get-Content $file);
 $ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
 $ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
 UpdateConfigurationNodes $xml $ns;
 Set-Content $file $xml.OuterXml;
}

function UpdateConfigurationNodes($xml, $ns)
{
 $selectConfigurationNodes = "//DTS:Configuration";
 $xml.SelectNodes($selectConfigurationNodes, $ns) | foreach { UpdateConfigurationNode $_ $ns; };
}

function UpdateConfigurationNode($configurationNode, $ns)
{
 $selectConfigurationString = "./DTS:Property[@DTS:Name='ConfigurationString']";
 $configurationStringNode = $configurationNode.SelectSingleNode($selectConfigurationString, $ns);

 $oldConfigurationStringValue = $configurationStringNode.'#text';
 $newConfigurationStringValue = GetNewConfigurationStringValue $oldConfigurationStringValue;
 $configurationStringNode.'#text' = "$newConfigurationStringValue";
}

function GetNewConfigurationStringValue($oldConfigurationStringValue)
{
 # implement some logic to determine new value
 return "new value";
}

Get variable value from variable with PowerShell

Posted by timvw on November 11, 2010 No comment

Sometimes you only know at runtime in which variable a certain value is stored. Let me clarify with an example:

$tim = 30;
$evy = 24;
$name = Read-Host "Enter your name";

In essence, if $name equals tim we want to use $tim and if $name equals evy we want to use $evy. This can be achieved with Get-Variable:

$age = Get-Variable $name -valueOnly;
Write-Host "Your age is $age";

Modify a text file with PowerShell

Posted by timvw on November 7, 2010 1 comment

A while ago i wanted to update a connection string in a configuration file. My first attempt was the following:

Get-Content $File
| Foreach { $_ -Replace "Source>(.*?)<", "Source>$New<" }
| Set-Content $File;

Running this scripts leads to the following error: “Set-Content : The process cannot access the file because it is being used by another process.” In order to avoid this you can complete the read operation before you start writing as following:

(Get-Content $File)
| Foreach { $_ -Replace "Source>(.*?)<", "Source>$New<" }
| Set-Content $File;

Invoke PowerShell script from MSBuild

Posted by timvw on November 7, 2010 No comment

Here is a small MSBuild target that allows you to invoke a PowerShell script, eg: powershell.exe & ‘script.ps1′ -SomeParam ‘x’

<Target Name="InvokePowerShell">
 <PropertyGroup>
  <PowerShellCommand>"$(PowerShellTool)" "&amp; '$(ScriptFile)' -SomeParam '$(SomeParam)' "</PowerShellCommand>
 </PropertyGroup>
 <Exec Command="$(PowerShellCommand)" />
</Target>

Build your solution with Visual Studio from MSBuild

Posted by timvw on November 7, 2010 No comment

Unfortunately MSBuild and BIDS Helper are not able to build an .asdatabase from our Analysis Services project (.dwproj). Here is a task which invokes Visual Studio to build such a solution:

<Target Name="DevEnvBuild">
 <Error Condition="'$(SolutionFile)'==''" Text="Missing SolutionFile" />
 <PropertyGroup>
  <DevEnvTool Condition="'$(DevEnvTool)'==''">C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe</DevEnvTool>
  <DevEnvSwitch Condition="'$(DevEnvSwitch)'==''">Build</DevEnvSwitch>
  <DevEnvBuildCommand>"$(DevEnvTool)" "$(SolutionFile)" /$(DevEnvSwitch)</DevEnvBuildCommand>
 </PropertyGroup>
 <Exec Command="$(DevEnvBuildCommand)" />
</Target>

Deploying a Database Project does not create artefacts for custom schemas

Posted by timvw on November 1, 2010 No comment

Last week we noticed that the artefacts for a custom schema in our Database Project were not created (or updated). It took us a while to figure out what was wrong. When you add a script via the ‘Add Existing item’ menu in VS2010 the file’s Build Action is set to Not in Build for some unknown reason. As soon as we changed the value to Build and Deployed again our artefacts were created.