PowerShell - Calling and Executing a Function with param from another Script file

Question

I am having trouble getting a Script to load and execute a function from another file. Right now the Function is using a "dummy" string variable which is given a value in the main script (so the param in the function itself is just a empty placeholder string that is given value in the main script. Thanks in advance! Ill explain more after the code below:

MAIN Script: iterates through a list of instances ($InstanceList) and for each $instance it tries to connect and then should execute the function with the parameter $InstanceName

   #Loads Server Management Libraries
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.RegisteredServers') | Out-Null

    #Adds SQL Snapins
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100

    #Loads CMS Function File
    . .C:\Scripts\CMS_Functions\Environment_Functions.ps1;

    #Variable for CMS Instance
    $CMSInstance = 'CMSInstanceName';

    #Variable for the list of sql instances and their path
    $InstanceList = Get-Content "C:\Scripts\InPutFiles\instancestest.txt";

    foreach($instance in $InstanceList)
    {               
        #Creates a Server Management Object for the given $instance
        $instanceObject = New-Object Microsoft.SqlServer.Management.Smo.Server($instance)

        #Obtains the name of the instance from the sql server object
        $InstanceName = $instanceObject.Name

        #Connects to the Central Management Registered Server Instance
        $connectionString = "Data Source=$CMSInstance;Initial Catalog=master;Integrated Security=SSPI;"
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
        $conn = New-Object System.Data.SqlClient.SqlConnection("Server=$CMSInstance;Database=master;Integrated Security=True")
        $CMSStore = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

    #Call Function
        getProdInstances $InstanceName
    }

FUNCTION Script which basically queries a sql table and finds instances and then if those $instances equal the first record in the $prodQuery array then it adds that instance to a group in a central management server (sql stuff) ($instance is the dummy placeholder param)

function getProdInstances $instance
{   
    $prodQuery = "SELECT DISTINCT INSTANCE
              FROM TABLE where INSTANCE = '$instance'
              ORDER BY INSTANCE;"

    $prodQuery = Invoke-Sqlcmd -Query $prodQuery;
    $prodResult = $prodQuery[0]

    if($prodResult -eq $instance)
    {
        #Variable for the highest level group directory
        $CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups["By Environment"].ServerGroups["PROD"]

        #Sets the Registered Server Variables
        $RegServerName = $prodResult
        $RegServerInstance = $RegServerName

        #Creates a Server Management Object for the Registered Server Group and Instance to be added to it
        $NewServer = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($CMSDBStore, "$RegServerName")

        #Creates a secure connection to the Registered Server Instance
        $NewServer.SecureConnectionString = "server=$RegServerInstance;integrated security=true"
        $NewServer.ConnectionString = "server=$RegServerInstance;integrated security=true"
        $NewServer.ServerName = "$RegServerInstance"

        #Displays information to the command prompt that the instanec $RegServerName has been added to the $CMSInstance
        Write-Host -ForegroundColor DarkGreen "Adding $RegServerName to $CMSInstance";

        #Adds the instance to the Registered Server CMS
        $NewServer.Create()
    }

I get the error 'getProdInstances' is not recognized as the name of a cmdlet, function, blah blah blah. pointing to the line of the main script where I call it. I know PS executes by top down line by line so im not sure if its a way I am setting up the main script, or how im setting the function up.

TheMadTechnician · Accepted Answer · 2014-02-11 21:50:22Z

1

Sorry, I commented instead of answering, so you may have seen a deleted comment.

Your line that's loading the function should not need that semi-colon at the end, and it shouldn't have that period immediately before the path. Try:

. C:\Scripts\CMS_Functions\Environment_Functions.ps1

See if that doesn't load the function for you, and resolve the issue. Or just drop the function in the beginning of the script instead of breaking it out into it's own file to avoid the issue all together.

answered Feb 11, 2014 at 21:50

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Skunny11 Over a year ago

I changed it to what you said and threw the function into the same file. I am now getting a connection error within the function pointing to the $prodQuery = Invoke-Sqlcmd and $prodResult lines saying there is network related error. If I take everything out of a function and put it into loops I dont get this error and it populates, However I need these things in functions because of how It needs to be set up for the future.

TheMadTechnician Over a year ago

You should update your original question with the current code and exact error you are getting (obviously server/db names can be altered for privacy).

Skunny11 Over a year ago

I got it working, had to put the functions parameter in parenthesis

Collectives™ on Stack Overflow

PowerShell - Calling and Executing a Function with param from another Script file

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related