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

related: http://stackoverflow.com/questions/1015038/powershell-sqlcmd — Ruben Bartelink, Jan 31 '13 at 12:35

score 26 · Accepted Answer · answered Mar 15 '12 at 04:43

26

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"

answered Mar 15 '12 at 04:43

Andy Arismendi

50,577
16
107
124

score 20 · Answer 2 · answered Jan 28 '15 at 17:09

20

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

answered Jan 28 '15 at 17:09

user4531

2,525
7
30
38

1

While Andi Arismendi's answer is a good one, and directly answers executing of the sqlcmd executable, I believe this one should be the accepted answer. If you're running sqlcmd scripts through powershell, it will save you time, effort, and keystrokes to simply use Invoke-Sqlcmd. – ascary Nov 09 '15 at 15:39
6

1. Invoke-Sqlcmd doesn't implement all the commands that are available with SQLCMD. 2. Invoke-Sqlcmd is not included in the base Powershell install – DonBecker Jul 15 '16 at 22:01
Invoke-Sqlcmd adds a semicolon to the end of stored procedures, which causes issues with comparison tools. See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/74d8acd4-9788-48e5-93bc-fa165a573ee2/powershell-invokesqlcmd-adding-semicolon?forum=sqltools and https://www.reddit.com/r/PowerShell/comments/5v29o2/options_for_executing_sql_server_script/ – kevinpo Apr 13 '17 at 13:42
Not to mention, Invoke-Sqlcmd wasn't supported in Powershell 2.0. – tbradt May 17 '17 at 17:46

JPBlanc · Answer 3 · 2020-04-06T13:24:33.520

This is how I build some externals command in my scripts

$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 it 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 :

This allow to comment each param.

Be careful not to forget a "`" and no space after them where they are at the end of the line.

score 3 · Answer 4 · answered Mar 15 '12 at 04:42

3

Use Invoke-Expression rather than Invoke-Command

answered Mar 15 '12 at 04:42

Andrew Shepherd

44,254
30
139
205

I don't see why people try to use Invoke-Expression when calling Win32 executables... This is what the call operator is for. – Andy Arismendi Mar 15 '12 at 04:44
@Andy - Invoke-Expression allows you to generate the entire command as a string first, which could be useful depending upon the circumstances. See http://stackoverflow.com/questions/6604089/dynamically-generate-command-line-command-then-invoke-using-powershell – Andrew Shepherd Mar 15 '12 at 04:49
1

Yep, but that's not happening here. Only use Invoke-Expression when there is an expression that needs to be evaluated. – Andy Arismendi Mar 15 '12 at 04:50

mjolinor · Answer 5 · 2012-03-15T15:34:17.653

3

The first positional parameter of invoke-command is -scriptblock, and it expects a script block argument. To take advantage of a here-string to build the command and then run it with invoke-command, you need to convert the here-string to a script block:

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

 Invoke-Command ([scriptblock]::create($sql))

edited Mar 15 '12 at 15:34

answered Mar 15 '12 at 10:53

mjolinor

66,130
7
114
135

All this is going to do is return the value in $sql, it will not actually execute it (at least not on my machine). – Mar 15 '12 at 14:37

score 0 · Answer 6 · answered Apr 18 '15 at 02:50

0

Both instance name and username should be fully qualified

<domain_name>\Instanc_name and <domai_name>\Username. Only your instance name is correctly scripted.

answered Apr 18 '15 at 02:50

user2063329

443
2
5
15

score 0 · Answer 7 · edited Sep 28 '17 at 15:43

This is what worked for me for using sqlcmd from within the powershell script using the & operator, see sample to generate a csv file:

& cmd /c "sqlcmd -S $sv -i $PROCFILE -s, -v varDB = $dbclean -o $filename"

$sv has server name like SERVERNAME\INSTANCE

$PROCFILE is like d:\TSTSQL\Sqlquery.SQL

$filename is d:\TSTSQL\Desiredoutfilename.CSV

$dbclean is a parameter passed to the sql file

How to execute sqlcmd from powershell?

7 Answers7

Linked