Getting exception when calling Invoke-Sqlcmd

Question

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?

Have you tries adding the DB name after .\SQLExpress?

xXhRQ8sD2L7Z
– xXhRQ8sD2L7Z

2014-12-01 17:39:07 +00:00
Commented Dec 1, 2014 at 17:39 — xXhRQ8sD2L7Z
– xXhRQ8sD2L7Z, Commented Dec 1, 2014 at 17:39
Do you mean using the DATABASE switch?

Mike Cheel
– Mike Cheel

2014-12-01 17:39:36 +00:00
Commented Dec 1, 2014 at 17:39 — Mike Cheel
– Mike Cheel, Commented Dec 1, 2014 at 17:39
I tried with the switch but makes no difference.

Mike Cheel
– Mike Cheel

2014-12-01 17:40:41 +00:00
Commented Dec 1, 2014 at 17:40 — Mike Cheel
– Mike Cheel, Commented Dec 1, 2014 at 17:40

Mike Cheel · Accepted Answer · 2014-12-01 17:56:35Z

1

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

answered Dec 1, 2014 at 17:56

Mike Cheel

13.3k10 gold badges79 silver badges107 bronze badges

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

2 Comments

GGirard Over a year ago

Same here! Did you find any explanation about that?

chillNZ Over a year ago

Thank you thank you thank you! Was struggling to work out what I did wrong. Not sure why it works differently, the official documentation specifies single quotes for the value which doesn't work...

Collectives™ on Stack Overflow

Getting exception when calling Invoke-Sqlcmd

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related