SQL Server 2014 missing SMO .TruncateData() method – Max Trinidad

Hum! I just found out that in SQL Server 2014 (SP2 installed), while migrating from SQL Server 2005, one of my PowerShell script (I’ve been using for a long time) that uses SMO to truncate tables. But, when running it against a SQL Server 2014 database, I’m getting an error:

“..this property is not available on SQL Server 2014.”

For mi surprise, I ran the same PowerShell script against SQL Server 2016 and it works fine.

Here’s a sample function that does a Truncate to all my tables in a database:

[sourcecode language=”powershell”]
function Clear-DatabaseTable
{
[CmdletBinding()]
param (
[string]$SQLServerInstanceName,
[string]$SQLServerDatabasename
)

[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”) | Out-Null;
$SQLSrvObj = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLServerInstanceName;
$tables = $SQLSrvObj.Databases[$SQLServerDatabaseName].tables;

## Database tables rowcounts before truncate:
$tables | Select @{ Label = “TruncatedTable”; Expression = { $_.Schema + “.” + $_.Name }; }, `
@{ Label = “PrevRowCount”; Expression = { $_.rowcount }; } | FT -auto

$ReadThis = `
“**********************************************************************`r`n ” `
+ “Do you really want to Truncate all tables in $SourcedbName ? Y/N `r`n” `
+ “**********************************************************************`r`n “;
$Cont = Read-Host $ReadThis;
[console]::ResetColor();

if ($Cont.ToUpper() -eq ‘Y’)
{
foreach ($t in $tables)
{
Write-Verbose “Truncating Table $($t)” -Verbose;
$t.TruncateData();
};
};
};

[/sourcecode]

Load this function into you session and then run the following command:

[sourcecode language=”powershell”]
Clear-DatabaseTable -SQLServerInstanceName ‘MTRINIDADLT2\MSSQL2K16A’ `
-SQLServerDatabasename ‘devMaxTest’;

[/sourcecode]

The results against SQL Server 2016 were successful: