Home Forums Programming Powershell Powershell: Passing variables to scriptfile using invoke-sqlcmd RE: Powershell: Passing variables to scriptfile using invoke-sqlcmd

  • 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!