Powershell: Passing variables to scriptfile using invoke-sqlcmd

Question

Post reply

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!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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!