Executing Sql scripts from powershell

Realizing I still have 6 Tips'n'Tricks articles nearly ready to go to follow up on the series, but none of them are finished, I thought I'd give you another powershell trick.

One of the main issue on windows when interop has to be achieved between powershell and cmd is the issue of CreateProcess trying sometimes to be too smart about quotes. That's why powershell supports being called with a base64 encoded parameter as command line parameters, and why attempts to use variables when calling sqlcmd.exe fail miserably.

So I wrote a quick hack script that concatenates data in a nice batch file and then executes the batch file. I've used it on several projects by now. Anyone wanting to make it more fancy don't hesitate.

function Execute-SqlFile($file, [string]$Server, [string]$dbName, [hashtable]$variables, [switch]$WindowsAuthentication=$true, [string]$Username, [string]$Password) {
        $batch = (join-Path (cat env:TEMP) "exec_sql.bat")
        write-Host Connecting to $Server
    
        $output = (join-Path $env:TEMP "output_sql.txt")
        if (test-Path $batch) {
           Remove-Item $batch -force
        }
        $data = ""
        if (test-Path $batch) { Remove-Item $batch }
        $data += "sqlcmd -S $Server"
        
        if ($WindowsAuthentication) {
            $data += ' -E'
        } else {
            $data += " -U $Username -P $Password"
        }
        if ($dbname) {
            $data += " -d $dbName"
        }
        
        if ($variables -and $variables.Count -gt 0) {
            $data += ' -v '
            $isFirst = $true
            foreach($key in $variables.keys) {
            
                if (! $isFirst) { $data += ' ' } else { $isFirst=$false }
        
                $val = $variables[$key]
                $data += "$key="
                $data += "`"$val`""
                
            }
        }
        $data += " -i `"$file`""
        
        
        $data += " -o `"$output`""
        
        $data | Add-Content $batch -force
        
        cmd /c (Resolve-Path $batch)
        
        gc $output

    }

Which lets you call it like so:

PS C:\WINDOWS\> Execute-SqlScript "local.sql" "dbserver" @{key="value"; key2="value2"}

Enjoy!

Technorati Tags: powershell, msh, monad, sql

Install windows components on a locked-down machine

More and more companies set their users as non admin, even with XP. The so-called locking down tries to protect the user and the network against anything only an administrator should do.

But there's also group policy that directs what gets shown in windows, which software gets activated, etc. In my case, the policy doesn't let you access the Add/Remove Windows Components section.

Being an administrator on the machine, I could go and change the registry to deny read permission to whichever account is being used for policies. That's a bit drastic and I wouldn't recommend it.

But thanks to the complexity and myriad of options available to group policy, sometimes you can get away with easier things.

To add or remove windows components, open PowerShell and type the following.

PS C:\WINDOWS> sysocmgr /i:$env:windir\inf\sysoc.inf

Or for people still using cmd

C:\WINDOWS> sysocmgr /i:%WINDIR%\inf\sysoc.inf

I'd rather we didn't have to play catch and seek with network administrators, but you have to do what you have to do to get the job done sometimes.

Technorati Tags: PowerShell, POSH, Group Policy

PowerShell arguments and encodings

Well, after fighting for an hour or two, I finally read a post explaining why my quotes were not passed around when invoking a script. Now that I'm using the -EncodedCommand attribute, everything works fine!

As the guys on the powershell team wrote about encoding conversion, I thought I'd provide two quick ones for those needing it, Url Encoding and Base64. Don't hesitate to add to your profile.

Note that I'm using LoadWithPartialName because I'm lazy and it still works on .net 2. Replace with proper LoadFrom or Load as needed.

[System.Reflection.Assembly]::LoadWithPartialName("System.Web") | out-null
function ConvertTo-UrlEncodedString([string]$dataToConvert)
{
    begin {
        function EncodeCore([string]$data) { return [System.Web.HttpUtility]::UrlEncode($data) }
    }
    process { if ($_ -as [string]) { EncodeCore($_) } }
    end { if ($dataToConvert) { EncodeCore($dataToConvert) } }
}
function ConvertFrom-UrlEncodedString([string]$dataToConvert)
{
    begin {
        function DecodeCore([string]$data) { return [System.Web.HttpUtility]::UrlDecode($data) }
    }
    process { if ($_ -as [string]) { DecodeCore($_) } }
    end { if ($dataToConvert) { DecodeCore($dataToConvert) } }
}
function ConvertTo-Base64EncodedString([string]$dataToConvert)
{
    begin {
        function EncodeCore([string]$data) { return [System.Convert]::ToBase64String([System.Text.Encoding]::Unicode.GetBytes($data)) }
    }
    process { if ($_ -as [string]) { EncodeCore($_) } }
    end { if ($dataToConvert) { EncodeCore($dataToConvert) } }
}
function ConvertFrom-Base64EncodedString([string]$dataToConvert)
{
    begin {
        function DecodeCore([string]$data) { return [System.Text.Encoding]::Unicode.GetString([System.Convert]::FromBase64String($data)) }
    }
    process { if ($_ -as [string]) { DecodeCore($_) } }
    end { if ($dataToConvert) { DecodeCore($dataToConvert) } }
}

 

Technorati Tags: msh, posh, powershell, scripting

Knowing which aliases are defined

[Update: Replaced -eq by -match as suggested on the PowerShell blog. Thanks guys, humbling to have you reading this blog. Tagging really does have a use :) ]

One of the main struggles I have when writing my PowerShell scripts for public consumption is that I never remember which aliases I can use. And eventually I'd like to naturally type gci instead of ls, but old habits die hard.

Here's a little function I have in my profile to get the list of all aliases defined for a command.

function Get-AliasShortcut([string]$CommandName) {
    ls Alias: | ?{ $_.Definition -match $CommandName }
}
Set-Alias gas Get-AliasShortcut

Which you can then execute as such:

6# gas get-childitem

CommandType     Name                                                Definition
-----------     ----                                                ----------
Alias           gci                                                 Get-ChildItem
Alias           ls                                                  Get-ChildItem
Alias           dir                                                 Get-ChildItem

Technorati Tags: powershell