Powershell Invoke-sqlcmd In-script Variable override

Question

I am using the following:

Powershell Command: Invoke-Sqlcmd -AbortOnError -InputFile "C:\FullPath\SQLQuery.sql" -ServerInstance WPU8V9011532 -Variable "PREF = 'NeededVal'"

SQLQuery.sql file content:

:setvar PREF "InternalVal"

USE $(PREF)MyDB Select * from ABC

What I need: To make the execution use "NeededVal" instead of "InternalVal".

Due to precedence rules, it doesn't work. Is it possible to override them? There are 100s of such files to execute and cannot be edited. Also, the servers of execution are multiple and repeatedly required (executions).

hector-j-rivas · Accepted Answer · 2022-05-14 18:53:55Z

2

I think your issue is the Variable parameter expects a string array. Also the use of single quotes does not make for valid SQL syntax. Try:

Invoke-Sqlcmd -AbortOnError -InputFile "C:\FullPath\SQLQuery.sql" -ServerInstance WPU8V9011532 -Variable @("PREF=NeededVal","ANOTHERVAR=ANOTHERVALUE")

UPDATE

As per the OP’s comment I addressed syntax issues. The answer is no, script variables have the highest precedence as per SQLCMD Variable Precedence.

One solution is to remove variables from the scripts and have all callers provide the values. Another is to coalesce SQLCMD variable values into SQL variables. The point is that there’s no getting around editing the SQL scripts.

edited May 14, 2022 at 18:53

answered Apr 20, 2022 at 15:52

hector-j-rivas

8159 silver badges22 bronze badges

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

1 Comment

Shishir Tanwar Over a year ago

Thank you Hector but it still looks at the inner values even if I pass them as a list. :(

Shishir Tanwar · Accepted Answer · 2022-05-13 16:27:22Z

0

There's just no way to do this: SQLCMD, command-line variables and script :setvar

Microsoft: https://learn.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-ver15#variable-precedence-low-to-high

answered May 13, 2022 at 16:27

Shishir Tanwar

211 silver badge4 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Powershell Invoke-sqlcmd In-script Variable override

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related