ScriptBlock with Function

Welcome Forums General PowerShell Q&A ScriptBlock with Function

This topic contains 2 replies, has 2 voices, and was last updated by

1 year ago.

  • Author
  • #89251

    Points: 0
    Rank: Member

    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."
    		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)
    				write-Host "Opening SQL Connection..."
    				# Open the connection to the database. 
    				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). 
    			catch [System.Data.SqlClient.SqlException]
    				$_ | select -expandproperty invocationinfo | Format-List Line, PositionMessage -force
    				write-host $_.Exception.ToString() -foregroundcolor "red"
    				# 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."
    				# 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

    Points: 332
    Helping Hand
    Rank: Contributor

    # Have you seen the write up to see if it helps in your use case?
    # Using a ScriptBlock Parameter With a PowerShell Function

  • #89317

    Points: 0
    Rank: Member

    Ya that didn't help

The topic ‘ScriptBlock with Function’ is closed to new replies.