Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Powershell: Passing variables to scriptfile using invoke-sqlcmd Expand / Collapse
Author
Message
Posted Tuesday, October 8, 2013 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 7:14 AM
Points: 3, Visits: 9
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!
Post #1502697
Posted Tuesday, October 8, 2013 10:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:52 PM
Points: 5,831, Visits: 3,751
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!!!
Post #1502708
Posted Tuesday, October 8, 2013 2:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 7:14 AM
Points: 3, Visits: 9
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
Post #1502819
Posted Wednesday, October 9, 2013 2:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:52 PM
Points: 5,831, Visits: 3,751
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!!!
Post #1502962
Posted Wednesday, October 9, 2013 11:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 7:14 AM
Points: 3, Visits: 9
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!
Post #1503216
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse