ScriptBlock with Function

This topic contains 2 replies, has 2 voices, and was last updated by  Chad Nathan Jones 1 month, 1 week ago.

  • Author
    Posts
  • #89251

    Chad Nathan Jones
    Participant

    I currently have the function below. I am trying to get an Invoke-Command to run as a Job so when it tries to create an SQL connection and insert the data it doesn't hang up the rest of my Powershell form.

    # Performs an ExecuteNonQuery command against the database connection. 
    function ExecNonQuery
    {
    	param ($cmdText)
    	
    	# Determine if parameters were correctly populated. 
    	if (!$cmdText)
    	{
    		# One or more parameters didn't contain values. 
    		write-Host "ExecNonQuery function called with no connection string and/or command text."
    	}
    	else
    	{
    		write-Host "Creating SQL Connection..."
    		# Instantiate new SqlConnection object. 
    		$Connection = New-Object System.Data.SQLClient.SQLConnection
    		
    		# Set the SqlConnection object's connection string to the passed value. 
    		$Connection.ConnectionString = "server=Computer\SQLEXPRESS;database=GUI;trusted_connection=true;"
    		
    		# Perform database operations in try-catch-finally block since database operations often fail.
    		if (Test-Connection "Computer" -Quiet)
    		{
    			try
    			{
    				write-Host "Opening SQL Connection..."
    				# Open the connection to the database. 
    				$Connection.Open()
    				
    				write-Host "Creating SQL Command..."
    				# Instantiate a SqlCommand object. 
    				$Command = New-Object System.Data.SQLClient.SQLCommand
    				# Set the SqlCommand's connection to the SqlConnection object above. 
    				$Command.Connection = $Connection
    				# Set the SqlCommand's command text to the query value passed in. 
    				$Command.CommandText = $cmdText
    				
    				write-Host "Executing SQL Command..."
    				# Execute the command against the database without returning results (NonQuery). 
    				$Command.ExecuteNonQuery()
    			}
    			catch [System.Data.SqlClient.SqlException]
    			{
    				$_ | select -expandproperty invocationinfo | Format-List Line, PositionMessage -force
    				write-host $_.Exception.ToString() -foregroundcolor "red"
    			}
    			catch
    			{
    				# An generic error occurred somewhere in the try area. 
    				write-Host "An error occurred while attempting to open the database connection and execute a command."
    			}
    			finally
    			{
    				# Determine if the connection was opened. 
    				if ($Connection.State -eq "Open")
    				{
    					write-Host "Closing Connection..."
    					# Close the currently open connection. 
    					
    				}
    			}
    		}
    	}
    }
    

    And in another function I am calling a foreach loop and trying to run the Invoke-Command.

    $script:g = $e | select -Unique Name, AccountExpirationDate | Sort-Object -Property Name
    	foreach ($xpusers in $g)
    	{
    		$xdate = $xpusers.AccountExpirationDate
    		$cleanxdate = $xdate.ToString("M")
    		$ExpiringAD.Items.Add($xpusers.Name + "  -  " + $cleanxdate)
    		$sqlDate = (Get-Date $xdate -Format G)
    		$xpusername = $xpusers.Name
    		$sqlData = "INSERT INTO dbo.ExpiringAccounts Values (`'$xpusername`', `'$sqlDate`')"
    		Invoke-Command -ScriptBlock {ExecNonQuery $args} -ArgumentList $sqlData -AsJob
    	}
    

    No matter if I hardcode the ArgumentList with the "INSERT INTO dbo.ExpiringAccounts Values ('SAM', '10/17/2017')" I get

    Invoke-Command : Parameter set cannot be resolved using the specified named parameters.
    
  • #89263

    postanote
    Participant

    # Have you seen the write up to see if it helps in your use case?
    # Using a ScriptBlock Parameter With a PowerShell Function
    'learn-powershell.net/2014/10/11/using-a-scriptblock-parameter-with-a-powershell-function'

  • #89317

    Chad Nathan Jones
    Participant

    Ya that didn't help

You must be logged in to reply to this topic.