How to execute sqlcmd from powershell?

Question

I have a string in powershell, which contains a native sqlcmd command. The command itself can be executed successfully in cmd.exe. I have difficulty in executing them in powershell. Anyone can help? Thanks.

This is sql.sql

select @@servername
go
select @@servicename

This is the result when I execute the sqlcmd command from cmd.exe

C:\Users\test>sqlcmd -S "(local)\instance1" -U a -P a -i "c:\temp\sql.sql"

--------------------------------------------------
thesimpsons\INSTANCE1

(1 rows affected)

--------------------------------------------------
INSTANCE1

(1 rows affected)

C:\Users\test>

This is the powershell script to call the sqlcmd command.

$sql = @"
sqlcmd -S "(local)\instance1" -U a -P a -i "c:\temp\sql.sql"
"@

Invoke-Command $sql

When I execute this powershell script, I got the following error.

PS C:\TEMP> $sql = @"
sqlcmd -S "(local)\instance1" -U a -P a -i "c:\temp\sql.sql"
"@

Invoke-Command $sql
Invoke-Command : Parameter set cannot be resolved using the specified named parame
ters.
At line:5 char:15
+ Invoke-Command <<<<  $sql
    + CategoryInfo          : InvalidArgument: (:) [Invoke-Command], ParameterBin 
   dingException
    + FullyQualifiedErrorId : AmbiguousParameterSet,Microsoft.PowerShell.Commands 
   .InvokeCommandCommand

Answer 1

To call a Win32 executable you want to use the call operator & like this:

& sqlcmd -S "(local)\instance1" -U a -P a -i "c:\temp\sql.sql"