Powershell: Passing variables to scriptfile using invoke-sqlcmd

• jamie.crawford

  Old Hand

  Points: 381

  More actions

  October 8, 2013 at 10:02 am

  #299024

  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:\temp\mybigscript.sql'

  $server = 'localhost'

  $database ='bigdb"

  $MyArray = "MyVar1 = 'miami'", "MyVar2 = 'florida'", "MyVar3 = 'usa'"

  Invoke-SqlCMD -InputFile "$scriptfile" -ServerInstance $server -Database "$database" -variable $myarray

  Is what I'm attempting to do even possible?

  Any help is appreciated.

  Thanks!

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  October 8, 2013 at 10:24 am

  #1656817

  I cannot sum it up any better than this DevCentral article.

  Just in case the link ever goes here is an example of a named parameter (must be the first line of the script):

  param([string]$namedArgument="default")

  # Defaults to the text "default" if not present.

  Write-Host $namedArgument

  Or using the standard $args variable:

  Write-Host $args[0]

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• jamie.crawford

  Old Hand

  Points: 381

  More actions

  October 8, 2013 at 2:05 pm

  #1656908

  Hi,

  I can see how that works with the variables in the command line.. I am not having problems doing that, I am having problems changing the variables inside of the actual sql script (add_location.sql) that invoke-sqlcmd is running. I tried a simple script below to see if it would work, and did not have any luck.

  Thanks again for the help!

  param (

  [string]$ScriptFile = "C:\Users\Administrator\Downloads\DATABASE\INSTALL\XX\Add_LOCATION.sql",

  [string]$Server = "localhost",

  [string]$Database = "DB_POWERSHELL",

  [string]$jdcfacility = "myfacilityname"

  )

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

  $sqlSrv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' ($server)

  TRAP {Write-Host -ForegroundColor Red "Error:Unable to run script" ;continue}

  Invoke-SqlCMD -InputFile "$scriptfile" -ServerInstance $server -Database "$database" -v $jdcfacility

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  October 9, 2013 at 2:20 am

  #1657026

  Does this offer what you need: http://technet.microsoft.com/en-us/library/cc281720.aspx

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• jamie.crawford

  Old Hand

  Points: 381

  More actions

  October 9, 2013 at 11:21 am

  #1657240

  I figured it out using an example from this page..

  http://thefirstsql.com/2010/06/09/using-sqlcmd-from-powershell/

  Here is my example that worked...

  #Powershell script

  #Parameters that must be set:

  $DBServer = "localhost"

  $database = "DB_POWERSHELL"

  $CITY = "KANSAS CITY"

  $STATE = "MO"

  $COUNTRY = "USA"

  $DBScriptFile = "C:\Users\Administrator\Downloads\DATABASE\INSTALL\XX\AddCity.sql"

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

  $sqlSrv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' ($server)

  $DBParam1 = "CITY=" + $CITY

  $DBParam2 = "STATE=" + $STATE

  $DBParam3 = "COUNTRY=" + $COUNTRY

  $DBParams = $DBParam1, $DBParam2, $DBParam3

  Invoke-Sqlcmd -InputFile $DBScriptFile -Variable $DBParams -Serverinstance $DBServer -Database "$database"

  #SMALL SECTION OF CODE FROM ADDCITY.SQL

  INSERT #CITY VALUES

  ('$(CITY)', '$(STATE)', '$(COUNTRY)');

  Thanks for your help!