Issue running Invoke-SQLCmd in PowerShell

619 views Asked by At

I'm very novice in PowerShell and I have a ps script that executes DB queries. But it is failing at the very beginning, and I cannot figure out what is wrong. It seems that Syntacs is correct.

I'm getting 2 errors: the main error is a Parsing error, and the 2nd one is less important (although help would be appreciated too), Here is the error I'm getting (I suspect the Parsing error, is not because of the Certificate error):

PS C:\Admin> .\Cancel.ps1 -Client  MyClient
================ Define Cancel Type ================
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
Verifying Client...
Invoke-SQLCmd : A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The
certificate chain was issued by an authority that is not trusted.)
At C:\Admin\Cancel.ps1:36 char:15
+     $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' | Write-Ou ...
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-SQLCmd : Incorrect syntax was encountered while parsing ''.
At C:\Admin\Cancel.ps1:36 char:15
+     $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' | Write-Ou ...
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
PS C:\Admin>

And here is the beginning of my script (that seems where it is failing at):

[CmdletBinding()]

param( [string][Parameter(Mandatory=$true, Position=0, HelpMessage="Missing defined client")]
       [ValidateNotNullOrEmpty()]$Client,
       
       [string][Parameter(Position=1)]$Secondary
)

Install-Module sqlserver
Update-Module sqlserver
Import-Module "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.ps1" –DisableNameChecking

Function Verify-Client {Param([string]$Client)
    $Query = "SELECT * FROM DBO.MyTable WHERE Name = '$Client'"
    $Output = Invoke-SQLCmd -Query $Query -Database 'MyDB' | Write-Output
    
    If ($Output -eq $null) {
        Write-Error 'FATAL ERROR: Client does not exist!'
        Break
    }
    Else {
        ## Build Client Array
        [ARRAY]$Script:ClientDetails = $Output
        Write-Host '  Client Found!'
    }
}

Here is my PowerShell version:

Major Minor Build Revision


5 1 17763 4720

0

There are 0 answers