Find-dbadbunusedindex | write-dbadbtabledata - Incorrect syntax near ')'

[Sirwill1968]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

I've tried two methods to pull unused indexes and write to a table the output.
Incorrect syntax near ')'.

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

Both of these fail with same error of Incorrect syntax near ')'

PS C:\Windows\system32> Find-DbaDbUnusedIndex -SqlInstance sql01 | Write-DbaDbTableData -SqlInstance sql01 -Database bb -table unusedindexs -AutoCreateTable
WARNING: [13:05:59][Write-DbaDbTableData] Failed to create table [bb].[dbo].[unusedindexs] | Incorrect syntax near ')'.
PS C:\Windows\system32> Get-DbaDatabase -SqlInstance sql01 |Find-DbaDbUnusedIndex |Write-DbaDbTableData -SqlInstance sql01 -Database bb -Table unusedindexes -AutoCreateTable
WARNING: [13:13:39][Write-DbaDbTableData] Failed to create table [bb].[dbo].[unusedindexes] | Incorrect syntax near ')'.

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

2.1.14

Other details or mentions

If I run the command in debug, this information is shown.
DEBUG: [13:21:06][New-Table] The following query failed: BEGIN CREATE TABLE [bb].[dbo].[unusedindexes] () END | Incorrect syntax near ')'.
The error is because there are no columns in the table.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.14393.6343
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.6343
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-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.7.4051.0

[andreasjordan]

The pipeline in PowerShell is a super feature. But to get to the root cause of this problem, we need to save the results of the first command to a variable and have a look at it.

Please run this code:

$unusedIndexes = @( )
$unusedIndexes += Find-DbaDbUnusedIndex -SqlInstance sql01
$unusedIndexes.Count
$unusedIndexes[0].GetType()
$unusedIndexes[0] | Format-List

This way, we get to know if there are any unused indexes and how the data looks like.

Then we try to pass that data to Write-DbaDbTableData.

[Sirwill1968]

PS C:\Windows\system32> $unusedIndexes = @( )
$unusedIndexes += Find-DbaDbUnusedIndex -SqlInstance sql01
$unusedIndexes.Count
$unusedIndexes[0].GetType()
$unusedIndexes[0] | Format-List
91

IsPublic IsSerial Name                                     BaseType                                                                                                                           
-------- -------- ----                                     --------                                                                                                                           
True     False    DataRow                                  System.Object                                                                                                                      




ComputerName           : SQL01
InstanceName           : MSSQLSERVER
SqlInstance            : SQL01
Database               : SbGatewayDatabase
DatabaseId             : 5
Schema                 : dbo
Table                  : MessageContainerRoleInstanceHistoryTable
ObjectId               : 821577965
IndexName              : CIX_MessageContainerRoleInstanceHistoryTable
IndexId                : 1
TypeDesc               : CLUSTERED
UserSeeks              : 0
UserScans              : 0
UserLookups            : 0
UserUpdates            : 26
LastUserSeek           : 
LastUserScan           : 
LastUserLookup         : 
LastUserUpdate         : 5/12/2024 3:46:33 PM
SystemSeeks            : 0
SystemScans            : 0
SystemLookup           : 0
SystemUpdates          : 0
LastSystemSeek         : 
LastSystemScan         : 
LastSystemLookup       : 
LastSystemUpdate       : 
IndexSizeMB            : 0.335937
RowCount               : 3240
CompressionDescription : NONE

[andreasjordan]

Thanks for the feedback. The data looks valid. Next step is to feed the data to Write-DbaDbTableData.

From a performance point of view, the best is to use the InputObject parameter:

Write-DbaDbTableData -InputObject $unusedIndexes -SqlInstance sql01 -Database bb -table unusedindexs -AutoCreateTable

If that still throws an error, please add -Verbose and provide the output.

[Sirwill1968]

VERBOSE: [07:13:16][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object
VERBOSE: [07:13:16][Connect-DbaInstance] authentication method is 'local integrated'
VERBOSE: [07:13:16][Write-DbaDbTableData] FQTN processed: [bb].[dbo].[unusedindexes]
VERBOSE: [07:13:16][Write-DbaDbTableData] Creating SqlBulkCopy object
VERBOSE: [07:13:16][New-Table] Creating table for [bb].[dbo].[unusedindexes]
WARNING: [07:13:16][Write-DbaDbTableData] Failed to create table [bb].[dbo].[unusedindexes] | Incorrect syntax near ')'.

So for more info ran debug

DEBUG: 2928 | [07:14:30][Connect-DbaInstance] Starting process block
DEBUG: 2939 | [07:14:30][Connect-DbaInstance] Immediately checking for Azure
DEBUG: 2832 | [07:14:30][Test-Azure] Test for Azure is negative
DEBUG: 2946 | [07:14:30][Connect-DbaInstance] Starting loop for 'sql01': ComputerName = 'sql01', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Default'
DEBUG: 2970 | [07:14:30][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object
DEBUG: 2832 | [07:14:30][Test-Azure] Test for Azure is negative
DEBUG: 3120 | [07:14:30][Connect-DbaInstance] authentication method is 'local integrated'
DEBUG: 3159 | [07:14:30][Connect-DbaInstance] ApplicationName will be set to 'dbatools PowerShell module - dbatools.io'
DEBUG: 3191 | [07:14:30][Connect-DbaInstance] ConnectionTimeout will be set to '15'
DEBUG: 3197 | [07:14:30][Connect-DbaInstance] Database will be set to 'bb'
DEBUG: 3203 | [07:14:30][Connect-DbaInstance] EncryptConnection will be set to 'True'
DEBUG: 3223 | [07:14:30][Connect-DbaInstance] PacketSize will be set to '4096'
DEBUG: 3239 | [07:14:30][Connect-DbaInstance] Pooled will be set to 'False'
DEBUG: 3267 | [07:14:30][Connect-DbaInstance] TrustServerCertificate will be set to 'True'
DEBUG: 3309 | [07:14:30][Connect-DbaInstance] Building ServerConnection from SqlConnectionInfo
DEBUG: 3311 | [07:14:30][Connect-DbaInstance] ServerConnection was built
DEBUG: 3329 | [07:14:30][Connect-DbaInstance] Building Server from ServerConnection
DEBUG: 3331 | [07:14:30][Connect-DbaInstance] Server was built
DEBUG: 3350 | [07:14:30][Connect-DbaInstance] Setting ConnectionContext.StatementTimeout to '0'
DEBUG: 3357 | [07:14:30][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data Source=sql01;Initial Catalog=bb;Integrated Security=True;Pooling=False;Multiple Ac
tive Result Sets=False;Encrypt=True;Trust Server Certificate=True;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: 3369 | [07:14:30][Connect-DbaInstance] We connect to the instance by running SELECT 'dbatools is opening a new connection'
DEBUG: 3371 | [07:14:30][Connect-DbaInstance] We have a connected server object
DEBUG: 3425 | [07:14:30][Connect-DbaInstance] We will set server.ComputerName to server.NetName
DEBUG: 3431 | [07:14:30][Connect-DbaInstance] ComputerName will be set to sql01
DEBUG: 2832 | [07:14:30][Test-Azure] Test for Azure is negative
DEBUG: 3443 | [07:14:30][Connect-DbaInstance] We added IsAzure = 'False', DbaInstanceName = instance.InstanceName = 'MSSQLSERVER', SqlInstance = server.DomainInstanceName = '', NetPort = inst
ance.Port = '1433', ConnectedAs = server.ConnectionContext.TrueLogin = 'RAD\A-WStillwell'
DEBUG: 3446 | [07:14:30][Connect-DbaInstance] We return the server object
DEBUG: 3481 | [07:14:30][Connect-DbaInstance] SetDefaultInitFields will be used
DEBUG: 88897 | [07:14:30][Add-ConnectionHashValue] Adding to connection hash
DEBUG: 3510 | [07:14:30][Connect-DbaInstance] We are finished with this instance
DEBUG: 88621 | [07:14:30][Write-DbaDbTableData] FQTN processed: [bb].[dbo].[unusedindexes]
DEBUG: 88690 | [07:14:31][Write-DbaDbTableData] Creating SqlBulkCopy object
DEBUG: 3543 | [07:14:31][ConvertTo-DbaDataTable] Bound parameters: Raw, IgnoreNull, TimeSpanType, SizeType
DEBUG: 3544 | [07:14:31][ConvertTo-DbaDataTable] TimeSpanType = TotalMilliseconds | SizeType = Int64
DEBUG: 88467 | [07:14:31][New-Table] Creating table for [bb].[dbo].[unusedindexes]
DEBUG: 88505 | [07:14:31][New-Table] BEGIN CREATE TABLE [bb].[dbo].[unusedindexes] () END
DEBUG: [07:14:31][New-Table] The following query failed: BEGIN CREATE TABLE [bb].[dbo].[unusedindexes] () END | Incorrect syntax near ')'.
WARNING: [07:14:33][Write-DbaDbTableData] Failed to create table [bb].[dbo].[unusedindexes] | Incorrect syntax near ')'.
DEBUG: [07:14:33][Write-DbaDbTableData] Failed to create table [bb].[dbo].[unusedindexes] | Incorrect syntax near ')'.

To me, this BEGIN CREATE TABLE [bb].[dbo].[unusedindexes] () END looks like it passes in no field names.

[Sirwill1968]

As a side note, if I use the output from the commands you gave above to show 1 record with all the data, I created a table manually, then ran the command with the | and it worked.

Find-DbaDbUnusedIndex -SqlInstance sql01| Write-DbaDbTableData -SqlInstance sql01-Database bb -table unusedindexes1

That loaded all the data 91 rows to be precise.
But for some reason autocreate fails on this data.

[andreasjordan]

The -AutoCreateTable parameter needs the table structure that is (normally) part of the DataRow object. But the name of the property is "Table", which in case of the output of Find-DbaDbUnusedIndex is overwritten by the column "Table".

[andreasjordan]

I will provide a pull request to test if the InputObject has column information and in case it has not, output a better error message.