Received parsing error for current version that I never received in 21.1.18256

[spodg13]

Just recently updated and I had to uninstall and revert back to 21.1.18256.

$pat_list = " ('1234','23435','3456', '4567' )" $qry = "select PAT_NAME as 'Patient' ,PAT_MRN_ID as 'MRN' from clarity_rpt..PATIENT where PAT_ID in $pat_list " would produce "Invoke-Sqlcmd : Incorrect syntax was encountered while parsing ' ' " yet the $qry read as:

select PAT_NAME as 'Patient' ,PAT_MRN_ID as 'MRN' from clarity_rpt..PATIENT where PAT_ID in ('1234','23435','3456', '4567')

[Matteo-T]

Hi @spodg13, would you be able to produce a better repro case, i.e. with mode details?

I user T-SQL to get a DB, a TABLE, and insert some data like this:

CREATE DATABASE clarity_rpt
GO

USE clarity_rpt
GO

CREATE TABLE [dbo].[PATIENT](
    [PAT_NAME] [nvarchar](50) NULL,
    [PAT_MRN_ID] [nchar](10) NULL,
    [PAT_ID] [nchar](10) NULL
) ON [PRIMARY]
GO

INSERT INTO PATIENT VALUES ('AAA', 'BBB', '1234')
GO

Then I ran the following PowerShell script (I'm aso attaching it as 'a.ps1.txt' a.ps1.txt , to make sure that no character is lost/transformed when doing the MarkDown:

$pat_list = " ('1234','23435','3456', '4567' )"

$qry = "select
PAT_NAME as 'Patient'
,PAT_MRN_ID as 'MRN'
from clarity_rpt..PATIENT
where PAT_ID in $pat_list "

Write-Host "Query:"
Write-Host -NoNewLine $qry
Write-Host ""

# MyServer is just a SQL Server 2022
Invoke-Sqlcmd -Query $qry -ServerInstance MyServer -TrustServerCertificate

I saved the script in a file (a.ps1). The result when I run it is this:

PS C:\Users\matteot> . .\a.ps1
Query:
select
PAT_NAME as 'Patient'
,PAT_MRN_ID as 'MRN'
from clarity_rpt2..PATIENT
where PAT_ID in  ('1234','23435','3456', '4567' )

Patient MRN
------- ---
AAA     BBB

which seems correct to me. What am I missing or doing differently from you?

[spodg13]

Apparently it is all about the -TrustServerCertificate. I had to go change my code to add this parameter under version 22 and it worked fine.

My $qry statement was working fine as I copied it into SSMS and ran it just fine. The error I was receiving was as follows:

Invoke-Sqlcmd : Incorrect syntax was encountered while parsing ''.

At I:\SQL\LawsonProject\RunningCode\DailyAudit_Pat_byUser_113391.ps1:176 char:19

• ... serResult = Invoke-Sqlcmd -ServerInstance $sqlserver -Database $datab ...

• 
  
  + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
  
  + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd : Incorrect syntax was encountered while parsing ''.

At I:\SQL\LawsonProject\RunningCode\DailyAudit_Pat_byUser_113391.ps1:188 char:15

• ... atResult = Invoke-Sqlcmd -ServerInstance $sqlserver -Database $datab ...

• 
  
  + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
  
  + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

As it had the “while parsing ‘ ‘ “ I took that to mean it had a statement error initially. I am not certain I understand why the TrustServerCertificate was needed or how to read that from the error I received, but it does work correctly as long as I have the parameter in place.

Darryl

On Jan 16, 2024, at 12:13 AM, Matteo Taveggia @.***> wrote:

Hi @spodg13 https://github.com/spodg13, would you be able to produce a better repro case, i.e. with mode details?

I user T-SQL to get a DB, a TABLE, and insert some data like this:

CREATE DATABASE clarity_rpt GO

USE clarity_rpt GO

CREATE TABLE [dbo].[PATIENT]( [PAT_NAME] nvarchar NULL, [PAT_MRN_ID] nchar NULL, [PAT_ID] nchar NULL ) ON [PRIMARY] GO

INSERT INTO PATIENT VALUES ('AAA', 'BBB', '1234') GO Then I ran the following PowerShell script (I'm aso attaching it as 'a.ps1.txt' a.ps1.txt https://github.com/microsoft/SQLServerPSModule/files/13947154/a.ps1.txt , to make sure that no character is lost/transformed when doing the MarkDown:

$pat_list = " ('1234','23435','3456', '4567' )"

$qry = "select PAT_NAME as 'Patient' ,PAT_MRN_ID as 'MRN' from clarity_rpt..PATIENT where PAT_ID in $pat_list "

Write-Host "Query:" Write-Host -NoNewLine $qry Write-Host ""

MyServer is just a SQL Server 2022

Invoke-Sqlcmd -Query $qry -ServerInstance MyServer -TrustServerCertificate I saved the script in a file (a.ps1). The result when I run it is this:

PS C:\Users\matteot> . .\a.ps1 Query: select PAT_NAME as 'Patient' ,PAT_MRN_ID as 'MRN' from clarity_rpt2..PATIENT where PAT_ID in ('1234','23435','3456', '4567' )

Patient MRN

---

AAA BBB which seems correct to me. What am I missing or doing differently from you?

— Reply to this email directly, view it on GitHub https://github.com/microsoft/SQLServerPSModule/issues/70#issuecomment-1893249887, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIRKBNIG3FMJDSLOLCLDZQ3YOYZD5AVCNFSM6AAAAABBVR2UZKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQOJTGI2DSOBYG4. You are receiving this because you were mentioned.

[Matteo-T]

The "secure by default" is a breaking change that started in v22 of the module. It's documented here under https://github.com/microsoft/SQLServerPSModule/wiki/Secure-by-default:-breaking-changes-going-from-v21-to-v22.

The error message is a bit cryptic (at best). I believe there is another issue logged to see if I can make it a little more explanatory.

I'll go ahead and resolve this issue for now. Thanks!