Blog Post

Max Length of a column in a DataTable in PowerShell

,

Whilst I was writing my Test-DbaLastBackup Posts I ran into a common error I get when importing datatables into a database

I was using this table

01 - table

and when I tried to add the results of the Test-DbaLastBackup I got this

02 -error.PNG

Exception calling “WriteToServer” with “1” argument(s): “The given value of type String from the data source cannot be converted to type nvarchar of the

specified target column.”

At line:356 char:4

+             $bulkCopy.WriteToServer($InputObject)

+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : InvalidOperationException

Hmm, it says that it can’t convert a string to a nvarchar, that doesn’t sound right.To find out what was happening I used a little bit of code that I use every single day

 $Error[0] | Fl -force

All errors from your current session are stored in the $error array so [0] accesses the most recent one and fl is an alias for Format-List and the force switch expands the object. This is what I saw

03 expanded error.PNG

System.Management.Automation.MethodInvocationException: Exception calling “WriteToServer” with “1” argument(s): “The given value of

type String from the data source cannot be converted to type nvarchar of the specified target column.” —>

System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the

specified target column. —> System.InvalidOperationException: String or binary data would be truncated.

String or binary data would be truncated. OK that makes sense, one of my columns has larger data than the destination column but which one? Lets take a look at some of the data

SourceServer  : SQL2016N3

TestServer    : SQL2016N1

Database      : RidetheLightning

FileExists    : Skipped

RestoreResult : Restore not located on shared location

DbccResult    : Skipped

SizeMB        : 4.08

BackupTaken   : 3/19/2017 12:00:03 AM

BackupFiles   : C:\MSSQL\Backup\SQL2016N3\RidetheLightning\FULL\SQL2016N3_RidetheLightning_FULL_20170319_000003.bak

SourceServer  : SQL2016N3

TestServer    : SQL2016N1

Database      : TheCallofKtulu

FileExists    : Skipped

RestoreResult : Restore not located on shared location

DbccResult    : Skipped

SizeMB        : 4.08

BackupTaken   : 3/19/2017 12:00:04 AM

BackupFiles   : C:\MSSQL\Backup\SQL2016N3\TheCallofKtulu\FULL\SQL2016N3_TheCallofKtulu_FULL_20170319_000004.bak

SourceServer  : SQL2016N3

TestServer    : SQL2016N1

Database      : TrappedUnderIce

FileExists    : Skipped

RestoreResult : Restore not located on shared location

DbccResult    : Skipped

SizeMB        : 4.08

BackupTaken   : 3/19/2017 12:00:04 AM

BackupFiles   : C:\MSSQL\Backup\SQL2016N3\TrappedUnderIce\FULL\SQL2016N3_TrappedUnderIce_FULL_20170319_000004.bak

Hmm, its not going to be easy to work out which bit of data is too big here.

All I need to know is the maximum length of the columns in the datatable though so I have a little snippet that will do that for me

$columns = ($datatable | Get-Member -MemberType Property).Name
foreach($column in $Columns) {
$max = 0
foreach ($a in $datatable){
       if($max -lt $a.$column.length){
        $max = $a.$column.length
       }
}
Write-Output "$column max length is $max"
}

and the output looks like this

04 - max length.PNG

So we can quickly see that the backupfiles property is too big and change the table accordingly and no more error.

Its pretty quick too, scanning 105 rows in 56 milliseconds in this example
05 - how long.PNG

I keep this little snippet in my snippets list for PowerShell ISE which you can find here

Here is the code to add this as a snippet to ISE
## A list of snippets
$snips = Get-IseSnippet
## Add a snippet
if(!$snips.Where{$_.Name -like 'Max Length of Datatable*'})
{
$snippet = @{
 Title = 'Max Length of Datatable'
 Description = 'Takes a datatable object and iterates through it to get the max length of the string columns - useful for data loads'
 Text = @"
`$columns = (`$datatable | Get-Member -MemberType Property).Name
foreach(`$column in `$Columns)
{
`$max = 0
foreach (`$a in `$datatable)
{
if(`$max -lt `$a.`$column.length)
{
`$max = `$a.`$column.length
}
}
Write-Output "`$column max length is `$max"
}
"@
}
New-IseSnippet @snippet
}
and if you want to add it to your VSCode snippets then you need to edit the PowerShell.json file which is located in your user home AppData folder ‘C:\Users\User\AppData\Roaming\Code\User\snippets\powershell.json’ or by clicking File –> Preferences –> User Snippets and typing PowerShell
04 user snippets.gif

Then you can add this bit of json inside the curly braces

 "Max Length of Datatable": {
"prefix": "Max Length of Datatable",
"body": [
"$$columns = ($$datatable | Get-Member -MemberType Property).Name",
"foreach($$column in $$Columns) {",
"    $$max = 0",
"    foreach ($$a in $$datatable){",
"        if($$max -lt $$a.$$column.length){",
"            $$max = $$a.$$column.length",
"        }",
"    }",
"    Write-Output \"$$column max length is $$max\"",
"}"
],
"description": "Takes a datatable object and iterates through it to get the max length of the string columns - useful for data loads"
}, 

and you have your snippet ready for use

07 - snippet in vscode.gif

Happy Automating

 

 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating