Saturday, July 11, 2009

Using PowerShell and SMO to change database columns collations

Changing a SQL serve database columns collations manually can be a tedious task. I have a database that I want to change the collation of all its non system columns to "Arabic_CS_AS".
Here is a PowerShell script that uses SQL Server Management Objects (SMO) to do this task:
(note that I load the assemblies with version 10.0.0 which is the version of SQL server 2008 I have installed on my system)

[System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[System.Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

$con = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

$con.ConnectionString="Data Source=.\SQLExpress;Integrated Security=SSPI;"


$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $con
$db = $srv.Databases["test"]

foreach ($table in $db.Tables)

foreach($column in $table.Columns)
if(-not ([string]::IsNullOrEmpty($column.Collation)))
$column.Collation = "Arabic_CS_AS"


Thursday, July 2, 2009

Not calling Dispose can cause InvalidComObjectException

During load testing an application that performs thousands of operations against Active Directory and under high load conditions. The process stopped working and our logs showed this error:

System.Runtime.InteropServices.InvalidComObjectException: COM object that has been
separated from its underlying RCW cannot be used.

Searching for this error, most answers on forums referred to trying to access a COM object from a thread other than the thread that created it. We use multithreading, but we did not use objects across threads.

Logs pointed us the location of the code where we should investigate. I made a review on a method that was called thousands of times and creates DirectoryEntry instances. The DirectoryEntry was not disposed!!

We were not sure that this can cause the above exception, but it was a bug and it needed to be fixed anyway. We fixed it and reapplied the scenarios that caused this exception, and it disappeared.

Other that understanding a new reason for that mysterious exception, there are some useful lessons:
  • Proper logging can help identifying errors quickly.
  • Failing to dispose disposable objects causes performance penalties that some developers underestimate their effect. It can make your application stop working!!
  • Early code review is important to spot these kinds of errors.
  • Test your application under real-life conditions.
  • Using memory profilers and dispose trackers is worth trying in some cases.