A stabilo marker of two s attached to one anotherSerialSeb

Executing Sql scripts from powershell

Edit

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: , , ,

Ads

Comment