October 8, 2013 at 10:02 am
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!
October 8, 2013 at 10:24 am
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!!!
October 8, 2013 at 2:05 pm
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
October 9, 2013 at 2:20 am
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!!!
October 9, 2013 at 11:21 am
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!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply