I am running into trouble getting my script to work. I am trying to create a script that will query a database, using Azure Automation, and create a file with a list of items that the query has found. The issue I am running into is that it does not seem to be taking some of my parameters and putting them into variables. If I run the command in the script, with parameters that I am setting, I get the results that I expected except for the blank line at the top of the output file. If I try to run the script the way it is supposed to be used, I get the error message below.
Directory Exists!
Transcript started, output file is C:\Scripts\Results\Database\Query-Database Results.Tue 02-15-2022 12-03 PM.log
Out-File : Cannot bind argument to parameter ‘FilePath’ because it is an empty string.
At C:\Users\a03.Tom_Bingeman\Query-Database.ps1:111 char:161
- … -Wrap -HideTableHeaders | Select-Object -Skip 1 | Out-File “$Output”
~~~~~~~~~
- CategoryInfo : InvalidData: (
[Out-File], ParameterBindingValidationException
- FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAllowed,Microsoft.PowerShell.Commands.OutF
ileCommandTranscript stopped, output file is C:\Scripts\Results\Database\Query-Database Results.Tue 02-15-2022 12-03 PM.log
I am not sure where I am going wrong as I am not an expert at using PowerShell. Any help that someone could provide would be greatly appreciated.
[CmdLetBinding()]
param(
[Parameter(Mandatory=$True, HelpMessage="Path to SQL Query file")]
[string]$SQLQueryFile = $null,
[Parameter(Mandatory=$True, HelpMessage="Name of SQL Server")]
[array]$SQLServer = $null,
[Parameter(Mandatory=$False, HelpMessage="Name of SQL Instance on the server")]
[array]$SQLInstance = $null,
[Parameter(Mandatory=$True, HelpMessage="Path to place the output file")]
[array]$OutputFilePath = $null
)
# Set the file that has the SQL Query in it
if ($SQLQueryFile)
{
try
{
Test-Path $SQLQueryFile -ErrorAction Stop | Out-Null
}
catch
{
$ErrorMessage = "[ERROR] Missing SQL Query File file -SQLQueryFile $SQLQueryFile"
Write-Host "$ErrorMessage"
exit
}
# Make sure you can import the query from the file.
try
{
$SQLQuery = $SQLQueryFile
}
catch
{
$ErrorMessage = "[ERROR] Error importing SQL Query from file: $($SQLQueryFile)"
exit
}
}
#Set the SQL server name.
elseif ($SQLServer)
{
$SQLServer
}
#Set the path to the output file you want.
elseif ($OutputFilePath)
{
$Output = $Output.tostring($OutputFilePath)
$Output
}
#Set the SQL instance of the SQL server if there is multiple instances on the SQL server.
elseif ($SQLInstance)
{
$Instance = \$SQLInstance
}
#Create function for logging timestamps.
function Get-TimeStamp {
return "[{0:MM/dd/yy} {0:HH:mm:ss}]" -f (Get-Date)
}
#Set up the folders and files needed for logging the script.
$LogFolder = "C:\Scripts\Results\Database"
$Exists = Test-Path -Path $LogFolder
if ($Exists -eq "True") {
Write-Host "Directory Exists!"
} else {
New-Item -Path $LogFolder -ItemType directory
}
$logdate = (Get-Date -UFormat ".%a %m-%d-%Y %H-%M %p")
$logfile = new-item -path $LogFolder -Name "Query-Database Results$logdate.log"
start-Transcript -path $Logfile
#Run the commands to perform the SQL query.
Invoke-Sqlcmd -InputFile "$SQLQuery" -ServerInstance "$SQLServer$Instance" | Format-Table -AutoSize -Wrap -HideTableHeaders | Select-Object -Skip 1 | Out-File "$Output"
Stop-Transcript
