SQLCMD variables can be passed as name/value pairs in multiline text format, or as a basic JSON file. We recommend the JSON format as the content is easier to validate in Visual Studio Code. This extension can be used in a build or deployment pipeline to run pre- and post-deployment SQL scripts. Powershell: Is it possible to pass parameter to a sql script file when using invoke-sqlcmd.
![]() ![]()
Hi,I have several large scripts that we have to usually modify manually to make changes to our databases. Usually this consists of 3 different variables.
Lets use $city, $state, $country for example. I have been looking for a way to pass these variables to the script but can not find any examples, so I'm not sure if it is actually possible. I see that invoke-sqlcmd has a -variable option, which I tried passing an array too to see if I could pass the variables to the script without any luck.$scriptfile = 'c:tempmybigscript.sql'$server = 'localhost'$database ='bigdb'$MyArray = 'MyVar1 = 'miami', 'MyVar2 = 'florida', 'MyVar3 = 'usa'Invoke-SqlCMD -InputFile '$scriptfile' -ServerInstance $server -Database '$database' -variable $myarrayIs what I'm attempting to do even possible?Any help is appreciated.Thanks!.
You can also do it this way which is better if you have many variables and want to externalize the SQL in a file (good for large SQL statements'# top of script or from a file $sql = 'Insert into mytable(col1, col2) values ($(var1), $(var2))' $sqlfile = 'c:tempmyquery.sql' # later in script $var1 = 'Test'$var2 = 'Test2'$variables = @('Var1 = '$var1','Var2 = '$var2')Invoke-sqlcmd -Query $sql -Variable $variables -ServerInstance myInstance -Database myDB #or with a file Invoke-Sqlcmd -InputFile $sqlfile -Variable $variables -ServerInstance myInstance -Database myDB(ツ)/.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |