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" 

Answer 2

You could also stop using the external 'SQLCMD.EXE' and use the Invoke-Sqlcmd cmdlet instead:

Invoke-Sqlcmd is a SQL Server cmdlet that runs scripts that contain statements from the languages (Transact-SQL and XQuery) and commands that are supported by the sqlcmd utility

Just open the 'sqlps' utility and run

Invoke-Sqlcmd -InputFile "C:\temp\sql.sql" 

Please see Running SQL Server PowerShell

You can also load the SQL Server snap-ins manually in PowerShell before using 'Invoke-Sqlcmd';
for MS SQL Server 2012 you can do that by running
Import-Module SqlPs