Getting exception when calling Invoke-Sqlcmd

3
votes

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?

1
powershell
Have you tries adding the DB name after .\SQLExpress? - xXhRQ8sD2L7Z
Do you mean using the DATABASE switch? - Mike Cheel
I tried with the switch but makes no difference. - Mike Cheel

1 Answers

1
votes

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