How to bind the -v parameter of sqlcmd from a PowerShell

Question

The following code, setting the -v parameter directly is working

$sqlcmd = @(Resolve-Path "$env:ProgramFiles\Microsoft SQL Server\*\Tools\binn\SQLCMD.EXE")[0]

$path1 = 'D:\somescript.sql'

& $sqlcmd -b -S NB-BK\SQLEXPRESS -d BK_Prod -U sa -P mypassword -l 180 -i $path1 -v Mandant=1 SAPMandant="009" SAPEinrichtung="0001"

But I need a way to set these values from a PowerShell variable.

I tried:

$sqlcmd = @(Resolve-Path "$env:ProgramFiles\Microsoft SQL Server\*\Tools\binn\SQLCMD.EXE")[0]

$path1 = 'D:\somescript.sql'

$sqlcmdparameters = 'Mandant=1 SAPMandant="009" SAPEinrichtung="0001" '
& $sqlcmd -b -S NB-BK\SQLEXPRESS -d BK_Prod -U sa -P mypassword -l 180 -i $path1 -v $sqlcmdparameters

I found this on SO, but it didn't help me.

bernd_k · Accepted Answer · 2012-08-25 17:22:10Z

2

I found the following solution for PowerShell V2

$sqlcmd = @(Resolve-Path "$env:ProgramFiles\Microsoft SQL Server\*\Tools\binn\SQLCMD.EXE")[0]
$path1 = 'D:\somescript.sql'
$cmdparameters = @(
    'Mandant=1',
    'SAPMandant="009"'
)

& $sqlcmd -b -S NB-BK\SQLEXPRESS -d BK_Prod -U sa -P mypassword -l 180 -i $path1 -v $cmdparameters

edited Aug 25, 2012 at 17:22

answered Aug 25, 2012 at 16:25

bernd_k

12.1k7 gold badges49 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chad Miller Over a year ago

Interesting passing an array works. Calling legacy commands in V1/V2 get's better in Powershell V3 with the -% operator. Keith Hill has blog post with sqlcmd -v example using V3's -% operator: rkeithhill.wordpress.com/2012/01/02/…

Chad Miller · Accepted Answer · 2012-08-25 15:46:06Z

1

Try using start-process. That's what I do for Powershell command-line parsing issues. First try invoke operator (&) and if that doesn't work wrap it in a start-process call.

$tempFile = [io.path]::GetTempFileName()

$exitCode = (start-process -FilePath $sqlcmd -ArgumentList @"
-b -S NB-BK\SQLEXPRESS -d BK_Prod -U sa -P mypassword -l 180 -i $path1 -v $sqlcmdparameters
"@ -Wait -RedirectStandardOutput $tempFile -NoNewWindow -Passthru).ExitCode

When using start-process typically you'll need to capture exitcode and also redirect output to temp file in get back the results. I'll then have some code to check $exitcode and cleanup temp file in try/catch/finally block

answered Aug 25, 2012 at 15:46

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

1 Comment

bernd_k Over a year ago

+1 for a solution that works. I accept your answer, because it shows how to use a string with all the parameter mapping. I found a more direct solution to the problem. See my answer.

Collectives™ on Stack Overflow

How to bind the -v parameter of sqlcmd from a PowerShell

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related