Author Posts

December 14, 2017 at 9:14 pm

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.

December 14, 2017 at 11:15 pm

# 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'

December 15, 2017 at 5:15 pm

Ya that didn't help