Powershell – Getting exception when calling Invoke-Sqlcmd

powershell

I have the following in a file called Test.sql:

SELECT '$(Val1)' AS [Value 1], '$(Val2)' AS [Value 2]

I am trying to run the following at the pwershell prompt:

Invoke-Sqlcmd -ServerInstance '.\SQLExpress' -Variable @("Val1='Test'","Val2='Test2'") -InputFile ".\SQL\Test.sql"

I get the error:

Invoke-Sqlcmd : Incorrect syntax near ''. At line:1 char:1
+ Invoke-Sqlcmd -ServerInstance '.\SQLExpress' -Variable @("Val1='Test'","Val2='Te …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

If I swap out the SQL inside of Test.sql with:

SELECT @@VERSION

The powershell command line works and so I think the issue is with the sql file itself.

If I run sqlcmd.exe from a dos prompt providing the equivalent parameters my sql script works.

What do I need to do to get this to work?

Best Answer

When I remove the single quotes from the SQL file it all works. Itt seems to work differently from the command prompt sqlcmd.exe.

Related Topic