Here's what I found:
1. Try/Catch block is not needed to around a script-block, as nothing is executing yet.
2. The errors in ISE with the credentials code block were due to the Try/Catch block been in the wrong place (before the script-block).
3. The try/catch was moved to before the Start-Job code block.
4. Then, create Credential code, also moved prior to the Start-Job. (This clears the error)
5. Also, the code that creates the "Archive" folder is wrong as it was including the filename.
BTW, you may want to create another filename report string for when the job error-out.
After clearing these issues, I can confirm that this code will execute in either ISE and PrimalScript.
Code: Select all
#Scriptblock
$sqlScript = {
try{
#SQL variables
$sqlDataSource = "earth\MSQLCTP33A"
$sqlDatabase = "SampleDB"
$connectionString = "Server=$sqlDataSource;Database=$sqlDatabase;Integrated Security=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
#Sql Query
$query = @"
Select ID, FirstName, LastName, Age from People
"@
$command = $connection.CreateCommand()
$command.CommandText = $query
[System.Data.SqlClient.SqlDataAdapter]$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
[System.Data.DataSet]$SQLDS = New-Object -TypeName System.Data.DataSet "SQLDS"
$null = $SqlAdapter.fill($SQLDS)
$connection.Close()
$SQLDS.Tables[0]
}
catch
{
$connection.Close()
if ($_.Exception.InnerException)
{
Write-Output $Error
Write-Output $_.Exception.InnerException.Message
}
else
{
Write-Output $Error
}
}
}
#Script starts here:
try
{
#Credentials Variables
$username = "earth\user1"
## this line didn't work for me -> $secPass = 'MyPa&&w0rd!' | ConvertTo-SecureString -Key (1..32)
$secPass = ConvertTo-SecureString 'MyPa&&w0rd!' -asplaintext -force;
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $secPass
$sqlJob = Start-Job -ScriptBlock $sqlScript -Credential $cred
$recJob = Receive-Job -Job $sqlJob -Wait -ErrorAction Inquire
#export data
$CSV = $recJob | ConvertTo-Csv -NoTypeInformation
$CSVName = '{0}.csv' -f (Get-Date -UFormat "%Y-%m-%d_Software_Export_%I.%M.%S")
$CSVPath = "$env:USERPROFILE\Documents\Audit\$CSVName"
if (!(Test-Path "$env:USERPROFILE\Documents\Audit")) {
## - Below line is wrong:
#-> $null = New-Item -Path "$env:USERPROFILE\Documents\Audit\$CSVName" -ItemType Directory -Force
##
## - Fixed code:
$null = New-Item -Path "$env:USERPROFILE\Documents\Audit" -ItemType Directory -Force
}
$CSV| ConvertFrom-Csv | Export-Csv -Path $CSVPath -NoTypeInformation
}
catch
{
if ($_.Exception.InnerException)
{
Write-Output $Error
Write-Output $_.Exception.InnerException.Message
}
else
{
Write-Output $Error
}
}