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: stackoverflow.com/questions/1015038/powershell-sqlcmd — Ruben Bartelink
– Ruben Bartelink, Commented Jan 31, 2013 at 12:35

Andy Arismendi · Accepted Answer · 2012-03-15 04:43:19Z

27

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, 2012 at 4:43

Andy Arismendi

53.1k17 gold badges113 silver badges130 bronze badges

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

Comments

user4531 · Accepted Answer · 2015-01-28 17:09:42Z

21

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, 2015 at 17:09

user4531

2,6078 gold badges31 silver badges38 bronze badges

4 Comments

ascary Over a year ago

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.

DonBecker Over a year ago

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

kevinpo Over a year ago

Invoke-Sqlcmd adds a semicolon to the end of stored procedures, which causes issues with comparison tools. See social.msdn.microsoft.com/Forums/sqlserver/en-US/… and reddit.com/r/PowerShell/comments/5v29o2/…

tbradt Over a year ago

Not to mention, Invoke-Sqlcmd wasn't supported in Powershell 2.0.

JPBlanc · Accepted Answer · 2020-04-06 13:24:33Z

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.

Andrew Shepherd · Accepted Answer · 2012-03-15 04:42:27Z

3

Use Invoke-Expression rather than Invoke-Command

answered Mar 15, 2012 at 4:42

Andrew Shepherd

45.5k33 gold badges148 silver badges212 bronze badges

3 Comments

Andy Arismendi Over a year ago

I don't see why people try to use Invoke-Expression when calling Win32 executables... This is what the call operator is for.

Andrew Shepherd Over a year ago

@Andy - Invoke-Expression allows you to generate the entire command as a string first, which could be useful depending upon the circumstances. See stackoverflow.com/questions/6604089/…

Andy Arismendi Over a year ago

Yep, but that's not happening here. Only use Invoke-Expression when there is an expression that needs to be evaluated.

mjolinor · Accepted Answer · 2012-03-15 15:34:17Z

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, 2012 at 15:34

answered Mar 15, 2012 at 10:53

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

1 Comment

user847990 Over a year ago

All this is going to do is return the value in $sql, it will not actually execute it (at least not on my machine).

user2063329 · Accepted Answer · 2015-04-18 02:50:18Z

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, 2015 at 2:50

user2063329

4572 gold badges5 silver badges15 bronze badges

Comments

timiTao · Accepted Answer · 2017-09-28 15:43:00Z

0

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

edited Sep 28, 2017 at 15:43

timiTao

1,4113 gold badges22 silver badges36 bronze badges

answered Sep 28, 2017 at 14:43

Jorge Besada

11 silver badge1 bronze badge

Collectives™ on Stack Overflow

How to execute sqlcmd from powershell?

7 Answers 7

Comments

4 Comments

Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

4 Comments

Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related