Distribution Groups and Lync 2013 LHPv2 - Part 3

Please see here for Part 1, and here for Part 2

The final part of Distribution Groups and Lync 2013 LHPv2!

This part automates the SQL clean up with the use of the SQL Stored Procedure 'rtcDeleteABEntry'.

In Part 1 we used the SQL T statement to delete the offending ObjectGUID from the rtcab database as can be seen here:

This is great for a single or a very small number of deletions but not so great if you have many ObjectGUIDs that need to be deleted for a Tenant.

The following script will automate this for you. Please be aware you need to export the ObjectGUIDs to a CSV, which was covered in Part 2.

#Distribution Groups and Lync 2013 LHPv2 – Bulk SQL deletion

# www.exchange2010.com  Oliver Moazzezi 2014

# SQL automation help gratefully received from James Sperring http://blog.sperring.me – thanks!

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server=.;Database=rtcab;Integrated Security=True"

$guids = Import-Csv C:\test.csv

$spName = "RtcDeleteAbEntry"

$paramName = "@_AdObjectGuid"

foreach ($g in $guids.Guid) {

  Write-Host "Executing $spName for $g"

  $guid = [guid]$g

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure

  $SqlCmd.CommandText = $spName

  $SqlCmd.Connection = $SqlConnection

  $parameter = $SqlCmd.Parameters.Add("@_AdObjectGuid", [System.Data.SqlDbType]::UniqueIdentifier)

  $parameter.Value = $guid

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

}

Write-Host "All Tenants GUIDs have been removed from the rtcab database. Remember to run Update-CsAddressBook" -foregroundcolor red -backgroundcolor yellow

Ensure to save locally as a PS1 script.

Ensure you update "$guids = Import-Csv C:\test.csv" to be the location of your CSV file, otherwise simply rename it to test.csv at the root of C:\.

Also note that the line "$SqlConnection.ConnectionString = "Server=.;Database=rtcab;Integrated Security=True" points to the local SQL server, as I have used "." – change this to the Server name that is hosting the SQL rtcab database if you aren't running this locally!

Once it has ran in Powershell you will see the following output:

Note that each 0 is the output from the SQL stored procedure saying that command completed successfully.

And finally after this has completed ensure that you run Update-CsAddressBook !

Enjoy!

Oliver Moazzezi - MVP Exchange Server