Call operator doesn't work when sqlcmd command promt aranged as variable

PowerShell Help
1

Hello, All
I need to execute several “sqlcmd” command prompt in my PowerShell script. When I execute this command directly in powershell using ‘&’ operator - all works fine:
& sqlcmd -S SQLSRV -v DatabaseName=“MyDb” -i “.\MyScript.sql” -b

but, if I arrange ‘sqlcmd…’ to PowerShell variable - I have got error:
$sqlcommand = “sqlcmd -S SQLSRV -v DatabaseName=`"MyDb`” -i `".\MyScript.sql`" -b"

& $sqlcommand
& : The term ‘sqlcmd -S SQLSRV -v DatabaseName=“MyDb” -i “.\MyScript.sql” -b’ is not recognized as the name of a cmdlet, function, script file, or operable program.
Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
How can I execute this “sqlcmd” command through variable ? Or may be make sense to use “Invoke-Expression” instead of “&” ?
Btw:
Invoke-Expression $sqlcommand works properly.

2

Why not just do

sqlcmd -S SQLSRV -v DatabaseName="MyDb" -i ".\MyScript.sql" -b
3

Why can’t you just use the Invoke-Sqlcmd cmdlet?

https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

 

4

Running SQLCMD directly works as JS mentioned.

For a full reference to how to run an executable have a look at this Microsoft article:
https://social.technet.microsoft.com/wiki/contents/articles/7703.powershell-running-executables.aspx

5

[quote quote=157733]Why can’t you just use the Invoke-Sqlcmd cmdlet?

https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

[/quote]
This is the native PowerShell and has limitations if you have SQL Server Manager installed I would recommend using SQLCMD and a prebuild SQL query.

6

Limitations in simply running an .sql query?

I’ve done it, seen no limitations on my end.

 

7

Last time I used it I had a case statement and a minimum of 3 different joins, there was no chance of it working. I haven’t tested it in the latest version as the SQLCMD method isn’t broken for me so I am not looking for a fix.

Solution was to install SQL Management Studio and use SQLCMD.

If it works now then great I might go back to using native powershell and cull the use of sqlcmd

8

[quote quote=157733]Why can’t you just use the Invoke-Sqlcmd cmdlet?

https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

[/quote]
Because Invoke-Sqlcmd doesn’t work if you have “==” in your db password:
sql server - Escape variable in sqlcmd / Invoke-SqlCmd - Stack Overflow