Function Syntax Issues

PowerShell Help
1

I’m part of a company with a lot of Powershell newbies (myself one of them). I understand it a little better than most, and I’m trying to create a function where users can be prompted for input that would, by the function’s end, spit out a text file with an Invoke-SQLCmd for them to run with verbose output. I’m doing this as a teaching/learning experience for the end users who are overwhelmed at the thought of Powershell. Here’s a script I use without issue:

[CmdletBinding()]

Param(


[Parameter(Mandatory = $True, HelpMessage = “Enter one or more filepath(s) in double quotes”)]


[string]$paths)



Enter a path for the text file output you require (Ex. C:\Temp\Output.txt)


$OutputPath = Read-Host “Enter path for text file output here (Format - DriveLetter:\Filepath\File.txt)”



Create a variable for these values to live in


$Output =



Create a foreach loop to prompt the user for Server Instance and database, then…


foreach ($path in $paths){


$ServerInstance=Read-Host"Enter Server Instance for $path (full FQDN if applicable)"


$Database=Read-Host"Enter Database for $path"



…write the output in the format of the desired command


Write-Output"Invoke-SQLCMD -ServerInstance $ServerInstance -Database $Database -InputFile $Path -Verbose"}



Save the text file to your machine.


$Output | Out-File $OutputPath

.......................................

Here’s my attempt at converting this into a function with some validation:

function Convert-SQLtoPS1{

[CmdletBinding()]


param(


[Parameter(Mandatory = $True, HelpMessage = “Enter one or more filepath(s) in double quotes (Ex. \Server\Directory\SubDirectory\SQL\SqlScript.sql”)]


[string]$ScriptPath,


[ValidateScript({Test-Path $ScriptPath})]


[Parameter(Mandatory = $True, HelpMessage = “Enter path for text file output here (Format - DriveLetter:\Filepath\File.txt)”)]


[string]$OutputPath,


[Parameter(Mandatory=$True, HelpMessage = “Enter Server Instance (FQDN required if applicable)”)]


[string]$Instance,


[ValidateScript({Test-Netconnection $Instance -InformationLevel Quiet})]


[Parameter(Mandatory=$True, HelpMessage = “Enter Database”)]


[string]$Database,


[ValidateScript({Get-SqlInstance -ServerInstance $Instance | Get-SQLDatabase $Database})]


)


$Output=Write-Output"Invoke-SQLCMD -ServerInstance $ServerInstance -Database $Database -InputFile $Path -Verbose"


} $Output | Out-File $OutputPath

I'm getting errors with syntax on the final 'ValidateScript' one-liner and I need this script to add to an existing text file, if the user runs this multiple times, at the $OutputPath location. I also get an issue with the Get-SQLInstance command despite the fact that when I run that ad hoc it returns the correct information of the test DB and server instance I'm using.
I'm wondering if someone can offer me a little guidance as to where I am going wrong here.
2

Your ValidateScript blocks are in the wrong place. They need to go inside of the parameter declaration, with that, you do not need to use the parameter name when using ValidateScript. You can use the current pipeline variable. Also, I would take out the ValidateScript for getting the sql instance and database and put it in a try/catch block.

function Convert-SQLtoPS1{

[CmdletBinding()]
param(

    [Parameter(Mandatory, 
        HelpMessage = "Enter one or more filepath(s) in double quotes (Ex. \\Server\Directory\SubDirectory\SQL\SqlScript.sql")]
    [ValidateScript({Test-Path $_})]
    [string[]]$ScriptPath,
    [Parameter(Mandatory, 
        HelpMessage = "Enter path for text file output here (Format - DriveLetter:\Filepath\File.txt)")]
    [string[]]$OutputPath,
    [Parameter(Mandatory, 
        HelpMessage = "Enter Server Instance (FQDN required if applicable)")]
    [ValidateScript({Test-Netconnection $_ -InformationLevel Quiet})]
    [string[]]$Instance,
    [Parameter(Mandatory, 
        HelpMessage = "Enter Database")]
    [string[]]$Database
)

    Process {
        Try {
            Get-SqlInstance -ServerInstance $Instance |Get-SQLDatabase $Database -ErrorAction Stop
            Write-Output "Invoke-SQLCMD -ServerInstance $ServerInstance -Database $Database -InputFile $Path -Verbose"
        }
        Catch {
            throw $_ # Could be substituted with anything else. throw is a terminating error
        }
    }
}

pwshliquori

3

This is the same conclusion we came to after looking at this for a bit, thank you for the help. I will use the try/catch instead on this one as well. Much appreciated.