1

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).

Shishir Tanwar
  • 21
  • 4

2 Answers2

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.

hector-j-rivas
  • 771
  • 8
  • 21
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

Shishir Tanwar
  • 21
  • 4
  • 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](/help/how-to-answer). – Community May 16 '22 at 07:03