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.