Incorrectly concatenated data type in Convert-DbaIndexToTable.ps1

[EnjoyinMoss]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: [16:07:27][Invoke-DbaQuery] [xxxxxx] Failed during execution | Incorrect syntax near ')'.

Steps to Reproduce

Invoke-DbaDbDataMasking -SqlInstance xxxxx -Database xxxxx -FilePath C:\xxxx\xxDBxx.xxTablexx.DataMaskingConfig.json

Attempt to mask a column with the data type NVARCHAR(MAX)

Please confirm that you are running the most recent version of dbatools

1.1.127

Other details or mentions

I captured the query being executed during a debugging session and discovered the following line ( 71582 ) in allcommands.ps1 results in a malformed sql query.

$indexToTable = Convert-DbaIndexToTable @params

Where $indexToTable.CreateStatement =

"CREATE TABLE dbo_Pxxxxxxxs([xxxxx] [datetime2],[xxxxxx] [datetime2],[xxxxxx] [int],[xxxxxxxxxx] nvarcharmax,[xxxxxx] [int],[xxxxxxxxx] [bit],[xxxxxxxxx] [datetime2],[xxxxxxxx] [datetime2],[RowNr] [bigint]);"

Which results in Incorrect syntax near ')'.

What PowerShell host was used when producing this error

Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.19041.1682
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.1682
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) Apr 11 2022 16:24:07 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19044: )

.NET Framework Version

.NET Framework 4.8.4510.0

[EnjoyinMoss]

[EnjoyinMoss]

Warning... many crimes committed here!

[EnjoyinMoss]

[EnjoyinMoss]

So the above edits got me back up and running. Suspect there are more elegant solutions than this.

[EnjoyinMoss]

Now I'm stuck in a forEach loop starting at line 71633 (facepalm)

[andreasjordan]

Sorry to say, but I can not find the code from your screenshots.

[EnjoyinMoss]

Apologies @andreasjordan the two screenshots directly above are showing my efforts to resolve the issue but I found myself in a bit of a rabbit hole. The first of the three screenshots shows the original DBATools code at the point in which it has the incorrectly formatted 'create' statement on line 71582 in the $indexToTable variable.

[andreasjordan]

Ok, now I understand. The author is @sanderstad - maybe he has time to have closer look.

[EnjoyinMoss]

I have found a workaround. Upgraded to [v1.1.134] - same issue exists however...

Inside the JSON file created by "New-DbaDbMaskingConfig" - if the target masking column metadata blocks are separated out into individual, repeating table blocks then the problem goes away,

I haven't the time to debug and find out why it works this way but I do feel as though there are a lot of programming memes that cover this journey I've had.

[sanderstad]

I will take a look to see if I can fix the problem

[sanderstad]

I have found a workaround. Upgraded to [v1.1.134] - same issue exists however...

Inside the JSON file created by "New-DbaDbMaskingConfig" - if the target masking column metadata blocks are separated out into individual, repeating table blocks then the problem goes away,

I haven't the time to debug and find out why it works this way but I do feel as though there are a lot of programming memes that cover this journey I've had.

@EnjoyinMoss Can you contact me when you have the time to explain the problem to me because I'm not sure what to fix here. Maybe a teams sessions or something like that will make things clearer for the both of us :)

[andreasjordan]

Any news?

[andreasjordan]

This got a bit stale. So let me bring this issue to you attention again...

[andreasjordan]

I had time to have a deeper look and found the issue. Fix is on its way....