CODEWITHSUNDEEP
powershellpowershell-3.0sqlcmd
craig
craig

Reputation: 26262

Invoke-Expression argument splatting with SqlCmd

Assuming:

$args = @()
$args += '-E'
$args += "-S 'SERVER'"
$args += "-d 'DATABASE'"
$args += "-Q 'SELECT GetDate() NOW'"

Invoke-Expression "sqlcmd $($args -join ' ')" produces:

NOW
-----------------------
2015-07-27 16:48:26.387

(1 rows affected)

My attempts at 'splatting' fail.

Invoke-Expression "sqlcmd" @args and Invoke-Expression 'sqlcmd' @args and Invoke-Expression sqlcmd @args produce:

`Error: 7/27/2015 4:53:00 PM:
At C:\Users\...\default.ps1:34 char:3 +   Invoke-Expression "sqlcmd" @args +   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~ [<<==>>]

Exception: A positional parameter cannot be found that accepts argument '-E'.

& sqlcmd @args and & 'sqlcmd' @args produce:

Sqlcmd: '-S 'SERVER'': Unexpected argument. Enter '-?' for help.

What is the correct splatting syntax?

** edit **

Similar pattern (source - Differential backups using 7-zip and PowerShell):

$args = @()
$args += 'a'
$args += "-tZIP"
$args += 'C:\Users\x\Desktop\archive.zip'
$args += 'C:\Users\x\Desktop\a.txt'

& '7z' @args

Results:

7-Zip [64] 9.38 beta  Copyright (c) 1999-2014 Igor Pavlov  2015-01-03

Scanning

Updating archive C:\Users\x\Desktop\archive.zip

Compressing  a.txt

Everything is Ok

Kernel  Time =     0.031 =   63%
User    Time =     0.000 =    0%
Process Time =     0.031 =   63%    Virtual  Memory =      9 MB
Global  Time =     0.049 =  100%    Physical Memory =      6 MB

What's the difference?

Upvotes: 0

Views: 2127

Answers (2)

Jower
Jower

Reputation: 575

Splatting works with powershell functions and CmdLets. SqlCmd does not support that. You could write a wrapper for SqlCmd and get it to work that way. See the following example.

function SqlCmdWithSplatting
{
param
(
    [string]$server,
    [string]$database,
    [string]$query
)
sqlcmd -E -S $server -d $database -Q $query
}

# Now call that with splatting

$myargs = @{Server="(localdb)\ProjectsV12";database="John";Query="select * from Table1"}

SqlCmdWithSplatting @myargs

    Id         
-----------
          1
          2
          3

Upvotes: 1

JPBlanc
JPBlanc

Reputation: 72610

First : I would avoid to give values to $args which is a Powershell automatic variable reserved for script parameters.

Second : here is the way I use to do it :

$scriptblock = {fullpath\sqlcmd -S `"(local)\instance1`" <# comment option -S #>`
                                -U a `
                                -P a `
                                -i `"c:\temp\sql.sql`" }
Invoke-Command -ScriptBlock $scriptBlock

You can then use $args variable inside the script block and even start it remotly.

$scriptblock = {fullpath\sqlcmd -S `"(local)\instance1`" <# comment option -S #>`
                                -U a `
                                -P a `
                                -i `"$($args[0])`" }
Invoke-Command -ScriptBlock $scriptBlock -argumentList "c:\temp\sql.sql" -computer "remote1"

Remark :One line per option allow to comment each param, but be careful not to forget a "`" and no space after them when they are at the end of the line.

Upvotes: 0

Related Questions